Multiple Inventory Entry

[DNASok]

First off, please forgive me as I am new to access and believe this has been asked for similar situations. I just can't get the previous support responses to fit what I need.

I have a simple "Inventory" Table which contains:

1. ID (primary key)
2. Item
3. Part Number
4. Serial Number
5. Received (date)
6. Unit Cost
7. Shipped (date)
8. Condition Shipped
9. POC Name
10. POC Address
11. POC Telephone
12. Loaned/Ordered

Here's what I want to do:

1. Single form to enter multiple serial numbers of a received item
2. Single form to change multiple serial numbers status to either "Loaned" or "Ordered"

For example, user receives 20ea of item A with part number X on date dd-mmm-yy. User would open form, enter item, part number, date received, and unit cost; then would enter each of the serial numbers for the 20ea received items. User would finally select a button which would input all this data into the "Inventory" Table.
Once I do this, I will need to make another form which allows multiple serial numbers of an item to be selected to process for shipment.
For example, user ships 7ea of item A with part number X on date dd-mmm-yy. User would open form, input the pertinent shipping information, select the item from a drop down list, then select the serial numbers which status will be changed to "shipped" or "loaned".

Again, I'm new, so some step-by-step may be needed. Any help would greatly be appriciated!

[TG_W]

Welcome to the forums, DNASok!

Comment 1: Except where it appears you have noted "(date)", are these the field names you are using? If so, I would suggest removing spaces and changing the "/" to an "_". this would make using code easier. You can use abbreviations for your field names in your table and show a full name in your forms. The field names really only need to make sense to you.

Comment 2: It appears as though all of the information will remain in one table, which is fine, but it appears users will be entering shipping information for each item, so they could be changing the status while entering the shipping information. Do all of the items have unique serial numbers? If so, it might be better to have three tables to track inventory only in one, receipt information in one, and shipping information in one.

These are just some blanket observations. With more info on data, better, more concise help can be provided.

[DNASok]

Thank you!

Comment 1: Noted, but these were not the actual field names "(date)" was only to say these are date fields.

Comment 2: The items could have the same name and part number, but the serial numbers for eacy are unique; i.e. There could be an item DOG with part number 123456789 with serial numbers 1, 2, etc... There could also be an item CAT with part number 987654321 with serial number 1, 2, etc...

Thank you for looking at this soo quickly. I have attached the database which currently only consists of the table.

[TG_W]

Are you tracking the inventory as it is received in and shipped out?

[DNASok]

Yes, I actually need to track them three (3) ways:

1. As assets come IN
2. As assets are sent out LOANED
   
   • LOANED assets will either be transferred back to IN if they are returned, or ORDERED if they are to be kept and funds are sent; i.e. excersize is over or excersize turns into a indefinite need
   
   
3. As assets are sent out ORDERED
   
   • ORDERED could come from either the IN or LOANED assets as a loaned asset could turn into an ORDERED asset if/when money is sent for what they borrowed

[TG_W]

I used some of the stuff I put into one of the tracking databases I built to track documents and their transmittal history. I split your main table into 3 tables (Inventory, Shipped, and Received) that are all linked by the Serial Number, as well as shorted many of the names or removed spaces/characters as I suggested in my initial reply. The Serial Number is at least one of the Primary Keys on each table as these should be unique.

I hid the Navigation Pane so other users will not see the tables and forms on the side. Just press F11 to open and make edits.

The database will open to a main switchboard. On the switchboard, I put a few place-holder buttons, one button that opens the Inventory form, and one exit button. The "Exit" button will open a form to confirm exit from the database with a cancel and exit button.

On the inventory form, you can add, delete, and edit inventory items. If an order comes in for 20 items with 20 unique Serial Numbers, the first row of information can be added and the "Duplicate" button pressed. The record will copy down everything, including the Serial Number, but it will not be saved if the Serial Number is not saved. This will prevent duplicate Serial Numbers in your database. There are some search boxes at the top that are set to filter after updating the text boxes, i.e. pressing the "Tab" or "Enter" keys or clicking outside of the box. I didn't add it, but it might be good to filter for items without the "Ordered" status as those should technically no longer be in your inventory. You will notice a "Select" field to the left of the form. Items that will go on 1 shipment can be selected, and the shipping information can be entered for muplitple items at once by selecting the "Enter Shipping Info" button at the top. It will open a form and allow for the user to enter all shipping information and process that information saving it to "Shipped".

Now, I did add the "Loaned/Ordered" filed to the "Shipped" table as well as the "Inventory" table. If you update in one, you need to manually update in the other. I did it this way to allow you to save the status it was shipped with while the current status is saved in the "Inventory" table so you have the full history of the item (as shipped vs current). this can be changed, though.

The same form can be created for Received Items, and I added a button for it but did not create the form. I also did not create forms to view the shipping or receiving history. I had enough time this morning to plug what is attached together since I have most of built in another database already, but I put some notes in the VBA code (identified by the green font) so that you can look through it and add the forms as desired, as well as add search criteria, etc.

I hope this helps.

[DNASok]

I cannot thank you enough, so all I can say is thank you. I am getting the following error when I attempt to process a shipment!

[TG_W]

You're quire welcome. Glad it helps.

I forgot to rename the field in the form. In "frmShip", go to Design View and left-click on the text field next to the label for POC Name. In the Properties Sheet, go to the Other tab and change the Name to "POCName". You will actually need to do that for all of the POC fields. Sorry about that.

[DNASok]

No worries, just caught that myself as well. Again, thank you very much for all this work!

[DNASok]

TG_W,

I am getting an "Index or primary key cannot contain a Null value" error when I attempt to use the "Duplicate" function you added. If it hit "End" it will add the duplicate, but I was wondering how I can remove this warning or correct it.

[TG_W]

That's strange. It didn't do that to me yesterday when I tested it, but it's doing it to me today, as well. Let me play with it some and I will get back to you. I apologize for the error.

[TG_W]

Ok, the two filters are the problem since they are set to filter on AfterUpdate. The problem seems to be that after the copy/paste commands, the form is trying to set the filter on. You can make the changes a couple ways, but the AfterUpdate needs to be removed.

1. You can add a button to kick off the search. You just need to cut and paste the code for AfterUpdate to the button and delete the AfterUpdate.
2. The changes I made was to kick off the filter after double-clicking the Text Box by cutting and pasting to the Double-Click and deleting the After Update.

Let me know if you need further direction. That should fix the problem, though.

[DNASok]

attachment wont DL...says "Invalid Attachment specified. If you followed a valid link, please notify the administrator"

[DNASok]

nevermind...working now...I'll check it out!

[DNASok]

Now when I double click an error comes up stating "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the filed or fields that contain duplicate dta, romove the index, or redefine the index to permit duplicate entries and try again."