UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Scanning To Enter Items To Purchase Order Please Help, Access 2007    
 
   
yeasir01
post Jul 3 2017, 11:07 AM
Post#1



Posts: 16
Joined: 15-December 16



I have the following tables
Vendors, Products, Purchase Orders, Purchase Orders Details

It is setup just like the ms access northwest demo.

I select a date & vendor on the mainform. On the subform I add products & qtys (very simple)

What i am trying to achieve is a textbox where I can key in a barcode number of a product and have the product add 1 item to the subform after enter. I do plan to use a wedge scanner to achieve this ultimately.

my goal is a way to track buydowns from the manufacture which pay quarty based on items purchased. It would be great if I can use my scanner to scan every item I receive on a weekly basis & print a report to show how many boxes of each item I purchased.

So after scanning a UPC somehow it would have to cross reference an item from the product table which has UPC and descriptions already set and add that item to the subform, if I scan the same item twice I would prefer if the subform changes qtys instead of showing the same item on a new line.

I do not understand VBA coding what so ever, so your help is much appreciated. Also if you can explain in detail how to achieve this the whole community could benefit from it. thanks
Attached File(s)
Attached File  Screenshot.png ( 9.72K )Number of downloads: 11
 
Go to the top of the page
 
GroverParkGeorge
post Jul 4 2017, 08:10 AM
Post#2


UA Admin
Posts: 30,750
Joined: 20-June 02
From: Newcastle, WA


Do you have the UPC field in the product table now?

If so, then it is a fairly straightforward task here. Create a query between products and purchase order details which includes that UPC field. It should also include the corresponding ProductID. Use that UPC from the product table to match the scanned UPC.

When you add a new UPC to the "scanned UPC" control on the main form, you can run VBA code that

a) looks for the corresponding ProductID in the purchase order details table, for the currently selected purchase order.
b) if that ProductID already exists for that purchase order in the purchase order details table, increment the quantity purchased.
c) if that ProductID doesn't already exist for that purchase order in the purchase order details table, add a new record containing that purchase order ID and ProductID, with a quantity of 1.
This post has been edited by GroverParkGeorge: Jul 4 2017, 08:11 AM

--------------------
Go to the top of the page
 
yeasir01
post Jul 4 2017, 01:33 PM
Post#3



Posts: 16
Joined: 15-December 16



Yes I do have a UPC field & a ID field as well. I know which steps need to be taken. I just dont know how to create the link between the input txtbox & the querry. can you be more specific maybe lead me in the right direction for VBA coding at least?? Thanks.
Go to the top of the page
 
tina t
post Jul 4 2017, 07:46 PM
Post#4



Posts: 5,169
Joined: 11-November 10
From: SoCal, USA


okay, so you know the steps you need to take, as also outlined by George's post. just break it down, and take it one step at a time:

a) looks for the corresponding ProductID in the purchase order details table, for the currently selected purchase order.

you can run a DCount() function against the details table, using the criteria section to filter for the primary key value of the current purchase order record and the ProductID. or you can write a Totals query using references to the form controls as criteria to, again, filter for the pk of the current PO# record and ProductID, and run a DLookup() function against that query. either way, your goal is to return a count of the instances of that ProductID in the details table, for the current PO# record.

b) if that ProductID already exists for that purchase order in the purchase order details table, increment the quantity purchased.

so if the count from the domain aggregate function is 1 (should never be more than 1) then update that specific record using an Update query - identifying the record by applying criteria in the Update query for the pk of the current PO# record and ProductID.

c) if that ProductID doesn't already exist for that purchase order in the purchase order details table, add a new record containing that purchase order ID and ProductID, with a quantity of 1.

if the count is zero (0), then run an Append query to add a new record to the details table, using the pk of the current PO# record as the foreign key value in the new details record.

requery the subform after the Update/Apppend action, so the new data will show in the subform.

i'd probably run the code from the UPC control's AfterUpdate event. scan/type the barcode and hit Enter or tab, the AfterUpdate event fires, and you're done.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
yeasir01
post Jul 6 2017, 08:16 PM
Post#5



Posts: 16
Joined: 15-December 16



What is the VBA to make this all happen?
Go to the top of the page
 
GroverParkGeorge
post Jul 6 2017, 09:09 PM
Post#6


UA Admin
Posts: 30,750
Joined: 20-June 02
From: Newcastle, WA


Why not see if you can come up with a sub. We'll help you flesh it out.

--------------------
Go to the top of the page
 
tina t
post Jul 6 2017, 10:46 PM
Post#7



Posts: 5,169
Joined: 11-November 10
From: SoCal, USA


as George says, give it a try. i learned to use macros years before i began learning VBA. when i started with VBA, i would often create a macro to do at least some of what i wanted, then use the "Convert Macros to VBA" option in Access to turn my macro into VBA, and then study the code.

i imagine that option is still available in newer versions of Access, though it may be called something different. the generated VBA is not the best, but it gets the basic actions done, and is a good learning tool. so if you have some experience with macros, that might be a good way to get started. regardless of how you go about it, you'll learn tons more from writing your own code, than you would if somebody simply wrote it out for you, ready to use.

hth
tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 
yeasir01
post Jul 12 2017, 03:27 AM
Post#8



Posts: 16
Joined: 15-December 16



ok this took a turn around I just realized that one product may have multiple UPC codes. I added a new table and linked all related UPC's back to the product table. so now when a UPC scan code is entered a search has to occur if the UPC is found then pull the related product & enter it into the PO detail section. Iv included a copy of the Database for your review.

Once more I dont know how to code, I dont know what a sub is & I dont know what an append query is. VBA is a language I dont speak im very limited on time & would appreciate someone's help in finishing this project off. I dont see a need to learn all ins & outs of MS access because I dont plan to make a career out of it, if I was I would attend a school for it. I'm just stuck with this one part of a project out of necessity would like someone to atleast get a code started then maybe I can figure out how to finish it???
Attached File(s)
Attached File  Buydown_Tracking.zip ( 112.03K )Number of downloads: 1
 
Go to the top of the page
 
yeasir01
post Jul 17 2017, 12:48 PM
Post#9



Posts: 16
Joined: 15-December 16



Wow, evertime I ask for help on this form I hit a brick wall. Thanks for the help crazy.gif
Go to the top of the page
 
GroverParkGeorge
post Jul 17 2017, 12:56 PM
Post#10


UA Admin
Posts: 30,750
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, the attitude expressed in your last post managed to discourage anyone from being too keen about offering more help....

"VBA is a language I dont speak im very limited on time & would appreciate someone's help in finishing this project off. I dont see a need to learn all ins & outs of MS access because I dont plan to make a career out of it, if I was I would attend a school for it."

While many of us are happy to help, encourage, tutor and explain, being asked to "...finish off this project off" for you because you don't feel the need to learn, probably wasn't something we expected to read here.

Make sense?

That said, I'm sure one of the good folks here will be along shortly and take an interest in the actual challenge of the problem. But unless you want to invest some of your OWN time and effort, it's probably not going to be anyone's highest priority today.

--------------------
Go to the top of the page
 
tina t
post Jul 17 2017, 02:36 PM
Post#11



Posts: 5,169
Joined: 11-November 10
From: SoCal, USA


i agree with George, except that IMO there's no challenge in such a simple goal. i come here to teach, and learn. since you don't want to learn, i have nothing to offer you. suggest you hire an Access developer to finish your project, since it requires more work than you're in a position to put into it. there are websites that can help you with that. good luck! tina

--------------------
"you can't take the sky from me"
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd October 2017 - 08:40 AM