lookup based on selection in list

  • Thread starter Thread starter Lara
  • Start date Start date
L

Lara

In the Combo Box Wizard, I've chosen to "find a record in
my form based on the value in the combo box." When I'm
done, this is the event procedure the wizard creates for
me, after update:

Private Sub Combo69_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Company] = '" & Me![Combo69] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

HOWEVER, we have company names WITH apostrophes in them.
When we choose one of those companies, the macro dies
("run time error 3077, syntax error(missing operator) in
expression") Can I rewrite this code to work with those
companies as well, or do I just have to make a rule that
they can't use apostrophes in the company name? The user
tells me he's having a similar problem with the "&"
symbol, but I haven't been able to duplicate it, so if
you happen to know why this is, too, I'd sure appreciate
the help! Thanks :~)
 
Another way to code this is as:

rs.FindFirst "[Company] = " & chr$(34) & Me![Combo69] &
chr$(34)

The contents of the combo box have to be enclosed in
either single or double quotes to resolve to a valid
string expression for the FindFirst method use. (The
wizard generated code uses single quotes.) Because
chr$(34) resolves to a double quote, you end up with the
other way to build a valid string expression.

If you do it this way, though, if a company name contains
a double quote, you'll still have troubles.

Relative to your problem with names containing ampersands,
bear in mind that the ampersand is the VBA operator for
concatenating strings. If a company name that contains an
ampersand gets used in an expression, it could resolve to
something that VBA tries to treat as concatenation
operation, instead of being treated as part of a string
literal.
 
Back
Top