Dlookup Help

[danian]

I have a simple sales form (frmSalesTransactions). When I enter a barcode number into the Barcode filed I would like the remaining fields (manufacture, Product Name and Cost) to automatically enter. All the required data is stored in tblProducts.

I understand this is done via a Dlookup on the “On Lost Focus” command.

I have never used Dlookup in VBA so some help would be great.

I have attached the database.

Thanks in advance

 

[danian]

Here's the attachment

 

[ScottGem]

I have a simple sales form (frmSalesTransactions). When I enter a barcode number into the Barcode filed I would like the remaining fields (manufacture, Product Name and Cost) to automatically enter. All the required data is stored in tblProducts.

I understand this is done via a Dlookup on the “On Lost Focus” command.

I have never used Dlookup in VBA so some help would be great.

I have attached the database.

Thanks in advance

There are three ways to DISPLAY data from another table on a form. Notice the emphasis on Display since, withth e exception of cost, you would not have anything other then the barcode in the transactions table.

One way is using a subform, but generally I would use that if I had 4 or more fields to display. The second way is using the Column property of a combobox. I would create a combo for you to enter the Barcode and add the other columns from your products table. then set the controlsource of the textboxes to:

=comboboxname.Column(x) where x is the number of the column (starting with 0).

Because you want to capture the cost at that time I would then use the After Update event to popualte the Cost control:

Me!txtCost = Me!cboProduct.Column(2)

The third way is using DLookups but I wouldn't use them in this case.

 

[danian]

Scott,

Thanks for the advice. I did what you suggested (point 2). the problem i am now having is that the data that is being put automatically into the 3 textboxes (manufacture,Product Name & Cost)is no longer writting back the the table tblSalesTransactions.

Also my Total cost textbox at the bottom is now longer calculating the total sum

PLease see attached database.

 

[ScottGem]

Scott,

Thanks for the advice. I did what you suggested (point 2). the problem i am now having is that the data that is being put automatically into the 3 textboxes (manufacture,Product Name & Cost)is no longer writting back the the table tblSalesTransactions.

Also my Total cost textbox at the bottom is now longer calculating the total sum

PLease see attached database.

That's because you didn't read what I said completely. Manufacture and Product Name should NOT be in the Transactions table. You already have them in the Products table. The only thing you absolutely need is the Barcode which identifies the product. I would include Cost since you would want to freeze the cost at the time of sale. I told you how to do that.

Some other tips. Date and Time are reserved words in Access and shouldn't be used for object names. Also its better to store the date and time together in one field. I'm not sure what Reference, Date and Time are doing in the Transactions table. Unless you only have one transaction per order, there shjould be a separate orders table. Finally, if you plan on using this for Inventory, you should be calculating on hand amounts by adding the incoming transactions and subtracting the outgoing transactions.

 

[Pat Hartman]

There are three ways to DISPLAY

Why does everyone forget that the query that joins the main table to the lookup tables is the glue that binds the normalized tables together? Only the cost should be obtained from the combo via code. All the other fields will populate automatically if you simply use a query that joins to the lookup table. Two warnings since you are obviously unfamiliar with this method:
1. Use a left join rather than an inner join if the join field is not required in the many-side table.
2. Set the Lock property to Yes for ALL columns obtained from the lookup table. You do not want them to be accidentally changed in this context.

NEVER use DLookup()s to accomplish this. It is very inefficient. Use the query with the left join for everything unless you actually need to duplicate a column such as cost and in that case, use a let statement in the AfterUpdate event of the combo to copy the cost from the combo's RowSource to the bound cost field.

 

[danian]

Scott,

I am having problems with the cost textbox. It does not seem to copy the data back to the table. You said to do the following. When i do this it does not seem to do anything.

Because you want to capture the cost at that time I would then use the After Update event to popualte the Cost control:

Me!txtCost = Me!cboProduct.Column(2)

It also does not do a total calculation it just gives #error

any ideas...

D

 

[Pat Hartman]

Is Me.txtCost bound to the correct table field? Check the ControlSource.

 

[danian]

Is Me.txtCost bound to the correct table field? Check the ControlSource.

Not to sure where to check. I have the following

Control Source = Cost (This is the filed i want the data to be added to on the table)

EVENT
After Update = Me!txtCost = Me!cbobarcode.Column(3)

Name = txtCost

Tried may different ways but still will not work any ideas...

 

[ScottGem]

I think I see, it looks like you typed

= Me!txtCost = Me!cbobarcode.Column(3)

in the After Update event. If so, that won't work. you need to use the Co9de builder and type it in like:

Me!txtCost = Me!cbobarcode.Column(3)

 

[danian]

ScottGem Thanks for the reply.

I have tried what you have suggested but still no luck. I have attached the database for you to refer to.

Thanks
Danian

 

[ScottGem]

I have a bit of a problem here. We have mader several suggestions but they don't seem to have been implemented. You still have Date and Time as field names. And there was NOTHING in the After Update event of the combobox. When I put in the code as I suggested in my last response it worked perfectly. leading me to wonder if you actually tried it.

As for the total, make the formula =Sum([Cost]). One final point. Don't use the Table as your RowSource. Create a query, event though you will be using all the fields.

 

[danian]

ScottGem Thanks for the reply.

Didn't fully understand where the data need to be put. I have looked at a sample database that you posted a little while ago for someone else. Looking at that database I realise I was not doing what you suggested in previous posts.

Thanks for you help and patience
Danian

 

[ScottGem]

glad to assist