CODEWITHSUNDEEP
ms-accessvbams-access-2010
madelina
madelina

Reputation: 55

Find next record based on certain criteria

I am trying to use the .FindNext (and .FindPrevious) function on an update form "next button" to find the record that meets certain criteria. 

Private Sub NextRecord_Click()
Dim foundmatch As Boolean

For x = 0 To 3 Step 1
  With Me.RecordsetClone
    .FindNext "[Sensitivity] = " & [TempVars]![AccessLevel] + x
    If .NoMatch Then
        foundmatch = False
    Else
        Me.Bookmark = .Bookmark
        foundmatch = True
        Exit For
    End If
  End With
Next
If foundmatch = False Then
    MsgBox "No More Records"
End If

End Sub

Upon a user entering the database the users accesslevel is assigned to a temp variable (1 to 4), and each project has a sensitivity rating of 1 to 4. The code below was used and worked for both next and previous only in finding records when the sensitivity and accesslevel were equal but not for sensitivities below the users access level which they are qualified to see. 

Private Sub PrevRecord_Click()
Dim Stringy As String
Stringy = "[Sensitivity] = " & [txtaccess]
With Me.RecordsetClone
.FindPrevious Stringy
If .NoMatch Then
MsgBox "No More Records"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub

Note: The form is pulled from a query with Sensitivity one of the fields, and [txtaccess] is a text box on the field with the default value set at [TempVars]![AccessLevel]. I've also tried changing it to:

Stringy = "[Sensitivity] >= " & [txtaccess]

but that doesn't work either

Upvotes: 1

Views: 1046

Answers (1)

madelina
madelina

Reputation: 55

I was able to fix the problem by setting applying a filter for sensitivity on the actual forms On_Load event rather than the command button. It now works using a next record command button added with the default code/settings!

Upvotes: 1

Related Questions