ComboBox That Looks Up Values

[Guest]

I have a cbo that looks up values on my form, It works great until PLU_DESC
has a ' or Number
Is there a fix for this
Here is my code
Private Sub Combo12_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[PLU_DESC] = '" & Me![Combo12] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[J. Goddard]

Hi -

To fix the quotes problem, change the ' to "", so the findfirst looks
like this:

rs.FindFirst "[PLU_DESC] = """ & Me![Combo12] & """"

If I read between the lines of you question correctly, [PLU_DESC] is a
text type, but with some of the [PLU_DESC] values numeric with leading
zeroes (e.g. 0025). To fix that, you need to format your number with as
many leading 0's as you need, like this:

rs.FindFirst "[PLU_DESC] = """ & format(Me![Combo12],"0000") & """"

That will only work if [PLU_DESC] is a constant length.

The format function accepts non-numeric data without complaint, so
format("45","00000") --> "00045" and
format("A2B","00000") --> "A2B"

HTH

John