Hi
I'm creating a database for a veterinary practice drug stock control. Basically I have an intake form of which I use a barcode scanner with keyboard wedge to input the data from a drugs barcode, into each relevant field.

For Example you blip the barcode scanner and it automatically fills in each field one after the other:

Product Code
Product Description
Batch Number
Expiry Date

This then oviously updates the drug input table with that drugs parameters.

The problem I have is when it comes to drug outtake. I need to able to create a form with the same fields as the intake but blank. So the user can open the outtake form, blip the barcode scanner which will fill those fields in and then have a search button which will then search the "batch number" field within the drug input table and bring up that batch number and drugs parameters.

I've created searches using queries, i.e. box appears asking to enter batch number which in turn brings up that drug. but this is no good as i need to be using the barcode scanner to input the details.

Can anyone help?

First, It sounds like your database is not properly normalized. If I understand you, you have a products table that contains the information you listed above. You then have an intake table that you enter info in according to the barcode, and an outtake table that you want to do the same.

First, I would not have separate intake and outtake tables. I would have one transactions table with a flag that indicates whether its an intake or outtake. Second, the ONLY thing you need in this table from the products table is the Product ID as a foreign key. Anything else can be pulled from the products table using a join. If you want to display this info when scanning, you can use a subform.

As for the searching, that's easy, once you have the barcode scanned into a control or variable, you can then use that variable within a DLookup to get the Batchnumber and input that as a foreign key.

HTH

I only use the one table for intake and outtake, which is (stupidly names i know) "drug intake"
Could u explain:
"you can then use that variable within a DLookup to get the Batchnumber and input that as a foreign key."

What is a DLookup?

Many Thanks

If you see us mention a function or command that you don't recognize, it would help to look it up in Access help. Generally that will get you the answer. If you still have questions after reading the help, you can ask further.

DLookup is a function that will return a value from a table or query based on supplied criteria.

Also its not a good idea to use spaces in object names. You might want to take a look at naming conventions (see here).

ta