DLookup syntax

[Guest]

The following is attached to the Control Source of a textbox on a form
called test:

=DLookUp("[job_id]","job"," [job_desc] = ' " & Forms!test!txtJobDesc & " ' ")

where job_id (autonumber) and job_desc (text) are fields in job table,
txtJobDesc is a combo box containing the job description on the test form.

The combo box is populated from a query that selects job_desc from the job
table.

The textbox remains blank when the form is in Form View.

Would someone let me know what I am doing wrong, please? and / or how to fix
the problem?

Thanks!

 

[Dirk Goldgar]

The following is attached to the Control Source of a textbox on a
form called test:

=DLookUp("[job_id]","job"," [job_desc] = ' " & Forms!test!txtJobDesc
& " ' ")

where job_id (autonumber) and job_desc (text) are fields in job table,
txtJobDesc is a combo box containing the job description on the test
form.

The combo box is populated from a query that selects job_desc from
the job table.

The textbox remains blank when the form is in Form View.

Would someone let me know what I am doing wrong, please? and / or how
to fix the problem?

It looks like youi have extra spaces around your criterion value. Try
this:

=DLookUp("[job_id]", "job",
"[job_desc] = '" & Forms!test!txtJobDesc & "'")

(but put it all on one line).

Actually, since this is on the form test itself, you should be able to
just write

=DLookUp("[job_id]", "job", "[job_desc] = '" & [txtJobDesc] & "'")

By the way, in most people's naming conventions, the "txt" prefix is
used for text boxes, not combo boxes.

 

[John Vinson]

The following is attached to the Control Source of a textbox on a form
called test:

=DLookUp("[job_id]","job"," [job_desc] = ' " & Forms!test!txtJobDesc & " ' ")

where job_id (autonumber) and job_desc (text) are fields in job table,
txtJobDesc is a combo box containing the job description on the test form.

The combo box is populated from a query that selects job_desc from the job
table.

The textbox remains blank when the form is in Form View.

Would someone let me know what I am doing wrong, please? and / or how to fix
the problem?

Thanks!

If Job_Desc is in fact a Text field (and not a lookup field), and if
you've posted the literal syntax of your DLookUp, you need to remove
some blanks. As it is, if the value in txtJobDesc were equal to
Database Developer, it would only match records in the table with a
leading blank and a trailing blank: " Database Developer " rather than
"Database Developer".

Change it to

=DLookUp("[job_id]", "job", "[job_desc] = '" & Forms!test!txtJobDesc &
"'")


John W. Vinson[MVP]

 

[Guest]

The following is attached to the Control Source of a textbox on a form
called test:

=DLookUp("[job_id]","job"," [job_desc] = ' " & Forms!test!txtJobDesc & " ' ")

where job_id (autonumber) and job_desc (text) are fields in job table,
txtJobDesc is a combo box containing the job description on the test form.

The combo box is populated from a query that selects job_desc from the job
table.

The textbox remains blank when the form is in Form View.

Would someone let me know what I am doing wrong, please? and / or how to fix
the problem?

Thanks!

If Job_Desc is in fact a Text field (and not a lookup field), and if
you've posted the literal syntax of your DLookUp, you need to remove
some blanks. As it is, if the value in txtJobDesc were equal to
Database Developer, it would only match records in the table with a
leading blank and a trailing blank: " Database Developer " rather than
"Database Developer".

Change it to

=DLookUp("[job_id]", "job", "[job_desc] = '" & Forms!test!txtJobDesc &
"'")


John W. Vinson[MVP]

Thanks John, Dirk,

I have pasted both your responses into the Control Source of the job_id
textbox, and the field is still blank in Form View in each case.

The job_desc combo box is populated using the query

SELECT job.job_id, job.job_desc FROM job ORDER BY [job_id];

as Row Source, with Table/Query Row Source Type, Bound Column 1 (is the ID?
- ie job_id is first column in the job table, job_desc the second column).
Is this a lookup? If it is, is DLookup still useful?

 

[John Vinson]

Thanks John, Dirk,

I have pasted both your responses into the Control Source of the job_id
textbox, and the field is still blank in Form View in each case.

The job_desc combo box is populated using the query

SELECT job.job_id, job.job_desc FROM job ORDER BY [job_id];

as Row Source, with Table/Query Row Source Type, Bound Column 1 (is the ID?
- ie job_id is first column in the job table, job_desc the second column).
Is this a lookup? If it is, is DLookup still useful?

The DLookUp really isn't necessary if all that you want is to see the
job_desc... even the separate textbox can be left off!

I'd simply set the ColumnWidths property of the combo box to

0;1.5

This will conceal the meaningless (to people) Job_ID and display the
Job_Desc.

If you really want a textbox to show the job_desc, just set the
control source of the textbox to

=comboboxname.Column(1)

This will pick up the description directly from the combo; the DLookUp
would just be an extra step.

John W. Vinson[MVP]

 

[Dirk Goldgar]

Thanks John, Dirk,

I have pasted both your responses into the Control Source of the
job_id textbox, and the field is still blank in Form View in each
case.

The job_desc combo box is populated using the query

SELECT job.job_id, job.job_desc FROM job ORDER BY [job_id];

as Row Source, with Table/Query Row Source Type, Bound Column 1 (is
the ID? - ie job_id is first column in the job table, job_desc the
second column).

Ah, that's the problem with the DLookup, then. The Value of the combo
box is the job_id, not the job_desc. But ...

Is this a lookup?

If you mean what I think you mean, then yes, it is.

If it is, is DLookup still useful?

You don't need it. You have the value of the job_id sitting right there
in the combo box for you. Just set your text box's ControlSource to

=[txtJobDesc]

 

[Guest]

John, Dirk -

Thank you so much for your help! It's working!

<snip>