DLookup syntax problem 1

[pompeyjon]

Hi All,

I am trying to get the following DLookup function to work, but it's not happy:

I have a form (MAIN), based on the table (COMMISSIONING). The form contains a simple lookup to a field (GPCODE) in a second table (GENERALPRACTITIONERS). I would like my DLookup control to return a text field (PCTDESC) from the (GENERALPRACTITIONERS) table based on the current selection in the (MAIN) form for the (GPCODE) field.

The syntax I am using is as follows;

=DLookUp("[PCTDesc]","GeneralPractitioners","[GPCode] =" & [Me]![GPCode])

but this is returning only #Name? in the control.

Can anyone help please? Thanks in advance.

Jon

 

[KenReay]

Hi

You say "The form contains a simple lookup ", by this do you mean GPCODE is a combo box?, if yes, by just increasing the number of columns in the combo box to include PCTDesc you can dispense with the DLookup, and instead use =GPCODE.Column(1)

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pompeyjon]

Ken,

Not sure what you mean. The GPCODE field is a combo box with a Row Source of:

SELECT DISTINCTROW GeneralPractitioners.GPCode, GeneralPractitioners.GPName
FROM GeneralPractitioners
ORDER BY GeneralPractitioners.GPName;

Do you mean I need add PCTDesc into the query above and add a text box with a Control Source of:

=GPCODE.Column3

I've tried that and it still gives me the #Name? error.

Jon

 

[KenReay]

Hi

.Column() is a zero based array so

=GPCODE.Column(3)

In addition to including the extra field in the query (rowsource) you need to adjust the value of the .column() property of the combo box.

See my FAQ under Combo Boxes, in the Forms Forum, it explains how to do this (I hope)



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pompeyjon]

OK. I solved my own problem. The syntax I actually needed was as follows:

=DLookUp("PCTDesc","GeneralPractitioners","GPCode = '" & Forms!Main![GPCode] & "'")

As per usual, it was a case of not specifying that GPCode is actually a text field and so belongs in quotes. D'Oh! No star for me...

Thanks to Ken though, for showing me something new with Combo boxes.

Jon