Check to see if its the last record. (1 Viewer)

[Saint34]

I'm a beginner and I am trying to do the following:

I have a textbox labeled SearchField and a button labeled search
On the on click event of the search the following code runs:

Private Sub Search_Click()
Me.Form.RecordsetClone.FindNext "PN LIKE '*" & Me.SearchField.Value & "*'"
If Not Me.Form.RecordsetClone.NoMatch Then
Me.Form.Bookmark = Me.Form.RecordsetClone.Bookmark
Else
MsgBox "Part number not found"
End If
End Sub



The problem with this code is that once it gets to the last record and you click search again it says "Part number not found"

What i want to do is check to see if it is on the last record, if so then use FindFirst else use FindNext

Any idea's?

Thanks,
-Saint

 

[DCrake]

You have two issues here.

1. You could issue a .Movefirst in the else statement
2. If you did how are you going to trap none existant codes?

 

[Saint34]

I dont understand your post.. I am not looking to Move any records. I am looking to select the next record.

The code I posted works fine other then the fact that If you are on the last record that is presented in the continuous form and you click search it will say that the part number does not exist. This is due to the fact that it is looking at everything under what you are already selected.

So in order to solve this problem I assumed that the best way would be to say that If you are on the last record then use the .FindFirst function otherwise use .FindNext. This way if you have selected a record below the one you are looking for it will select the 1st one.

My guess is that the code would look a little something like this:

Private Sub Search_Click()
If RecordSet.AbsolutePosition = RecordSet.RecordCount - 1 Then 'you are on the last record
Me.Form.RecordsetClone.FindFirst "PN LIKE '*" & Me.SearchField.Value
& "*'"

If Not Me.Form.RecordsetClone.NoMatch Then

Me.Form.Bookmark = Me.Form.RecordsetClone.Bookmark

Else

MsgBox "Part number not found"

End If
Else 'you are on some other record
Me.Form.RecordsetClone.FindNext "PN LIKE '*" & Me.SearchField.Value
& "*'"

If Not Me.Form.RecordsetClone.NoMatch Then

Me.Form.Bookmark = Me.Form.RecordsetClone.Bookmark

Else

MsgBox "Part number not found"

End If
End If
End Sub

 

[Saint34]

I got it to work perfect after thinking about it a little more....

Code I use:
Private Sub Search_Click()
Me.Form.RecordsetClone.FindNext "PN LIKE '*" & Me.SearchField.Value & "*'"
If Not Me.Form.RecordsetClone.NoMatch Then
Me.Form.Bookmark = Me.Form.RecordsetClone.Bookmark
Else
Me.Form.RecordsetClone.FindFirst "PN LIKE '*" & Me.SearchField.Value & "*'"
If Not Me.Form.RecordsetClone.NoMatch Then
Me.Form.Bookmark = Me.Form.RecordsetClone.Bookmark
Else
MsgBox "Part Number not found"
End If
End If
End Sub

 

[vbaInet]

When you say "Check to see if it's the last record" what do you mean? Relative to the form's recordset based on an ordering or relative to the Max ID of the ID field?

 

[Saint34]

If you have a list of PN's:

156
159
1955
1915

Then you search for 15 and hit the search button it goes to the 1st record (156) then you hit search again and it goes to the 2nd record (159) then you hit it again and it goes to the 4th record (1915) Now you are at the bottom so if you were to hit the search button again it should being you back to the 1st record (156). That part works perfect now with the code I posted above.. Now I have another question... I keep getting errors.

I have a combo box now that i called SearchColumn under that combo box it lists the columns you can search under (PN, MfgPN,Type, .... so on)
and instead of this line:
Me.Form.RecordsetClone.FindNext "PN LIKE '*" & Me.SearchField.Value & "*'"
i am trying to use this
Me.Form.RecordsetClone.FindNext "Me.SearchColumn.Value LIKE '*" & Me.SearchField.Value & "*'"
but its not working. Any idea's on this?

Thanks,
Saint

 

[vbaInet]

That part works perfect now with the code I posted above..

Your code is actually doing things the wrong way. Have a look at this:

Private Sub Search_Click()
    Dim rst As DAO.Recordset
    
    Set rst = Me.RecordsetClone
    
    With rst
        If Not (.EOF And .BOF) Then
            .FindNext "PN LIKE '*" & Me.SearchField.value & "*'"
            If .NoMatch Then
                .FindFirst "PN LIKE '*" & Me.SearchField.value &  "*'"
                If Not .NoMatch Then
                    Me.Recordset.Bookmark = .Bookmark
                Else
                    MsgBox "Part Number not found", vbInformation, "Part  not found"
                End If
            Else
                Me.Recordset.Bookmark = .Bookmark
            End If
        Else
            MsgBox "Part Number not found", vbInformation, "Part not  found"
        End If
    End With
End Sub

i am trying to use this
Me.Form.RecordsetClone.FindNext "Me.SearchColumn.Value LIKE '*" & Me.SearchField.Value & "*'"
but its not working. Any idea's on this?

The bit in red is the problem.