dlookup dilemma

  • Thread starter Thread starter JohnE
  • Start date Start date
J

JohnE

I have a form that shows what reports are available along
with what the user needs to do. One of the fields is
instructions telling the user what they need to do. I am
trying to fill the field using a click event in a listbox
of the report names. I am trying to use the following
code. Originally was using a query but it was cutting off
the instructions.
The table is usrtblReportModule.
The field is called Instructions.
The table field is a memo data type.
The report name is in the usrtblReportModule as ReportName.

Me.txtInstructions.Value = DLookup
("[Instructions]", "usrtblReportModule", "[Instructions]
=Forms![usrfrmReportModule]![ReportName]")

If anyone can see the error, I say thanks in advance for
the assistance.
*** John
 
I have a form that shows what reports are available along
with what the user needs to do. One of the fields is
instructions telling the user what they need to do. I am
trying to fill the field using a click event in a listbox
of the report names. I am trying to use the following
code. Originally was using a query but it was cutting off
the instructions.
The table is usrtblReportModule.
The field is called Instructions.
The table field is a memo data type.
The report name is in the usrtblReportModule as ReportName.

Me.txtInstructions.Value = DLookup
("[Instructions]", "usrtblReportModule", "[Instructions]
=Forms![usrfrmReportModule]![ReportName]")

If anyone can see the error, I say thanks in advance for
the assistance.
*** John
Click to expand...

1) At the very least, as [instructions] appears to be text you need to
write the where clause as a Text, not Number, datatype.
"[Instructions] = '" & Forms![usrfrmReportModule]![ReportName] & "'")

For clarity only, the double and single quotes are:
= ' " & Forms![usrfrmReportModule]![ReportName] & " ' ")

2) You can use the Me! word in the where clause also.

Me!txtInstructions.Value = DLookup ("[Instructions]",
"usrtblReportModule", "[Instructions] = '" & Me![ReportName] & "'")

3) Because the Value property is the default property, you do not need
to expressly state it.
Me.txtInstructions = DLookup( etc....)

4) You want to DLookUp the [Instructions] field in the table, but as
criteria you write "[Instructions] = etc.
Well if [Instructions] already = something, why do you need to look it
up.
I suspect you mean, as the where clause:
"[SomeOtherField] = '" & Me![ReportName] & "'")
 
Back
Top