Text box data based limitiations

[jmmunoz2141]

Hello,

I currently have a cmd button on a form with the following code

Private Sub Command138_Click()

If DLookup("[Permission]", "tbl_user", "[User ID] = " & Forms![frm_cafe entry form]!Text32) = 1 Then
    DoCmd.ApplyFilter "", "[User ID] Like ""*"" & [Forms]![frm_cafe entry form]![Text32] & ""*""", ""
    DoCmd.OpenQuery "qry_user add to report", acViewNormal, acEdit
    DoCmd.GoToControl "Text32"
    Me.Text32.Value = "####"
Else
    DoCmd.Beep
    DoCmd.GoToControl "Text32"
    Me.Text32.Value = "####"
End If

End Sub

This is working just fine for me as is but I need to add another layer to this security check. I have a text box (Text32) in the same form that, depending on the time of the day, displays a specific string of text. This text is referring to the meal at that time (breakfast, lunch or dinner)

What I would like is for the command button to first check what the text box is displaying and then continue to check if the user has the necessary permissions in their user field. Example: user clicks on command button; code checks text32 and sees that is displaying "Breakfast"; code then checks userID to see if user has needed permissions; etc.

[pbaldy]

If Me.Text32 = "Breakfast" Then

[jmmunoz2141]

Oops! I made a mistake in the original post; The text box is actually Text16.

Thanks for that. I'm having trouble writing the code for this however. I can get the if statement to work fine when I search by either the textbox or the DLookup but I can't seem to get them working simultaneously. Is there a way to have the if statement look first at the text box and then look to see the users rights?

[pbaldy]

If you combine them it will run both. I'd probably wrap what you have inside another If/Then block that tested the textbox (better names will help you later). That way the DLookup() only runs when the first test is true. Minor performance difference, but they can add up.

[June7]

If Then structures can be nested.

Select Case structure might be useful for your requirement.

What should happen if Text16 is lunch or dinner?

[jmmunoz2141]

If you combine them it will run both. I'd probably wrap what you have inside another If/Then block that tested the textbox (better names will help you later). That way the DLookup() only runs when the first test is true. Minor performance difference, but they can add up.

So I've attempted this and this is what I wrote...

Private Sub Command138_Click()

'This runs an append query that adds the user to the report meals table when clicked.

If Me.Text16 = "Breakfast" Then
    DLookup("[Permission]", "tbl_user", "[User ID] = " & Forms![frm_cafe entry form]!Text32) = 1
    DoCmd.ApplyFilter "", "[User ID] Like ""*"" & [Forms]![frm_cafe entry form]![Text32] & ""*""", ""
    DoCmd.OpenQuery "qry_user add to report", acViewNormal, acEdit
    DoCmd.GoToControl "Text32"
    Me.Text32.Value = "####"
Else
    DoCmd.Beep
    DoCmd.GoToControl "Text32"
    Me.Text32.Value = "####"
End If

End Sub

I'm positive that I've done this wrong because now whenever I use the cmd_button it jumps straight to the else statement. I've confirmed that the parameters are correct when testing. Should I be making two separate if statements for this? Also, you are absolutely right about the better naming structure ; that is definitely on my to do list.

If Then structures can be nested.

Select Case structure might be useful for your requirement.

What should happen if Text16 is lunch or dinner?

Hmm, not familiar with Select Case Structure. VBA coding is as new to me as changing an alternator so I will need to dig into that one a bit. As for the lunch or dinner change, I would need the button to do a separate check for each meal and proceed to run the same commands as my original code. I'd imagine that this would need to made into three separate if statements? One to check for breakfast, one for lunch and one for dinner I'm guessing...

[jmmunoz2141]

Welp, I are dumb. I re-wrote my code (slightly) and now it does work

 Private Sub Command138_Click()

'This runs an append query that adds the user to the report meals table when clicked.

If Me.Text16 = "Breakfast" And DLookup("[Permission]", "tbl_user", "[User ID] = " & Forms![frm_cafe entry form]!Text32) = 1 Then
    DoCmd.ApplyFilter "", "[User ID] Like ""*"" & [Forms]![frm_cafe entry form]![Text32] & ""*""", ""
    DoCmd.OpenQuery "qry_user add to report", acViewNormal, acEdit
    DoCmd.GoToControl "Text32"
    Me.Text32.Value = "####"
Else
    DoCmd.Beep
    DoCmd.GoToControl "Text32"
    Me.Text32.Value = "####"
End If

End Sub

I simply added an And between the two statements and it seems fine.

Now the only thing I need to crack is getting it to acknowledge when it's lunch and dinner and perform the same actions. I'm not sure if this would require separate if statements or if I can get by using the same statement and putting it under an Elseif structure. I'm going to test and report back with what I find.

[June7]

Not sure, how does the meal impact what happens?

Does it really matter what the meal is if the user is not permission 1?

Could meal be included in the DLookup WHERE CONDITION argument?

Really need a better understanding of data and what you are trying to accomplish. Why are you using an APPEND query to add record to a table?

[jmmunoz2141]

Let me try my best to help you understand my needs with this. The reason I need this button to check for the current meal is because I need to restrict users to certain meals. User 1 only has rights for Breakfast, User 2 has rights for Breakfast and Dinner etc etc. Every user in the user table will have a permission setting enabled so the current meal is always relevant.

Not really sure about the DLookup question :/


As for the append query being used, I needed to find a way to save the a users information to a table every time they clicked on the cmd button with their user number entered. I made a post about this a while ago where there is sample of my database: https://www.accessforums.net/access/...ble-41640.html
This sample is rather old though so it's of little use atm.

[June7]

Consider:

'1 = breakfast
'2 = lunch
'3 = dinner
'4 = breakfast and lunch
'5 = breakfast and dinner
'6 = lunch and dinner
'7 = breakfast and lunch and dinner
Dim intUP As Integer
Dim booAdd As Boolean
intUP = DLookup("[Permission]", "tbl_user", "[User ID] = " & Forms![frm_cafe entry form]!Text32)
Select Case Me.Text16
    Case "Breakfast"
        If intUP = 1 Or intUP = 4 Or intUP = 5 Or intUP = 7 Then booAdd = True
    Case "Lunch"
        If intUP = 2 Or intUP = 4 Or intUP = 6 Or intUP = 7 Then booAdd = True
    Case "Dinner"
        If intUP = 3 Or intUP = 5 Or intUP = 6 Or intUP = 7 Then booAdd = True
End Select
If booAdd = True Then
    'do this
Else
    'do this
End If

[jmmunoz2141]

Excellent! This totally fixes my next obstacle, using all the the different permission levels I have.

The only issue I can find is that whenever I enter a user ID outside of my tables range, I get an error message "Run-time error '94': Invalid use of Null"

Here is the code I am currently running...(I just added my original commands)

'1 = breakfast
'2 = lunch
'3 = dinner
'4 = breakfast and lunch
'5 = breakfast and dinner
'6 = lunch and dinner
'7 = breakfast and lunch and dinner
Dim intUP As Integer
Dim booAdd As Boolean
intUP = DLookup("[Permission]", "tbl_user", "[User ID] = " & Forms![frm_cafe entry form]!Text32)
Select Case Me.Text16
    Case "Breakfast"
        If intUP = 1 Or intUP = 4 Or intUP = 5 Or intUP = 7 Then booAdd = True
    Case "Lunch"
        If intUP = 2 Or intUP = 4 Or intUP = 6 Or intUP = 7 Then booAdd = True
    Case "Dinner"
        If intUP = 3 Or intUP = 5 Or intUP = 6 Or intUP = 7 Then booAdd = True
End Select
If booAdd = True Then
    'do this
    DoCmd.ApplyFilter "", "[User ID] Like ""*"" & [Forms]![frm_cafe entry form]![Text32] & ""*""", ""
    DoCmd.OpenQuery "qry_user add to report", acViewNormal, acEdit
    DoCmd.GoToControl "Text32"
    Me.Text32.Value = "1234"
Else
    'do this
    DoCmd.Beep
    DoCmd.GoToControl "Text32"
    Me.Text32.Value = "1234"
End If

[pbaldy]

An integer can't handle Null.

intUP = Nz(DLookup("[Permission]", "tbl_user", "[User ID] = " & Forms![frm_cafe entry form]!Text32), 0)

[June7]

Thanks Paul, that usually trips me during debugging.

Don't need the comment lines about the meal codes. I just put them in there to help me with my coding.

[pbaldy]

No problem. I've banged my head against that one often enough to be on the lookout for it now.

[jmmunoz2141]

An integer can't handle Null.

intUP = Nz(DLookup("[Permission]", "tbl_user", "[User ID] = " & Forms![frm_cafe entry form]!Text32), 0)

That fixed it. Thanks!

Thanks Paul, that usually trips me during debugging.

Don't need the comment lines about the meal codes. I just put them in there to help me with my coding.

I know but I like them there. Handy to have a cheat sheet in case I have to change them in the future.

I can't thank the two of you enough for this. Everything is working exactly how I need it to and I couldn't be happier