DoCmd.FindRecord

[Bill Stanton]

I have a combo whose RowSource is a query
defining 5 fields, only one of which displays
in the combo box, i.e., FieldWidth = 0;0;0;2";0.
The combo provides a selection of classical
music composers. I have a case where I'd like
to pre-position to a particular composer.
For example,

strTemp = "Mozart"
me.cmboComposers.SetFocus
DoCmd.FindRecord strTemp, acStart

Attempting to use the above code, I get a 2162
runtime error: "A macro set to one of the current
fields properties failed because of an error in a
FindRecord action argument"

Is the problem that I can't use the FindRecord
method on the underlying query of a combo, as
I often do with the underlying query of a form's
RecordSource?

Thanks,
Bill

 

[Allen Browne]

It would be easier to lookup the value for the combo's Bound Column, and
assign that to the combo:

Me.cmbo.Composers = DLookup("ID", "ComposerTable", "ComposerName =
'Mozart'")

 

[Bill Stanton]

Thanks Allen. I had looked at DLookup in the HELP text
but had somehow convinced myself that it was not an
applicable method to use. I'm glad that you steered me
back, as I have a couple of other instances where it would
help provide some desired function.

Thanks again,
Bill
Graeagle, CA USA

 

[Bill Stanton]

Allen,
I've been trying to get the bugs out of the syntax
requirements of the DLookUp function. In the
statement:
Me.cmboComposer = DLookup("[CompName]", "QComposers", "[CompName] = " &
Mozart)

Where Mozart is defined:

Private Const Mozart As String = "Mozart, Wolfgang Amadeus"

Access has a fit when the comma is encountered.
I tried using "Mozart" & chr(44) & "Wolfgang Amadeus",
but that wouldn't even compile.

Is there a syntax for DLookUp that will allow me to
have a string with a comma embedded in its midst?

Thanks,
Bill

 

[Allen Browne]

You need to finish up with the 3rd argument reading:
[CompName] = "Mozart, Wolfgang Amadeus"

Quotes within a string must be doubled up, so that must be:
"[CompName] = ""Mozart, Wolfgang Amadeus"""

Simplest way to achieve that might be:
Private Const Mozart As String = """Mozart, Wolfgang Amadeus"""

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I've been trying to get the bugs out of the syntax
requirements of the DLookUp function. In the
statement:
Me.cmboComposer = DLookup("[CompName]", "QComposers", "[CompName] = " &
Mozart)

Where Mozart is defined:

Private Const Mozart As String = "Mozart, Wolfgang Amadeus"

Access has a fit when the comma is encountered.
I tried using "Mozart" & chr(44) & "Wolfgang Amadeus",
but that wouldn't even compile.

Is there a syntax for DLookUp that will allow me to
have a string with a comma embedded in its midst?

Thanks,
Bill

It would be easier to lookup the value for the combo's Bound Column, and
assign that to the combo:

Me.cmbo.Composers = DLookup("ID", "ComposerTable", "ComposerName =
'Mozart'")

 

[Bill Stanton]

Got it Allen, thanks. I was aware of the double quotes
to denote a single quote within a string, but I'd somehow
convinced myself that the use of the single quote (') would
accomplish the same thing, but it didn't.

Anyway, I got what was needed so I thank you again.

Bill Stanton
Graeagle, CA USA

You need to finish up with the 3rd argument reading:
[CompName] = "Mozart, Wolfgang Amadeus"

Quotes within a string must be doubled up, so that must be:
"[CompName] = ""Mozart, Wolfgang Amadeus"""

Simplest way to achieve that might be:
Private Const Mozart As String = """Mozart, Wolfgang Amadeus"""

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,
I've been trying to get the bugs out of the syntax
requirements of the DLookUp function. In the
statement:
Me.cmboComposer = DLookup("[CompName]", "QComposers", "[CompName] = " &
Mozart)

Where Mozart is defined:

Private Const Mozart As String = "Mozart, Wolfgang Amadeus"

Access has a fit when the comma is encountered.
I tried using "Mozart" & chr(44) & "Wolfgang Amadeus",
but that wouldn't even compile.

Is there a syntax for DLookUp that will allow me to
have a string with a comma embedded in its midst?

Thanks,
Bill

It would be easier to lookup the value for the combo's Bound Column, and
assign that to the combo:

Me.cmbo.Composers = DLookup("ID", "ComposerTable", "ComposerName =
'Mozart'")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a combo whose RowSource is a query
defining 5 fields, only one of which displays
in the combo box, i.e., FieldWidth = 0;0;0;2";0.
The combo provides a selection of classical
music composers. I have a case where I'd like
to pre-position to a particular composer.
For example,

strTemp = "Mozart"
me.cmboComposers.SetFocus
DoCmd.FindRecord strTemp, acStart

Attempting to use the above code, I get a 2162
runtime error: "A macro set to one of the current
fields properties failed because of an error in a
FindRecord action argument"

Is the problem that I can't use the FindRecord
method on the underlying query of a combo, as
I often do with the underlying query of a form's
RecordSource?