Need help with dlookup.

[Keith]

Hi everybody. This is my first post here. Thanks to all who donate their time and knowledge.



I work at a dry cleaner. We still use 3-part carbonless forms to hand enter order details and amounts. There is no record keeping to speak of, not even customer info; I have to ask repeat customers for their phone number every time. So I have a used Windows 98 PC I want to put up at the shop and start a database.

I started by creating a customers table, and an invoice table. They are related so that my form has customer info with a subdatasheet with their invoice number, date of drop, total price, yes/no paid, and date of pick up as fields. This all works pretty well; I would just enter that data from the invoices at the end of each day.

I would like to take it a step further and enter the actual order details (2 pants, 3 shirts, etc.) and the subtotals directly into the database with these order details as a subdatasheet of the invoice subdatasheet.

My problem is with trying to look up the price of a garment as I select the item from a dropdown list. I have an Items table with fields ItemID, Item, and Price. And I have an OrderDetails table with Quantity, Item, and Price fields. I have entered about six items and their prices to get started. I have created a lookup list based on the Item column in the Items table, but I am unable to get the price to automatically populate the Price field in the OrderDetails table. (Eventually, I will have to figure out how to calculate the Quantity * Price to get a total and then relate it to the proper invoice number and proper customer, but I can't get past the dlookup).

I have created a form from the OrderDetails table. In design view, I right click on the Item field, go to properties, event, afterUpdate and enter this:

Private Sub Item_AfterUpdate()
Price = DLookup("Price", "Items", "Item=" & Item)
End Sub

My research shows this is the way but I get multiple error messages. I even sometimes just get the name of the item instead of the price. I have tried adding [] around some fields, to no avail.

Any ideas? Thanks for the help.

Best wishes, Keith.......

[pbaldy]

You can see if this helps:

DLookup Usage Samples

but the better answer would be to include the price field in the rowsource of the combo and then have this:

Me.PriceField = Me.ComboName.Column(x)

where x is the column containing the price (the column property is zero based).

[Keith]

You can see if this helps:

DLookup Usage Samples

but the better answer would be to include the price field in the rowsource of the combo and then have this:

Me.PriceField = Me.ComboName.Column(x)

where x is the column containing the price (the column property is zero based).

Thank you the reply. I get error message "property not found." I know I'm lost so that's not surprising.

In the form that I created from my OrderDetails table I have form fields ID(autoNumber), Quantity(number), Item(text), and Price(currency).

The only combobox in the form is the Item field which already has the value ItemsT in the rowSource property to provide data (the items) for the drop down list. This combobox is named simply, Item.

The other fields are text boxes so I assume I need to put the syntax you provided in the rowSource property of the combobox named Item, thereby replacing the ItemsT that's there now.

I further assumed that I had to change the syntax you provided (Me.PriceField = Me.ComboName.Column(x)) to match my field names, so I now have Me.Price = Me.Item.Column(3) in the rowSource property of combobox Item.

What am I doing wrong?

Thank you for your help, Keith.......

[pbaldy]

The rowsource property either needs to be a table, query or SQL statement that returns the desired records. What I posted was intended to be VBA code in the after update event of the combo:

http://www.baldyweb.com/FirstVBA.htm

[Keith]

Nothing is working. I am disgusted. It simply will not add the price of the item I select. I guess I will just manually add the price of the item so I can move on to calculating the total and relating the order to a customer.

Thank you for your help.

[pbaldy]

Can you post the db with your attempt? We should be able to fix it.

[Keith]

Thank you. I'll do that, but I want to work on it some more first. I am having pretty good success except for that issue.

Thanks again, Keith......

[evander]

Keith,

Private Sub Item_AfterUpdate()
Price = DLookup("Price", "Items", "Item=" & Item)
End Sub

By saying that "Item = " & Item, you are actually referring to the actual primary key in the combo box, not the currently displayed text value.

So for example, if I have a combo box that derives its drop-down data from a table named Product containing the following:
ID Product
1 Apple
2 Orange

And, let's say I create a command button that executes the following code (assume that the currently selected product is Apple):
Msgbox Combo0

You will get 1, not Apple.

In your case, you are comparing an ItemID (combobox value) with a text table field (Item).

Hope this helps.

[Keith]

That was it, evander! I just added "ID" before the =. I'll probably have more questions, but this issue is SOLVED.

Thanks to all!!!