Dlookup help needed

[mkb_cma]

I am making one form titled "Caravan List".I want the data for it's column "Desc" to be filled through using Dlookup whenever the Asset_ID matches the similar Asset_ID data of table titled "WorkingCaravanList".

I used the below but it didn't worked

Private Sub Description_AfterUpdate()
Dim Desc As Variant
Desc = DLookup("[Description_]", "WorkingCaravanList", "[Asset_ID]= '" & Table![WorkingCaravanList]![Asset_ ID] & "'")
End Sub



Please help.

[hertfordkc]

From your description of the problem, i expected to see
Desc = DLookup("[Description_]", "WorkingCaravanList", "[Asset_ID]= '" & [OtherAsset_ ID] & "'")

instead of

Desc = DLookup("[Description_]", "WorkingCaravanList", "[Asset_ID]= '" & Table![WorkingCaravanList]![Asset_ ID] & "'")

More info please.

[mkb_cma]

Thanks.

I tried

Private Sub Desc_AfterUpdate()
Dim Desc As Variant
Desc = DLookup("[Desc_]", "WorkingCaravanList", "[Asset_ID]= '" & [OtherAsset_ ID] & "'")
End Sub

But still there is an error message

"The expression After Update you entered as the event property setting produced the following error:The object doesn't contain the automation object 'Asset_ID.'.

[hertfordkc]

Thanks.

I tried

Private Sub Desc_AfterUpdate()
Dim Desc As Variant
Desc = DLookup("[Desc_]", "WorkingCaravanList", "[Asset_ID]= '" & [OtherAsset_ ID] & "'")
[OtherAssetID] was to be replaced by the id that you needed to match.
The error message suggests that [Asset_ID] isn't in the WorkingCaravanList?????
However, that may be a reference to OtherAssetID????
End Sub

But still there is an error message

"The expression After Update you entered as the event property setting produced the following error:The object doesn't contain the automation object 'Asset_ID.'.

Your original post suggests to me that "OtherAssetID" may actually be Asset_ID, i.e. the field names are the same in both tables. The original post has the Lookup comparing "Assest_ID" to itself.

[mkb_cma]

From your description of the problem, i expected to see
Desc = DLookup("[Description_]", "WorkingCaravanList", "[Asset_ID]= '" & [OtherAsset_ ID] & "'")

instead of

Desc = DLookup("[Description_]", "WorkingCaravanList", "[Asset_ID]= '" & Table![WorkingCaravanList]![Asset_ ID] & "'")

More info please.

For more info, please find attached the screen shots of the "Form" and "Table".

Regards,

[John_G]

First of all, when you put Dim Desc as Variant in your code, the DLookup function is assigning its return value to that variant, not a control on your form, so take that line out.

Assuming the names of the controls on your form are Asset_ID and Desc, then your DLookup should be something like this:

me!Desc = DLookup("[Desc_]", "WorkingCaravanList", "[Asset_ID]= '" & me!Asset_ID & "'")

John

[mkb_cma]

First of all, when you put Dim Desc as Variant in your code, the DLookup function is assigning its return value to that variant, not a control on your form, so take that line out.

Assuming the names of the controls on your form are Asset_ID and Desc, then your DLookup should be something like this:

me!Desc = DLookup("[Desc_]", "WorkingCaravanList", "[Asset_ID]= '" & me!Asset_ID & "'")

John

Thanks for the suggestions.I tried but problem is still there.Please find attached the screen shots of the updated code and error message.

Regards,

[John_G]

In your form design, what is the setting for the After Update property of the combo box containing the Asset_ID? It has to be set to [Event Procedure] in order for your code to run.

And what is the name of that combo box control?

John

[mkb_cma]

In your form design, what is the setting for the After Update property of the combo box containing the Asset_ID? It has to be set to [Event Procedure] in order for your code to run.

And what is the name of that combo box control?

John

Hi John,
Thanks for your help but i am still unsuccessful.Attached is the screen shot of relevent section of the properties of "Asset_ID".

Best Regards,

[John_G]

OK, that's fine. Now, what is the code in that Event Procedure?

John

[mkb_cma]

OK, that's fine. Now, what is the code in that Event Procedure?

John

Thanks John,

Attached is the screen shot of the code.


Please check.

Regards,

[John_G]

Hi -

The me!Desc_ = .... statement has to be in the After Update event of Asset_ID, not Desc_. Note that your control name is [Desc_] (with an underscore), not [Desc].

What you want to do is have the code change the description ([Desc_] every time you change (i.e. update) the Asset_ID; that's why it goes in the After Update event of Asset_ID. So, your VBA should be like this:

me![Desc_] = DLookup("[Desc_]", "WorkingCaravanlist", "Asset_ID = '" & Me![Asset_ID] & "'" )

This should work fine, as long as the field names are correct.

John

[mkb_cma]

Hi -

The me!Desc_ = .... statement has to be in the After Update event of Asset_ID, not Desc_. Note that your control name is [Desc_] (with an underscore), not [Desc].

What you want to do is have the code change the description ([Desc_] every time you change (i.e. update) the Asset_ID; that's why it goes in the After Update event of Asset_ID. So, your VBA should be like this:

me![Desc_] = DLookup("[Desc_]", "WorkingCaravanlist", "Asset_ID = '" & Me![Asset_ID] & "'" )

This should work fine, as long as the field names are correct.

John

Thanks John for your help and patience. It worked.