I am trying to create a form that will do the following:
1) User will enter a UPC code via a wedgescanner into Form box
2) I want that 'UPC code' to verify against a table that contains UPC codes and if the Code is there, to return the associated 'Item Number' in the next box, if code is not in table then return 'error'. However, there may be more than one Item Number for a particular Code, So I would want a drop down box to appear in the 2nd box with all the potential Item Numbers listed. The user would pick the correct one. (So if only one item is related to code, then only that item would appear in drop down box)
3) Upon sellecting the correct 'Item Number' from the drop down box, the associated 'Item Description' would be brought up in a 3rd box
4) User to enter a 'Location' - not dependent on a table
5) User to enter a 'Quantity' - not dependent on a table
6) then this Data will be need to be added to a table that will be tracking all this data.
7) then move back to UPC code box.
I have been hacking about for a couple of hours on this and figure if I can at least be able to type in a UPC then tab to next field that a item# would pop up, I would be half way there....
I have a decent working knowledge of tables, queries, and macros but have had very limited experience working with forms, thus my question (or at least the start of possibly more questions).
I would welcome any help or direction in this project.
Your Item field will be a combo box without a RowSource. Add some VBA code to your Code field activated by an AfterUpdate or a LostFocus event which will create an SQL SELECT statement string (strSQL, say) searching for the relevant items. Include an ORDER By clause, if you like. Make this statement the current RowSource by including the following actions at the end of your VBA code:
After you have entered the other items, create an SQL INSERT statement to put the data into a table. You can either run this from a command button, or put some code into each of the other fields to check that all fields are populated (again using LostFocus or AfterUpdate) and then run the statement with the following VBA instruction
I do this a lot. I have multi-level lookup tables which I edit with an unbound form containing a subform bound to the appropriate table. Sometimes I have as many as three combo boxes in the main form, each populating the next in the manner I have described. The last one populates the subform.
One last thing. In the Close Action for your form, reset the RowSource to a blank string. The reason for this is that Access closes down the form in a funny sequence, and because your Code field will have been shut down before the Item field, it will prompt you for a parameter to enter. Harmless, but irritating.