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.......