Dlookup in ControlSource

[Don K]

I need to change the controlsource of a textbox depending on the value of a
combo box. On the AfterUpdate event of the combobox I have the following
statements:

Select Case cboboxvalue
Case 1
me.recordsource = "query1"
me.txtboxname.controlsource =
"=Dlookup("queryname","fieldname","criteria")"

Case 2
etc

End Select

The criteria is a number and is coded as:
"intID = 1"

I know the issue is most likely with the quotation marks, but I have not
been able to delimit them without either getting an error, or having part of
the string become a comments line because of a single quote.

How should the controlsource line be coded?

Thanks,

 

[John W. Vinson]

I need to change the controlsource of a textbox depending on the value of a
combo box. On the AfterUpdate event of the combobox I have the following
statements:

Select Case cboboxvalue
Case 1
me.recordsource = "query1"
me.txtboxname.controlsource =
"=Dlookup("queryname","fieldname","criteria")"

Case 2
etc

End Select

The criteria is a number and is coded as:
"intID = 1"

I know the issue is most likely with the quotation marks, but I have not
been able to delimit them without either getting an error, or having part of
the string become a comments line because of a single quote.

How should the controlsource line be coded?

To enclose a doublequote inside a string delimited by doublequotes, use a
doubled doublequote (how's THAT for doubletalk). Two ways you can use:

me.txtboxname.controlsource = _
"=Dlookup(""queryname"",""fieldname"",""intID = 1"")"

or,

Dim strCrit As String
strCrit = "intID = 1"
me.txtboxname.controlsource = _
"=Dlookup(""queryname"",""fieldname""," & strCrit & ")"

This gets even worse if the field in the criterion is a Text field since you
must delimit the criterion with quotemarks also - you may need quadruple
quotes.

 

[Don K]

John, thank you very much for your post. In all my years working with
Access I had never seen the use of the doubled double-quotes before, just
various combinations of single and double quotes. Your answer worked as
advertised, and I am very appreciative of your time and thoughts.

Don