DLookup Syntax - weeping at this point

[cstanley]

Hello all --

I have a table named tblSystemInformation and a table named tblBuildingCodes. Items in the SystemInformation table have a code assigned to them; I would like the description matching this code, in the BuildingCodes table to appear in a text box (called BuildingName) when the combo box displaying the building code is updated. So I used the following Dlookup statement:


=dlookup("[Description]", "tblBuildingCodes", "[cboBuildingCodes] =" _
&Forms![frmSystemInformation]!BuildingName)

But it errors and expects a line number or label statement at the end of statement... what am I doing wrong?????

Thanks,

Chris

 

[bradcccs]

=dlookup("[Description]", "tblBuildingCodes", "[BuildingCodeID] =" &Forms![frmSystemInformation]!CboBuildingCodes)

where:
"Description" is the name of the field in table tblBuildingCodes
"tblBuildingCodes" is the name of the table
"BuildingCodeID" is the PK of the tblBuildingCodes
"CboBuildingCodes" is the name of the combo box you are looking up the building code. This requires you to have the PK as the bound column.

If you need further help, repost.

Brad.


Note, you could do this by using extra columns in your combo rowsource, and displaying Code & Description in the combo itself. (or use After_update event of combo box to capture column(1)or(2) to text box)

 

[Rich]

You could follow bradds advice and add the field to the combo, no vba is required, just set an unbound textbox control source to =[MyCombo].[Column] (1), where column 1 is the second column in your combo

 

[cstanley]

Thank you all!!!!!!!!!!!!


"May the Great Bird of the Galaxy roost on your planet"

Chris

 

[cstanley]

A related problem...


I have a combo box called "cbobuildingcodes" and a text box called "Description". The value in the Description text box is dlookup'ed based on the value for the combo box. This works fine. However, I'd like some error-checking code in there so when the combo box is empty, I don't get an error message in the text box.

So, I put the following in the After Update property of the combo box:

Private Sub cbobuildingcodes_AfterUpdate()
If IsNull(cbobuildingcodes) Then
Me![Description] = ""
Else
Me![Description] = DLookup("[Description]", "tblBuildingCodes", "[BuildingCode] =" & [Forms]![frmSystemInformation]![cbobuildingcodes])

End If

But all that it gives me is a blank box - it doesn't even do the dlookup anymore... what am I doing wrong?

Thanks,

Chris

 

[bradcccs]

Chris,

The afterupdate event will only "fire" after updating the combo box. Thus the #error will still display prior to combo selection. (Thus remove the after_update coding)

You need to base you dlookup on a condition that the combo is completed.

In the "Description" textbox where you dlookup was change it to:

= iif(isnull(cboBuildingCodes),"",DLookup("[Description]", "tblBuildingCodes", "[BuildingCode] =" & [Forms]![frmSystemInformation]![cbobuildingcodes]))

ie: if the Combo cboBuildingCodes is null, the text box will display "" (nothing). If the combo box has a value, then the dlookup will activate. There is no need to refresh the form etc as the dlookup updates anyway.

Note: I imagine that all building codes have a description, so there is probably no need to account for the possibility of the dlookup returning a null value. If I am wrong in this assumption, repost, and we shall lengthen the statement yet again.

Cheers

Brad.

 

[cstanley]

Thanks - worked liks a charm.

Chris