Search code deactivates navigation buttons when search results are longer than a page

[Workingonit]

I have a form that has a subform on it. The main form shows a category of furniture and has custom navigation buttons and a search text box for asset numbers and command button that runs the search. The subform shows the asset numbers associated with that furniture category, sometimes there is only one asset number, in other cases there could be 60. There is a scroll bar to scroll through the asset numbers when there are too many to see in the initial window.

The buttons all work as they should except when I search for an asset number that is part of a category that has too many asset numbers to show in the main window. When this happens the "previous" and "next" navigation buttons do not take you to the previous or next record. All of the other buttons on the form work though - you can go to the first, or the last record, and you can search for a new asset.

This is the code for the search:

 Private Sub cmdAssetSearch_Click()
 Dim rs As Object
 If IsNull(Me.TextAsset) Or Me.TextAsset = "" Then
   MsgBox "Please type in an asset number to search for.", vbOKOnly
   Me.TextAsset.SetFocus
Else
If DCount("[Asset Number]", "[Qry_FurnitureCodeAsset]", "[Asset Number]=" & Me.TextAsset) = 0 Then
    MsgBox "Please check the asset number entered and search again.", vbOKOnly
Else
    Set rs = Me.Recordset.Clone 'this is the main form
    rs.FindFirst "[Asset Number] = " & Me.TextAsset.Value
    Me.Bookmark = rs.Bookmark
     rs.Close
    
    Set rs = Nothing
End If
End If
 Me.TextAsset = ""
Forms!Furniture_CategoryCodeAsset![Item Code].SetFocus
 End Sub

This is the code for the previous and next buttons:

 Private Sub cmdPrevious_Click()
    
    On Error GoTo OnNextRecord
    DoCmd.RunCommand acCmdRecordsGoToPrevious
    GoTo wayout
OnNextRecord:
    MsgBox "You're already on the first record."
wayout:
    On Error GoTo 0
 End Sub
 Private Sub cmdNext_Click()
    
    On Error GoTo OnNextRecord
    DoCmd.RunCommand acCmdRecordsGoToNext
    GoTo wayout
OnNextRecord:
    MsgBox "You're already on the last record."
wayout:
    On Error GoTo 0
 End Sub

I've also attached a picture of what I mean when I say there are more asset numbers than what the window shows.

Any insight at all as to what might be happening would be supremely appreciated! Thank you!

 

[spikepl]

"Search code deactivates navigation buttons" so the buttons are disabled (i.e. not enabled)? Or is that your own interpretation?

"the "previous" and "next" navigation buttons do not take you to the previous or next record"

What is NOT happening is not interesting. The interesting thing is what IS happening. WHat happens when you press the button? Does the code execute (did you check that?)? Do you get an error? Access Explodes? what?

 

[Minty]

I'll back up that question - what are the record selectors doing when there AREN'T too many records... How do you know they are working.

From you code I can't see how they would move you around the subforms records?

 

[Workingonit]

I'm sorry I'm not being clear, I have been trying to figure out this problem for a while and I am having trouble putting it into words.

I do not want the navigation buttons to take me through the subform, they control the main form only.

I know the previous and next buttons work because I can freshly open the form and scroll through the records on the main form without incident. Or I can search for an asset number that is part of a category that only has a few assets attached to it, the previous and next button take me to the previous or next record on the main form as expected.

It's only when I search for an asset number that is part of a category that has many assets attached to it, and therefore I have to scroll through the subform, that the previous and next buttons do not take me to the previous or next records in the main form. (Example in the picture I attached.)

I say they are "deactivated" because nothing happens when they are pressed in that situation. The main form does not move, the subform does not move, there are no error messages, there is no crash menu - they just don't move you to the previous or next record. The first and last record selectors do work though.

I hope this makes my situation more clear.

 

[Minty]

Okay I think I understand the problem a little better now... I've no idea what might be causing it though. Are there any on current events on either the main or sub form?

 

[Workingonit]

There are no on current events for either the main or sub form

The only thing that is different between the previous/next and first/last navigation buttons is the "on error" and message box about being on the first or last record already. I deleted that portion of the code and tried a search, and the buttons still did nothing as I described above - no change.

 

[vbaInet]

Tidied code, give it a try:

Private Sub cmdAssetSearch_Click()
    Dim rs As DAO.Recordset
    
    With Me.TextAsset
        If Nz(.Value, vbNullString) = vbNullString Then
           MsgBox "Please type in an asset number to search for.", vbOKOnly
           .SetFocus
        Else
            If DCount("[Asset Number]", "[Qry_FurnitureCodeAsset]", "[Asset Number]=" & .Value) = 0 Then
                MsgBox "Please check the asset number entered and search again.", vbOKOnly
            Else
                Set rs = Me.RecordsetClone 'this is the main form
                
                With rs
                    .FindFirst "[Asset Number] = " & .Value
                    If Not .NoMatch Then
                        Me.Bookmark = .Bookmark
                    End If
                End With
                
                Set rs = Nothing
            End If
        End If
    End With
    
    ' // You need to think about the below when the search returns nothing
    Me.TextAsset = Null
    Forms!Furniture_CategoryCodeAsset![Item Code].SetFocus
    ' //
End Sub

Private Sub cmdPrevious_Click()
On Error GoTo OnNextRecord

   DoCmd.RunCommand acCmdRecordsGoToPrevious

wayout:
    Exit Sub

OnNextRecord:
   MsgBox "You're already on the first record."
   Resume wayout
End Sub

Private Sub cmdNext_Click()
On Error GoTo OnNextRecord

    DoCmd.RunCommand acCmdRecordsGoToNext

wayout:
    Exit Sub

OnNextRecord:
   MsgBox "You're already on the last record."
   Resume wayout
End Sub