Dlookup Issues

[Watson88]

Hi Guys,

I've browsed these forums already and have almost solved my issue by using the information I have already found although I still cannot resolve the last few errors in my Dlookup formula. I'm starting to get quite the headache now!

I am making a form (and subform) where I wish to enter supplier invoice information. The data is to be split between 2 tables when it is saved, a supplier invoice table and a order line table (so I can have multiple products against the same invoice number). In the subform, I wish to enter a product code and have its Description, Unit Type, List Price and Discount Price auto updated by using DLookUp.

For example, this is what I have in the control source for the description box on my subform;

=DLookUp("[Description]","tblBrickies","[Product Code]=" & [Product_Code])

The part of the formula [Product_Code] is referencing a text box on the subform. The reason why I have done it this way is so I can save Order Line information to a table separate from where the product information is listed.

I should add that before embarking on this project, I have had no previous exposure to access so to say my knowledge is limited would be a huge understatement! Any insight at all would be appreciated.

Regards,
Adam

 

[Minty]

Assuming your product code is a text value you will need to put escaping quotes around the value in the criteria.

=DLookUp("[Description]","tblBrickies","[Product Code]='" & [Product_Code] & "'")

This may be easier if your used a combo box to select your product codes, and pulled in the other fields (Description, , Unit Type, List Price and Discount Price ) on the combo, then simply display the other columns on the form using the after update event of the combo box.

 

[Watson88]

Thanks for replying so promptly!

I actually considered this approach originally. The problem I had though, is that I couldn't work out how to take information from one table and then save aspects of it to another. For example;

Product List table contains all of our suppliers product information with Product Code as the primary Key. I then wanted an Order Line table so I could capture how many items we were buying for each project. In this table I would need to capture Product Code, Qty and Price. The subform I tried originally used a 5 column combo box that referenced the Product List Table. Depending on what I selected from the Combo box, the other fields updated with the remaining column information. The only issue I had was that it wouldnt let me save the record as it was trying to create a duplicate entry on the Products List table instead of saving it to the Order Line table like I wanted it to.

The Product Code field is both Letters and Numbers although the field they are stored in is a Short Text field, would that mean I would need the quotes?

Thank you
Adam

 

[Watson88]

I gave it a try anyway, works perfectly!

You sir, are a life saver!

May I ask, what have those quotations actually done to the formula and why have they been placed in those positions in the formula?

No doubt I will have MANY more questions as I add more complexity to the database.

Thank you
Adam

 

[Minty]

The quotes are used to both identify and delimit the criteria text. Access requires quotes around strings 'Your Text' , # around dates #29/08/2016# and in many circumstances the dates need formatting as american format, have a read here for some of the issues http://allenbrowne.com/ser-36.html

With regard to your original problem with the combo box, it sounds like you may be suffering from having used a look-up field in your table. http://access.mvps.org/access/lookupfields.htm