rs.FindFirst not working

[Knowlton]

I have a form for updating records in a details table. There is an unbound combo populated with the available records in the table. I have the following code in the AfterUpdate event of the combo.

Private Sub cboTag_AfterUpdate()
    'Move to appropriate record
    Dim rs As DAO.Recordset

    If Not IsNull(Me.cboTag) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        rs.FindFirst "Tag = '" & Me.cboTag & "'"
        If rs.NoMatch Then
            MsgBox "Tag Not found"
        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If

When running the code always goes to the not found error. The variable is correct and the record is there so what asm I missing?
Thanks!

[twinnyfo]

Knowlton,

I used identical syntax and it works fine. It appears you are using a Combo Box as your search item?

Does that Combo Box have an ID and a Value? Your current code is looking for the value of the Combo Box, which should be an index, but is set up as a text string.

What is the row source for your combo box?

[zmbd]

Twinnyfo most likely has the solution there
Open your form, and look at the [row source] property of Me.cboTag under the data tab, also the [bound column] value.

[Knowlton]

The combo has only one column visible which is the bound column and is the Tag which is a text field. There are 3 criteria columns in the query to limit the comb to records which have not been updated.

[Knowlton]

BTW: The version is 2013 if that matters.
Thanks!

[zmbd]

Ok, then your control isn't returning the expected value.

What I tend to do instead of your code

11.         Set rs = Me.RecordsetClone
12.         rs.FindFirst "Tag = '" & Me.cboTag & "'"

I build the string first so that I can debug print it to ensure that the string is evaluating correctly.

   Set rs = Me.RecordsetClone
   zstrCriteria = "Tag = '" & Me.cboTag & "'"

debug.print zstrCriteria
'press <ctrl><g> to see the result

   rs.FindFirst zstrCriteria

[Knowlton]

Here is my updated code:

 Dim rs As DAO.Recordset
    Dim zstrCriteria As String
    
    If Not IsNull(Me.cboTag) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        'define search string
        zstrCriteria = "Tag = '" & Me.cboTag & "'"
        'show results
        Debug.Print zstrCriteria
        
        rs.FindFirst zstrCriteria
        If rs.NoMatch Then
            MsgBox "Tag Not found"
        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If

I selected a tag in the combo (4002424027), stepped through and looked at the criteria string. It was:Tag = '4002424027'. I continued stepping through and it went to the error again.

[twinnyfo]

This is a silly question, but are you sure there is a field named "Tag" in the recordset?

[Knowlton]

I would say that at this point no question is silly. Yes there is a field named "Tag". For what it may be worth, I created this in Access 2000 and it was working. Then we updated to 2013 and I started having problems. I failed to post the rowsource for the combo so here it is.

SELECT tblRetreadDetails.Tag
FROM tblRetreadDetails
WHERE (((tblRetreadDetails.Capped) Is Null) AND ((tblRetreadDetails.CapDate) Is Null) AND ((tblRetreadDetails.Reason)="Retread")) OR (((tblRetreadDetails.Capped) Is Null) AND ((tblRetreadDetails.CapDate) Is Null) AND ((tblRetreadDetails.Reason)="Sell Casing"))
ORDER BY tblRetreadDetails.Tag;

Thanks so much for your help!

[twinnyfo]

What is the RecordSource for the form? There must be something causing this code not to find the appropriate record. Even weirder that it previously worked and now it does not....

[Rabbit]

Is Tag a string or numeric data type?

[Knowlton]

Tag data type is Short Text