Verify Records (1 Viewer)

tannerwhitesell

New member
Local time
Yesterday, 23:02
Joined
Apr 7, 2021
Messages
4
Looking for suggestions to make my job a lot easier.

I work for a bus manufacturer and we have an access database to manage all of our production jobs. Each job is made up of options similar to if you would go to build and spec a car at a car lot. Within each option are materials that make up our bill of materials. My job is to update those materials to accurately reflect what we make on the manufacturing floor. However when I am entering the updated materials into the option if the material does not exist in the inventory table then it will throw me an error. Right now I am either using conditional formatting in excel to verify that they exist in the table or searching them by hand in the database. Does anyone know of a better way to paste values into an application or something that I can paste these updated materials in to verify that they are in fact in the inventory table? If they are not in the inventory table I would like to return a PN and then false or have the script run an insert statement to add them into the database. I might just be stuck with returning the value as false because I will have to add cost and vendors etc to the record.

Let me know your thoughts.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:02
Joined
Feb 19, 2002
Messages
32,908
Usually material doesn't materialize out of nowhere. Do you not know ahead of time that you have some new material?

It is also not clear whether you are using Access to manage the inventory or Excel. During the process of data entry, sometimes it makes sense to allow new items to be added to a lookup table on the fly but in most cases it does not. Can you clarify for us please. If you are using a form with a combo to do the data entry, you can use the NotInList property of the combo to add new items on the fly. That option isn't available with copy/paste since you have no form events you can use in which to run code.
 

tannerwhitesell

New member
Local time
Yesterday, 23:02
Joined
Apr 7, 2021
Messages
4
Usually material doesn't materialize out of nowhere. Do you not know ahead of time that you have some new material?

It is also not clear whether you are using Access to manage the inventory or Excel. During the process of data entry, sometimes it makes sense to allow new items to be added to a lookup table on the fly but in most cases it does not. Can you clarify for us please. If you are using a form with a combo to do the data entry, you can use the NotInList property of the combo to add new items on the fly. That option isn't available with copy/paste since you have no form events you can use in which to run code.
You are correct however we don't really have a lot of data integrity. The last person that had my job before me took the easy way out and put in a cost placeholder for a bunch of options. It is a misc part number that they use in the option instead of adding a new part to the inventory table. Attached to this reply is a screenshot of the form that we use to enter parts into the option. We are trying to add actual part numbers and cost instead of using the MISC option so we have a more accurate bill of materials. Often times when I am updating these options I have to find what that MISC part is and add it to the inventory table so it can be added to the option.
 

Attachments

  • Capture.JPG
    Capture.JPG
    96.5 KB · Views: 42

tannerwhitesell

New member
Local time
Yesterday, 23:02
Joined
Apr 7, 2021
Messages
4
Usually material doesn't materialize out of nowhere. Do you not know ahead of time that you have some new material?

It is also not clear whether you are using Access to manage the inventory or Excel. During the process of data entry, sometimes it makes sense to allow new items to be added to a lookup table on the fly but in most cases it does not. Can you clarify for us please. If you are using a form with a combo to do the data entry, you can use the NotInList property of the combo to add new items on the fly. That option isn't available with copy/paste since you have no form events you can use in which to run code.
We use access to price our jobs. I use excel only to analyze the data in access by exporting the table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 23:02
Joined
Feb 19, 2002
Messages
32,908
Your initial post sounds like you are importing data from a spreadsheet? Is that what is going on? Maybe you should go into more detail regarding the process and what you are doing when you get the error.
 

Users who are viewing this thread

Top Bottom