Solved Recordset Use (1 Viewer)

Sheinen

New member
Local time
Today, 04:08
Joined
Mar 16, 2021
Messages
3
So I am trying to make a button in my database open the report "Paid Subscriptions for the Month" but it first has to check a table that contains each what permissions each clearance level has. It is supposed to check the table, see if it has access and then open the report of he/she has access. It keeps on telling me that there is a data type mismatch in the criteria expression of rs.FindFirst. TempVars("Clvl") is a number but all items are that in the field "ClearanceLevel".
This is my code:
Code:
Private Sub btnPaidSub_Click()
   Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("Clearance Permissions", dbOpenSnapshot, dbReadOnly)
    rs.FindFirst "ClearanceLevel = '" & TempVars("Clvl") & "'"
    
    If rs!ObjectName = "Paid Subscriptions for the Month" And rs!HasAccess = True Then
      DoCmd.OpenReport "Paid Subscriptions for the Month", acViewReport
    Else
      MsgBox "You do not have clearance!!!"
    End If
End Sub

I've tested the following code to see if it works without checking but it only executes the else and not the then:
Code:
Private Sub btnPaidSub_Click()
   Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("Clearance Permissions", dbOpenSnapshot, dbReadOnly)
  
    If rs!ObjectName = "Paid Subscriptions for the Month" And rs!HasAccess = True Then
      DoCmd.OpenReport "Paid Subscriptions for the Month", acViewReport
    Else
      MsgBox "You do not have clearance!!!"
    End If
End Sub
Can anyone help me figure out what's wrong and if this code will actually work? If it won't work, any suggestions?
I've attached the back end so you can see the tables used. The data inside is messy and a bit inefficient but please do not mind that for it still needs to be worked on after.
 

Attachments

  • Access_be.accdb
    736 KB · Views: 267
Solution
Code:
Private Sub btnPaidSub_Click()
    If DCount("1", "Clearance Permissions", _
                    "ClearanceLevel = " & [TempVars]![Clvl].Value & _
                    " And [ObjectName] ='Paid Subscriptions for the Month' And [HasAccess]=-1") Then
      DoCmd.OpenReport "Paid Subscriptions for the Month", acViewReport
    Else
      MsgBox "You do not have clearance!!!"
    End If
End Sub

theDBguy

I’m here to help
Staff member
Local time
Today, 01:08
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

Can't download your file right now. What is the data type of ClearanceLevel?

Sent from phone...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:08
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub btnPaidSub_Click()
    If DCount("1", "Clearance Permissions", _
                    "ClearanceLevel = " & [TempVars]![Clvl].Value & _
                    " And [ObjectName] ='Paid Subscriptions for the Month' And [HasAccess]=-1") Then
      DoCmd.OpenReport "Paid Subscriptions for the Month", acViewReport
    Else
      MsgBox "You do not have clearance!!!"
    End If
End Sub
 
Solution

Sheinen

New member
Local time
Today, 04:08
Joined
Mar 16, 2021
Messages
3
Thanks for the welcome. Clearance level is an integer. Goes from 0 to 2.
I'll try out the dcount solution.
 

Sheinen

New member
Local time
Today, 04:08
Joined
Mar 16, 2021
Messages
3
I do not understand how dcount works (I'll look into it) but I am thankful that your solution was correct, arnelgp. Thank you for the help.
 

Users who are viewing this thread

Top Bottom