DLookUp

[David Patterson]

I am having difficulty in setting up a DLookUp function for a control on a
form. I am using Access 2002.

I have a form (frmQuickEnterResourcesUsed), with subform, for users to enter
resource data. The principal form has two fields (unbound), RefNo and
WorkName and the subform allows users to input resource information about
the piece of work. The subform and main form are linked by RefNo (the
Primary Key for the whole database).

I want users to select the work item description from a combo box [WorkName]
and then from that automatically populate the RefNo which will then enable
the appropriate data in the subform to be viewed and updated/amended.

I have put a DLookUp statement as the Control for the RefNo field as
follows: =DLookUp("[RefNo]","[tblPrimary]","[WorkName] = & [Forms]!
[frmQuickEnterResourcesUsed]! [WorkName]")

I keep on getting either #Error or #Name in the RefNo field. I've used a
similar statement on other forms before OK. Any idea on what I might be
doing wrong?

Thanks,

David

 

[Graham Mandeno]

Hi David

If WorkName is a text field in your table, then you must enclose the value
you are looking up in quotes:

=DLookup(..., "[WorkName]='" & [WorkName] & "'" )

HOWEVER, THERE IS A BETTER WAY!

I am assuming that while RefNo is the primary key in your table, WorkName is
also unique. Correct?

Further, I assume that the RowSource of your combo box is something like
this:
Select [WorkName] from tblPrimary order by [WorkName];

The nice thing about a combo box is that it can have multiple columns, and
the "bound" column (the one containing its *value*) does not need to be the
same as the visible column(s). So, you can use the combo to lookup the
RefNo corresponding to a given WorkName, and you don't need to use DLookup
at all.

I suggest you change your combo box properties as follows:

Columns: change from 1 to 2

ColumnWidths: set to 0 (this hides the first column and divides the
available remaining width between the other visible column(s))

RowSource: change to:
Select [RefNo], [WorkName] from tblPrimary order by [WorkName];

Name: change to cboRefNo

Now, change the LinkMasterFields for your subform from RefNo to cboRefNo.

If you still wish the RefNo to be visible on the form, then rename the
textbox to txtRefNo and set its ControlSource to:
=[cboRefNo]
(note that this textbox is no longer required, and is purely for display
purposes)

Now everything should work as required, and more efficiently than before