FindNext for multiple criteria not working

[madg]

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

[jforbes]

I would rethink this approach. Even after you get this to work, you will probably find some other place that you will need to apply a Band-Aid fix, then you will find another, and another.

Storing the Access Level into a TempVar or a Global Variable is a good idea. I would change the Form to use a Filter so that only the records at the user's access level are displayed and then let MS-Access manage the Navigation between records.

Applying this filter is pretty easy and there are a few different methods, but one of the most straight forward is to include the filter in the DoCmd.OpenForm Statement. You could use some code similar to the following for the button that opens up your Form:

DoCmd.OpenForm "SomeForm", , , "[Sensitivity]=" & TempVars("AccessLevel").Value

Again, this is the easy way. You could base the Form's RecordSource on a Query that uses the AccessLevel to limit the records.

If you don't want to mess with that (although, I highly recommend you do), you might get your code working if you flip your sign in your where clause:

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

[madg]

I already had a filter so that it would open to the selected project record which worked. I am able to get both filters to work independently however, not at the same time.

Private Sub ProjectID_Click()
 
Dim stCriteria As String
Dim stCriteria1 As String
Dim stCriteria2 As String
 
stCriteria1 = "[ProjectID]=" & [ProjectID]
stCriteria2 = "[Sensitivity] >= " & [TempVars]![AccessLevel]
stCriteria = stCriteria1 & " And " & stCriteria2
 
DoCmd.OpenForm "Project Update", , , stCriteria
 
 
End Sub
 

[jforbes]

I'm a little unsure what you are attempting to do with ProjectID_Click().

There are a few typical scenarios when opening a Form:

1. Opening the Form to add a New Record. No existing Records are available in the Form other than the records that are being added while the Form is open.
2. Opening a Form to a specific Record so it can be Edited. Only the Record to be edited is available in the Form and there is no navigation other than closing the Form. stCriteria1 = "[ProjectID]=" & [ProjectID] is typical criteria for this type of operation.
3. When Browsing Records, all the Records are available and the can be navigated at will. Sometimes the NewRecord is available to add Records, but that mostly depends on how tightly the New Record creation is controlled. stCriteria2 = "[Sensitivity] >= " & [TempVars]![AccessLevel] is typical criteria for this type of operation.

So, in your code, when these two pieces of Critera are ANDed together, the user will get scenario 2, Editing a single record, and that is only if they have the AccessLevel to view to the Record.

You may need two separate buttons, one for Editing and one for Browsing. Or maybe define what you are attempting to do a little more and we can get it hashed out.

[madg]

Ideally I would like the user to open an "Update Project" pop-up form after double clicking from a "Project List" datasheet form. I would then also like them to browse through to update an other project that they have access to, in the case they want to make multiple updates.

[madg]

I was able to solve the problem by using a combobox that updates the form instead. That way I just put a where condition on the query for the access level and it works perfectly now!