Access beginner needs to build an Inventory Tracker/Manager.

[JoCoR]

Hi all. I consider myself an above average Excel user, self taught through experience and by searching for answers on google and forums. I am, however, humbled by how much I'm struggling trying to figure out Access. I know they are two different kinds of beasts, but Access is making me loose my mind.

I am trying to build an Inventory database, where we can keep track of reagents we receive, when are they put in use and by whom. My plan is to use a barcode scanner to scan the QR barcode that comes with each reagent box, and have Access split/parse the barcode string into 3 specific portions that we need:

1. Reagent Reference # is the string that will identify the reagent, specific for each one, and which will never change.
2. Lot Number is the reagent's lot number, which changes frequently.
3. Expiration Date is the reagent's shelf expiration date, which also changes with each lot.

I already made a Database with all the Reagent Reference # with the pertinent information, like Reagent Pack Name, Description, Type of reagent and which analyzer uses it.

What I want is to have a field in a form where I scan the QR code and it will automatically grab the strings that are needed from the barcode, use the Reagent Reference # to match it to the corresponding reagent and bring up the name and description into another field in the form. Then the user just enter quantity received and miscellaneous information, hit Save and the data will be added to a Table log for tracking purposes.



I'm guessing the part I'm having issues with the most is determining table relationships and building queries.
I'm attaching a very crude version of what I have so far, which is just the tables and a plain form. I managed to do what I want in Excel. My brain is still struggling to comprehend that Excel and Access are not the same, so my approach is still very Excel oriented.

Any help would be greatly appreciated.

[June7]

Good that you are not using spaces in naming convention. Should also avoid punctuation/special characters (underscore only exception) - one field has "/" character. Shippment should not have two p's. NewLot/Shippment - better would be NewLotShipment or NewLot_Shipment

QR codes and bar codes are not really the same. QRcodes may be trickier than barcodes in translating to a text structure that can be saved into database. Certainly more difficult for Access to generate the graphical output for printing. Translation must be done by the scanner software for saving to database. Scanner is an input device just as a keyboard and mouse are input devices. Need the right drivers.

Code behind textbox will have to parse string to its parts and save to fields. Or just save full string and calculate substrings when needed.

For an introduction to inventory control, review http://allenbrowne.com/AppInventory.html

[ssanfu]

Things I saw:

* "tblReagentDB.Reagent Type" has a space

* The caption property for "tblReagentLog.Reagent" is "Pack Name & Description". In Access, the "&" is a hot key. So the caption would have to be "Pack Name && Description". But I would recommend the caption property NOT be used. Use the caption property of the control label instead.

* You shouldn't use look-up FIELDS in tables, nor Calculated fields in tables.
I would also recommend against Value Lists in tables. Since I never use a form in Datasheet view, it is a waste of time to create the value list in a table, then having to create the value list again in a combo box on a form.



You need error handling code for when the "ReagentReferenceIDParse" is not in the "tblReagentDB" table.



My $0.02.......

[orange]

I agree with the comments given so far. I recommend creating of a draft data model based on your requirements
that can be tested with pencil and paper (where it is much easier to adjust) using some sample data and scenarios.
See the Database Planning and Design link in my signature for several articles that might be helpful to your project.
You may also find some insight in this free Stock Management video from Software-Matters.
Good luck with your project.