MS Access 2010 - Barcode data capturing

[Bartvn]

Dear all,



I'm still struggling with populating the data in my form when scanning a barcode.
I have the the following tables:
- tblCustomer
- tblTrucks
- tblWorkorders

My table relationship is as followed:
- tblCustomers is linked towards tblTrucks (CustomerID from tblCustomers towards CustomerID in table tblTrucks)
- tblCustomers is linked towards tblWorkOrders through the CustomerID and the tblWorkOrders is also linked towards the tblTrucks (WorkOrderID from tblWorkOrders towards WorkOrderID in tblTrucks).

I have combobox that is working fine when selecting the customer, the data is populated with the contact information of the customer (except the vehicule information).

Can someone help me or give an example on how to populate a form based on a data entry (eventually barcode, but normal text should be the same) in a textbox?
As indicated above, the enter should be configured on the scanner, which I can look into the manual guide of the Datalogic scanner.

Thank you all for your help!!!!
Bartvn

[Dal Jeanis]

It seems like you need to look at the examples of cascading combo boxes. Start here https://www.accessforums.net/forms/c...ombo-3189.html

[June7]

Sounds like a circular relationship and that can cause issues.

Already offered in post 4 two methods of displaying related info.

What do you not understand about the suggestions?

Are you entering the barcode into a bound textbox to create a new record?

Or are you entering the barcode into an unbound textbox as search criteria?

[Bartvn]

Hi June,

Thx for your reply.
The barcode is indeed scanned into an unbound textbox and the objective is once the barcode is scanned a search is done to see if the vehicle is available in the DB or not. If it is available, the complete data is filled-in with the customer data (address, phone, etc.)

Thank you in advance and best regards,
bartvn

[June7]

Why not use a combobox?

Still don't understand purpose of this form. Is this data entry to create a new record?

After the search and vehicle is found as 'available', what should be happen? Are you creating a 'transaction' record of some sort? What is this database for?

[Bartvn]

Related to your first question (Combobox), we don't know the barcodeID's by heart. So we already made a Combobox based on the companyname, which works correctly.

The scanner can help us in saving time and also computer knowledge of the end-user. Once the truck is arriving, we scan the barcode and all the customer info is filled-in automatically on a work order (that's why we have a work order table). The only thing that should be done by the end-user is filling-in the description of the work done by the technician and the number of hours worked on the truck.

Once the hours en description is filled-in, the work order calculates automatically the workorder costs. (not the invoice, as the invoice is handled separately)

So to answer if we want to create a new record or not, this is a second handling that we would like to do, but than through another form. (frmCustomerTruck)
This last is not urgent, as we can scan them one by one and add them to the DB in a first stage.

Thanks for your support!
Bart

[June7]

This barcode is a vehicle ID? The vehicle is permanently associated with a particular company? It is this company info you want to retrieve?

First of all, should probably not be saving all that related customer info into record, just the VehicleID.

Second, scan the barcode into a combobox set up as multi-column. The columns will have company info. Use textboxes that reference the combobox columns so the info will be displayed. If possible scanned barcode not in the combobox RowSource list then use NotInList event of combobox to handle. This can be code to open a form to add the new vehicle record. http://www.blueclaw-db.com/access_no...ed_example.htm

[Bartvn]

Dear June7,

Please find hereby a copy example of my DB. I just included the important tables (Customers, Trucks & WorkOrders).
The objective is that when the company name is selected from the combobox, the information about the customer is displaying in the form. (contact information, truck information, workorder information).

This is working fine, but I also would like to have this when I put my cursor in the chassisnumber field of the truck & scan the barcode. The barcode information (letters & numbers) are in the truck table and when the barcode is scanned, I would like that the company information is displayed together with al the other information. If no workorder is made yet, the objective would be that I can create more than 1 workorder per truck and also be able to add different trucks for the same company, as a company can have more than 1 truck.

Thanks for you support.
Bartvn
Database1.zipDatabase1.zip

[June7]

You have TruckID in tblCustomers and CustomerID in tblTrucks but both are empty. What is the relationship? Each truck is associated with one customer? A customer can bring many trucks for service? If this is the case, remove TruckID from tblCustomers and populate tblTrucks with CustomerID.

Also, presume each truck can be serviced more than once. Remove WorkorderID from tblTrucks. TruckID should be in tblWorkorders, not CustomerID.

This doesn't even go near the situation where a customer sells their truck and you get to service it for another customer.

The chassisnumber is in rfmCustomerTruck subform. Subforms do not dictate parent form. The subform container Master/Child links synchronize related records. The main form already has the customer info. What you want to do is go to the customer record that is associated with scanned chassisnumber. However, you should already know who the customer is and opened the form to their record. Now you need to find that truck in the subform.

If you have a chassisnumber and the customer is unknown, cannot use scan into bound chassisnumber textbox to 'find' customer. Scanning into the textbox changes value of existing record or initiates a new record.

Could have an unbound textbox or combobox on the main form to scan the chassisnumber then use code to identify customer of record and then filter main form or go to that customer record.

[Bartvn]

Hi June,

Thank you for for the feedback. I Will adapter the tablets as you indicated.
Next to that, can you gide me through a step-by-step approach on how to create the unbound textbox working for the chassisnummer on the mainform as you explained?

thanks,
bartvn

[June7]

Combobox RowSource:

SELECT tblTrucks.Chassisnumber, tblCustomers.CustomerID FROM tblCustomers INNER JOIN tblTrucks ON tblCustomers.CustomerID = tblTrucks.CustomerID;

Other combobox properties:

LimitToList: Yes
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 1.0";0"

Code to filter the main form could then be like (column index begins with 0):

Sub cbxChassis_AfterUpdate()
If Not IsNull(Me.cbxChassis) Then
Me.Filter = "CustomerID=" & Me.cbxChassis.Column(1)
Me.FilterOn = True
End If
End Sub

[ItsMe]

....Next to that, can you gide me through a step-by-step approach on how to create the unbound textbox working for the chassisnummer on the mainform as you explained?

Here is something to get you started. There is a form named "Sample" you can scan a barcode into the first field and it will produce a subform with relative information.

Like June mentioned. You need to work on your referential integrety and relations in general. I placed a Customer ID value in the truck table just to get things started.
Sample.zip

[Bartvn]

Hi ItsMe,

Thank you for your support and help.
I tried to include your example into my DB and this seems more or less to work.
I still have another question, how can I link the workorder (from tblWorkOrder) to the corresponding customer and truck?

Is it possible to include an example in the sample DB you provided me?

Thanks for your help!!
Bartvn

[June7]

One way to link is by building a query that joins tables. This would be useful in a report output.

[ItsMe]

Hi ItsMe,

Is it possible to include an example in the sample DB you provided me?

I think the example is a little too complex to incorporate into your existing DB. Maybe you can get a main form with a subform working first. Maybe have a combobox select work orders. Then the scanner function could possibly help to locate the appropriate work order.

You may believe this is something you already have. I do not believe you have this working correctly yet. Like June mentioned, make a query. With this query you can reference it in your forms, etc.

If you get the results you want in a query, you will know you have the correct relationships with your tables. Building the query will help you to focus on getting the referential integrity correct. It will make you normalize your data. Do this as a first step.

That is my two cents....