Combo box value problem

[SpandexBobcat]

Hi folks,

I am trying to use a combobox as a simple search on a field to bring up a record on the current form. I have done it before but for some reason I am getting a 'The value you entered isn't valid for this field' and I can't figure out why...

I have a form based on a single table, the combobox code is as follows:

Private Sub CmbSearch_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As String

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Job_Ref] = '" & Me![CmbSearch] & "'"
    Me.Bookmark = rs.Bookmark
    
End Sub

The combobox is unbound with a row source of

SELECT [qrySearch].[Job_Ref] FROM qrySearch;

One thing that I have done is assign a custom format to the field 'Job_Ref' as "REW"0000#. Field Job_Ref is an autonumber...

Can you see where I am going wrong? Or could you provide an alternative?

Thanks in advance,

SB

"If at first you don't succeed, destroy all evidence that you tried.

 

[Leeeon]

Think the problem lies with the formatting. Your query is expecting a string as the parameter, but Job_Ref is an autonumber, even with the formatting!

What's happening is Access is still storing Job_Ref as a number, it just formats on the fly to make it look like a string eg Stored as 12345, Displayed as REW12345.

Try removing the quotes from the around the criteria in query.

 

[SpandexBobcat]

Thanks Leeeon, I will look into it.

SB

"If at first you don't succeed, destroy all evidence that you tried.

 

[TheAceMan1]

How are ya SpandexBobcat . . . . .

Are you sure your looking at the right column?

[blue]Me![CmbSearch].Column(1)[/blue]

See Ya! . . . . . .

 

[SpandexBobcat]

Bearing up under the strains of life AceMan.....

There is only one column in the combobox...

"If at first you don't succeed, destroy all evidence that you tried.