derekstewart
MIS
I have a database containing procedure data and the associated products used in each procedure. These tables are linked on an autonumber procID. The product info is further broken into a product and description table linked on item number to consolidate lengthy repetitive descriptions. I have a form which draws info for the procedure table, such as date and invoice number and total cost. A subform based on a query which combines both Product and Description tables, providing the vendor item number and cost info for each product with the description.
The subform item number field uses a combo box allowing users to select the product they need. The description is auto populated, and the cost and quantity left blank for the user to key in. The trouble is occurring when the user needs to enter an item number qty not currently in the description table. An error message will occur saying “the text you entered is not an item for the list”. This is due to the item number being the primary key of the description table. When this happens I run a macro which opens another form tied to the description table so the item can be added to the table. Then the thought is that I would return to the original form and add the record. The problem is that the form needs to be refreshed. If I close it and open it again I can select the newly added item. If not, then the record doesn’t appear in the drop down. There are almost 1000 procedures and 3500 products in the database that need to be periodically worked. If I had a macro close then reopen the database after a record is added, how do I get it to automatically return to the record they were working on? Finally, is there an easier way to do this? I’m thinking yes…. Really I need the subform vendor item number combo box to lookup a product if one currently exists returning the description and if not to automatically add the vendor item number and the description to the file so the next time it is needed it will appear in the combo box. Sorry for the novel, but any thoughts? Thanks. Derek
The subform item number field uses a combo box allowing users to select the product they need. The description is auto populated, and the cost and quantity left blank for the user to key in. The trouble is occurring when the user needs to enter an item number qty not currently in the description table. An error message will occur saying “the text you entered is not an item for the list”. This is due to the item number being the primary key of the description table. When this happens I run a macro which opens another form tied to the description table so the item can be added to the table. Then the thought is that I would return to the original form and add the record. The problem is that the form needs to be refreshed. If I close it and open it again I can select the newly added item. If not, then the record doesn’t appear in the drop down. There are almost 1000 procedures and 3500 products in the database that need to be periodically worked. If I had a macro close then reopen the database after a record is added, how do I get it to automatically return to the record they were working on? Finally, is there an easier way to do this? I’m thinking yes…. Really I need the subform vendor item number combo box to lookup a product if one currently exists returning the description and if not to automatically add the vendor item number and the description to the file so the next time it is needed it will appear in the combo box. Sorry for the novel, but any thoughts? Thanks. Derek