DLookup function

[Guest]

I have a dlookup function which displays the value from an attached table
based on the value entered in a combo box on a form. The user selects a
dealer code and I want the dealer name to appear below as confirmation (the
dealer name is in the combo box but obviously you can not see this when the
box is closed after you exit the field).

Until the record is saved though this will not work and #error appears in
the field. I can not save the record first as they are all unbound text boxes
which are saved to a table via code. Does anyone know a way round this
please? I want it to display just as a check that the right dealer code was
selected on the initial data entry.
Thanks
Sue

 

[Steve Schapel]

Sue,

It seems to me that your DLookup() function is wrongly conceived. It
sounds like you must have a Dealers table or some such, where the dealer
code and dealer name are listed, rght? So your DLookup should be
looking in this table, not in the table that the form is working with.

Either that, or the syntax of the DLookup is incorrect, but obviously I
can't comment on this without seeing what you've done.

Alternatively, you can refer to the applicable column of the dealer code
combobox in the dealer name text box, using an expression similar to
this in the ControlSource (instead of the DLookup)...
=[dealer code].[Column](1)

 

[Sandra Daigle]

Hi Sue,

You can actually skip the Dlookup and simply use the value directly from the
combo using a calculated control. In the ControlSource of the control to
display Dealer Name put the following to display the contents of the 2nd
field of the currently selected row. Replace 'DealerCode' with the name of
the combo. Note that the Column property is indexed starting with 0 so this
actually shows the second column.

=[DealerCode].[column](1)

 

[Guest]

Thanks guys this worked a treat!

Hi Sue,

You can actually skip the Dlookup and simply use the value directly from the
combo using a calculated control. In the ControlSource of the control to
display Dealer Name put the following to display the contents of the 2nd
field of the currently selected row. Replace 'DealerCode' with the name of
the combo. Note that the Column property is indexed starting with 0 so this
actually shows the second column.

=[DealerCode].[column](1)


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I have a dlookup function which displays the value from an attached
table based on the value entered in a combo box on a form. The user
selects a dealer code and I want the dealer name to appear below as
confirmation (the dealer name is in the combo box but obviously you
can not see this when the box is closed after you exit the field).

Until the record is saved though this will not work and #error
appears in the field. I can not save the record first as they are all
unbound text boxes which are saved to a table via code. Does anyone
know a way round this please? I want it to display just as a check
that the right dealer code was selected on the initial data entry.
Thanks
Sue