Dlookup as a default value (1 Viewer)

[Shecky]

Hello.
I have used the following code as the default value for my subform field "Product_Desc":

=DLookUp(" [Supplies]![Product_Desc]  "," [Supplies] ",[Supplies]![TP_Prod_Code ]=[TP_Prod_Code])

To put it into words, I would like the default value for my subform field Product_Desc to be the Product_Desc from my supplies table, where the product code from a dropdown box in the subform is the same as the product code in the supplies table. I have tried using the Ctrl-Alt-Space to load the value into the form and nothing happens.

I am not sure if it is a syntax error or another issue.
TP_Prod_Code is an autonum field with a format of 'TPC'0000 on Supplies, and a number on PO_details with the same format. Data in the table appears stored correctly. Table designs are below.

Any comment on why the default value wont load? Records in the PO_details where the TP_Prod_Code combo box has yet to be entered/selected shows a #Name, which seems correct.

 

[Gasman]

Your Dlookup() is all wrong. Look up the syntax.
https://support.microsoft.com/en-us/office/dlookup-function-8896cb03-e31f-45d1-86db-bed10dca5937

Also, if you have the product code, you should not be storing the description as well, unless special reasons?

 

[Minty]

You shouldn't store the description in both places, on the form simply display the appropriate value from the combo drop down from your prod code value.

And a default value in a table can't reference another table to the best of my knowledge.
You could do it on a form, but only in the after update of the TP_Prod_Code.

 

[The_Doc_Man]

If you are saying that your autonumber is FORMATTED to read TPCnnnn but you are actually storing it as nnnn, then any lookups have to use the "as stored" rather than the "as formatted" value for doing lookups. It might be a pain in the toches to think along these lines, but NEVER use the formatted values for lookup. The only time formatted values should show up would be on forms or reports visible to the end users.

 

[Shecky]

Thanks for the replies people.
I do realize I am storing the data twice, but I need to keep record of what was ordered 'at the time as product descriptions can change, and I also want the user to be able to add a note that might be relevant to that specific order "Red caps only" or something similar.

As Minty suggested I think that maybe this would be better as VBA code on an AfterUpdate once the TPC combobox is selected.

Im concerned the actual error may be due to the way the data is stored as Doc_Man said, hence why I posted the data from the tables.

 

[arnelgp]

I do realize I am storing the data twice, but I need to keep record of what was ordered 'at the time as product descriptions can change

this must not change?
a product code is tied to a particular product description, 1:1.
if there is any light deviation in color, dimension, material class, you need to make new product code for it.
you don't change it, you create new one.

 

[Shecky]

Arnelgp: No the Prod_Desc must also be stored at the time of issuing of the purchase order. In case the description should change in the future for whatever reason. If there is a dispute regarding the PO, the exact text desc must retained.

I have tried to add code to my AfterUpdate event on the TP_Prod_Code combo box using the macro builder:
[Product_Desc]=DLookUp("Product_Desc","Supplies",[TP_Prod_Code]=[Forms]![Purchase_Orders]![PO_Details Subform]![TP_Prod_Code])

Nothing happens after update

Is changing another control by macro possible in the way I am trying? Must I use VBA?

 

[arnelgp]

if that is the "path" you want to take, it's ok, it's not my db anyway.
you can use macro or vba, vba would be much easier:

private sub tp_prod_code_afterupdatate()
me.product_desc = me.tp_pro_code.column(1)
end with

 

[Pat Hartman]

The most efficient way to show lookup data is to base the form on a query that uses a LEFT join to the lookup table. When you choose the value from the combo, the lookup is activated and the bound controls on the form will populate. Also note that this method does not store duplicate data nor does it require any code at all.

WARNING - when using this method, it is important to prevent accidental updating of the lookup values so always set the the locked property to Yes for all of the lookup fields.