matching data (barcodes) in a datasheet view and show extra info automatically

[dennisb82]

I'm relatively new to Access. I started a little project to learn Access. I try to simplify the description.


The first goal of my project is to match the order data from the customers with the barcodescans and then print reports. The orders table gets filled every day with data from customers.

Tables:
"tblOrders” (OrderID, customer/storenr, barcode, product, quantity , planned shipping date)
“tblRoutes” (customer/storenr, routenr, sequence)
“tblStores” (customer/storenr, customer, storenr, address)
“tblMutations” (barcodes, date/time, remark)

Now, I want to fill the tblMutations with a form (datasheet view) by scanning barcodes. Thebarcode scanning it just like typing and an Enter. After putting in the barcode (with the scanner) I want to automatically show information of other tables in the record of the form (like, customer, store, product, quantity, route) that are related to that barcode.

I searched en tested for days with comboboxes/auto populate/query’s. There is a lot to find on the internet, but I didn’t manage to get what I want. Maybe someone can put me in the right direction?

[ranman256]

you have a form open,
the 0 tab index (the control that gets the cursor 1st) will be your input box
scan barcode
barcode fills and does the search. (you can program barcode reader to add the 'enter' keystroke)

[dennisb82]

you have a form open,
the 0 tab index (the control that gets the cursor 1st) will be your input box
scan barcode
barcode fills and does the search. (you can program barcode reader to add the 'enter' keystroke)

Thanks ranman256. This is looks like what I want to have. Now I have a form form imput. I used: http://www.baldyweb.com/Autofill.htm

Some "problems":
-I can choose from the box, but it takes automatically the first in alfanummeric order. How can I choose the others?
-Also I don't understand how I can select the fields I want to store in my tblMutations.
-Is there a possibility to enter A unknow value? Now I have to choose some available.

[June7]

With your table structure, tblOrders and tblMutations would be linked on the common barcode fields. Combobox to scan barcode into would have a RowSource that references tblOrders joined to tblRoutes and tblStores as RowSource and textboxes referencing combobox columns.

If you want to provide db for analysis, follow instructions at bottom of my post.

[dennisb82]

With your table structure, tblOrders and tblMutations would be linked on the common barcode fields. Combobox to scan barcode into would have a RowSource that references tblOrders joined to tblRoutes and tblStores as RowSource and textboxes referencing combobox columns.

If you want to provide db for analysis, follow instructions at bottom of my post.

Thanks for the tip! I attached the database.

My current questions regarding the frmMultations and the tblMutations
-I don't understand how I can select the fields I want to store in my tblMutations. The tblMutations is not filled correctly with scan-data-time and Remark.
-I want to be able to overwrite the columns that are auto filled. Is this possible?

[June7]

Your attachment is corrupted and won't download.

What is the barcode for - a product identifier? Why don't you have a Products table?

[dennisb82]

Your attachment is corrupted and won't download.

What is the barcode for - a product identifier? Why don't you have a Products table?

Hi June7. I uploaded the file again. Please let me know if it works.

I only have 3 products. For me it is not interesting what they are. The mail thing is to match my barcodes (with related data) with the file of the customer.

My current questions regarding the frmMultations and the tblMutations
-I don't understand how I can select the fields I want to store in my tblMutations. The tblMutations is not filled correctly with scan-data-time and Remark.
-I want to be able to overwrite the columns that are auto filled. Is this possible? Where can I store there overwrites? I don't want to overwrite the tblOrders

[June7]

So these barcodes do not identify products, what do they identify? They seem to be a unique identifier for tblOrders.

The ControlSource for Scan-Date-Time textbox should be the field, not an expression. Put the Now() function in the DefaultValue property.

Set Customer, Storenr, Product, Qty as TabStop No. Why would you want to duplicate these fields into tblMutations? Why would you want to change the retrieved values and store in tblMutations? If you want to be able to 'overwrite' then you need fields in tblMutations and bind textboxes to those fields. What is purpose of tblMutations? Exactly what are you trying to accomplish?

[dennisb82]

To clarify what I want to accomplish in the first fase of my learning project: We receive shipments and orderfiles from customers. In these order files we get the barcode, storenumber, shippingdate en quantity. We put these files combined in the tblOrders. We sort this shipments by route and store. The stores of different customers are sorted in one route. We scan the barcodes to match with the orderfiles.

The barcodes identify the store, product and quantity. With the scanned barcodes I want to make the documentation for the drivers.

The scanproces must be very flexible. The user must have the ability to change all things in the form. For example: We’ve seen barcodes that are not matching the product or quantity. So we have to make a change. After all scanning is done, I want to have the ability to check the changes. Therefore I thought I have to store duplicates and overwrites in tblMutations. Any tips to do this?

I put the Now() function in the DefaultValue Property. TabStops to No looks handy too. Thanks.

Now I’m struggling with the combobox. With the formfields that are filled with the combobox (barcode) I can’t make changes to Customer, store, product, quantity. I tried to get it working with Dlookup (AfterUpdate barcode) to see If I can overwrite, but I can’t get dlookup to work.

[June7]

Would have to use code to populate the textboxes. These textboxes would have to be bound to fields in tblMutations to save the data.

VBA code like:

Me!Customer = Me.combobox.Column(1)

A combobox for the barcode implies users could select barcode from list. However, if you don't want this, maybe a textbox is better approach. Then multiple DLookups to set the other fields.

Me!Customer = DLookup("Customer", "tblOrders", "barcode='" & Me.tbxBarcode & "'")

The real trick is figuring out which event to put code into. Perhaps the barcode textbox AfterUpdate.

Advise no spaces or punctuation/special characters in naming convention. So Customer_StoreNr would be better than using /.

[dennisb82]

Yes! this is it. I manage to populate the boxes with the code and After Update. For the com0bobox and with DLookup. Also I can overwrite now!
Also when I'm overwriting a field.

Dlookup seems the way to go.
I had to store all the fields, or else when I fill the next record, all previous record gets the new customer, storenr, product and quantity.

Thanks for pointing me in the right direction.

[dennisb82]

Is there an easy way to check for double or unknown barcodes (empty dlookup fields)?
It can be accepted, but I want to play a sound for attention?

[June7]

Could use DCount() or DLookup() to check if barcode already exists.

Use IsNull(fieldname) to check if field is Null. I don't allow empty strings in fields so I don't check for that. Empty string and Null are not the same thing so if empty string is a concern, the following will handle both Null or empty string: If Me!fieldname & "" = "" Then

Can use DoCmd.Beep to play a sound.

[dennisb82]

Thanks June7! I learn a lot. I used the DCount and If. Also put in the sound.

I have one more challange for my frmMutations.

After sorting the products by route and store, sometimes a product is placed at the wrong shop. With my current frmMutations I can't notice that.

At the moment I use a complex Excel (no VBA) file and before I scan the barcodes I first scan the barcode witch has the Custstr in it.
This is placed in a column until the new Custstr is scanned. This is done with an If formula that checks the customers name.
Then when I scan a barcode of a product, It checkes the Orders if it has the right Custstr in the next cel of the row.

With your experience: is there a way to check if the barcode (product) is placed at the right store in my frmMutations?

[June7]

Sorry, don't understand the process you described.