DLook up VB in a Sub Form

[smc678]

I have a form Form_Adj with a subform on it SubForm_Adj. When I have this form open and I type a numer in the sub form i want to use the following Dlook up to find the additonal info. It doesnt work when im in the Form but it does work in the sub form. Any Ideas?



Private Sub txtSKU_AfterUpdate()
Me.txtSKU_DESC = DLookup("[SKU_DESC]", "tbl_itemmaster", "[SKU]=Forms!SUBFORM_ADJ![txtSKU]")
Me.txtCOST = DLookup("[UNIT_PRICE]", "tbl_itemmaster", "[SKU]=Forms!SUBFORM_ADJ![txtSKU]")
End Sub

[June7]

Where is the code - main form or subform? Where is the textbox - main form or subform?

Concatenate variables. Reference to control is a variable. I assume SKU is a text field.

Me.txtSKU_DESC = DLookup("[SKU_DESC]", "tbl_itemmaster", "[SKU]='" & Forms!SUBFORM_ADJ![txtSKU] & "'")

Me.txtCOST = DLookup("[UNIT_PRICE]", "tbl_itemmaster", "[SKU]='" & Forms!SUBFORM_ADJ![txtSKU] & "'")

Better methods than DLookup to retrieve related info:

1. multi-column combobox with the SKU_DESC field as a column then textbox ControlSource can reference the combobox column

2. include the related table in the form RecordSource, join type "Include all records from {the primary data table} and only those records from {SKU table} that are equal" - bind textboxes to the related info fields and set them as Locked Yes, TabStop No

[smc678]

The code is in the subform and the text box is in the subform. What I have been reading is you have to ref the me for the main form then the sub form. This still dosent work?

Private Sub txtSKU_AfterUpdate()
Me!SUBFORM_ADJ.Form!txtSKU_DESC = DLookup("[SKU_DESC]", "tbl_itemmaster", "[SKU]=Forms!Frm_adj!SUBFORM_ADJ.form![txtSKU]")
Me!SUBFORM_ADJ.Form!txtCOST = DLookup("[UNIT_PRICE]", "tbl_itemmaster", "[SKU]=Forms!Frm_adj!SUBFORM_ADJ![txtSKU]")
End Sub

[June7]

You still have the control reference within quote marks and not concatenated. Review my examples again.

If the code and all the textboxes on the referenced form, can use Me. qualifier. If they are all on the subform there is no need to reference the main form. Unless you are using Navigation form, which is a whole different monster. I don't use them.

Me.txtSKU_DESC = DLookup("[SKU_DESC]", "tbl_itemmaster", "[SKU]='" & Me.[txtSKU] & "'")
M.txtCOST = DLookup("[UNIT_PRICE]", "tbl_itemmaster", "[SKU]='" & Me.[txtSKU] & "'")

Why are you saving this data anyway? Appears to be duplication.

Did you look at the alternatives suggested in earlier post?