Full Version: Enable / Disbale Command Buttons
UtterAccess Forums > Microsoft® Access > Access Forms
mike60smart
Hi Everyone

I have a Main Form with an Unbound textbox whose Control Source is :- =fosUsername()

I am trying to Enable / specific Command Buttons depending on Type ie Manager or Auditor
I have a table named tblUserSecurity with the following fields:-

Click to view attachment

When I uncomment the Code and Open the Form I get this error:-

Click to view attachment

The unbound textbox which displays the current User then displays as #Error

How can I modify the following On Open Code??

Any Help appreciated.

Code :-
'Private Sub Form_Load()
'Dim rstuser As Recordset
'Set rstuser = CurrentDb.OpenRecordset("tblUserSecurity", dbOpenDynaset, dbSeeChanges)
'rstuser.FindFirst "[userID]=" & Chr$(34) & txtUserName & Chr$(34)
'
'If rstuser.NoMatch Then
'
'Select Case Manager
'
'cmdUpload.Enabled = True
'cmdRetro.Enabled = True
'cmdConcurrent.Enabled = True
'cmdQA.Enabled = True
'cmdROIStats.Enabled = True
'
'Else
'cmdUpload.Enabled = False
'cmdRetro.Enabled = False
'cmdConcurrent.Enabled = False
'cmdQA.Enabled = False
'cmdROIStats.Enabled = False
'End If
'
'If rstuser.NoMatch Then
'
'Select Case Auditor
'
'cmdRetro.Enabled = True
'cmdConcurrent.Enabled = True
'cmdROIStats.Enabled = True
'cmdROIID.Enabled = True
'Manager_Reports.Enabled = True
'Else
'cmdRetro.Enabled = False
'cmdConcurrent.Enabled = False
'cmdROIStats.Enabled = False
'cmdROIID.Enabled = False
'Manager_Reports.Enabled = False
'End If
'End Select
'
'End Sub
Bob G
mike,

if there is no match in your findfirst then the user isnt in the original table. wouldnt that be a different concern?

you would want something like this i think.

CODE
....
if rstuser.nomatch then
disable all buttons
else
select case rstuser.usertype
case is manager
enable buttons for manager
case is auditor
enable buttons for auditor
end select
mike60smart
Hi Bob

Your suggestion is exactly what I need

I have changed the code as shown below but have no idea of the syntax for "Case is Manager or Case is Auditor"??

Code:-
Private Sub Form_Load()
Dim rstuser As Recordset
Set rstuser = CurrentDb.OpenRecordset("tblUserSecurity", dbOpenDynaset, dbSeeChanges)
rstuser.FindFirst "[userID]=" & Chr$(34) & txtUserName & Chr$(34)

If rstuser.NoMatch Then

cmdUpload.Enabled = False
cmdRetro.Enabled = False
cmdConcurrent.Enabled = False
cmdQA.Enabled = False
cmdROIID.Enabled = False
Manager_Reports.Enabled = False
cmdROIStats.Enabled = False

Else

case is manager

cmdUpload.Enabled = True
cmdRetro.Enabled = True
cmdConcurrent.Enabled = True
cmdQA.Enabled = True
cmdROIStats.Enabled = True

case is auditor

cmdRetro.Enabled = True
cmdConcurrent.Enabled = True
cmdROIStats.Enabled = True
cmdROIID.Enabled = True
Manager_Reports.Enabled = True

End If
End Select

End Sub
Bob G
my example earlier has the proper syntax for the select case, i just didnt put in all the button enable commands.
BruceM
Note in Bob's example the presence of Select Case (which doesn't appear in the code you posted), and also that the End Select precedes the End If. I expect the error is because you have done the End If before concluding the Select Case.

When you identify the appropriate record in the recordset, you need to get the value of the Manager field. Maybe like this after the FindFirst:

Then in the code, after the Else:

CODE
Select Case rstUser!UserType
  Case Is "Manager"
    Me.cmdUpload.Enabled = True
  ' etc.
  Case Is "Auditor"
    Me.cmdROIID.Enabled = True
  ' etc.
End Select


Note the quotes around Manager and Auditor. One way or the other you need to identify the contents of the UserType field. You could also use DLookup if the table is relatively small. With a recordset, be sure to close it when you are done.

As an observation, there are several buttons that are enabled for both Manager and Auditor. You could enable those buttons after the Else, then do the Select Case for the remaining controls. It would have no effect overall, but is a little tidier.
mike60smart
Hi Bob & Bruce

Ok I changed the code now to that shown below but the unbound textbox to obtain the Username is still showing #Error

Code is:-
Private Sub Form_Load()
Dim rstuser As Recordset
Set rstuser = CurrentDb.OpenRecordset("tblUserSecurity", dbOpenDynaset, dbSeeChanges)
rstuser.FindFirst "[userID]=" & Chr$(34) & txtUserName & Chr$(34)

If rstuser.NoMatch Then
cmdUpload.Enabled = False
cmdRetro.Enabled = False
cmdConcurrent.Enabled = False
cmdQA.Enabled = False
cmdROIID.Enabled = False
Manager_Reports.Enabled = False
cmdROIStats.Enabled = False

Else

Select Case rstuser!UserType
Case Is "Manager"
cmdUpload.Enabled = True
cmdRetro.Enabled = True
cmdConcurrent.Enabled = True
cmdQA.Enabled = True
cmdROIStats.Enabled = True

Case Is "Auditor"

cmdRetro.Enabled = True
cmdConcurrent.Enabled = True
cmdROIStats.Enabled = True
cmdROIID.Enabled = True
Manager_Reports.Enabled = True
End Select
End If


End Sub
BruceM
QUOTE
the unbound textbox to obtain the Username is still showing #Error

Is that txtUserName in the code? If so, how does it get its value?
mike60smart
Hi Bruce

Many thanks I declared the txtUsername and now all seems to be working OK
Many thanks yet again
cheers.gif
Code is now:-

Private Sub Form_Load()
Dim strUsername As String
Dim rstuser As Recordset

strUsername = Me.txtUserName

Set rstuser = CurrentDb.OpenRecordset("tblUserSecurity", dbOpenDynaset, dbSeeChanges)
rstuser.FindFirst "[userID]=" & Chr$(34) & txtUserName & Chr$(34)

If rstuser.NoMatch Then
cmdUpload.Enabled = False
cmdRetro.Enabled = False
cmdConcurrent.Enabled = False
cmdQA.Enabled = False
cmdROIID.Enabled = False
Manager_Reports.Enabled = False
cmdROIStats.Enabled = False

Else

Select Case rstuser!UserType
Case 1
cmdUpload.Enabled = True
cmdRetro.Enabled = True
cmdConcurrent.Enabled = True
cmdQA.Enabled = True
cmdROIStats.Enabled = True

Case 2

cmdRetro.Enabled = True
cmdConcurrent.Enabled = True
cmdROIStats.Enabled = True
cmdROIID.Enabled = True
Manager_Reports.Enabled = True
End Select
End If


End Sub
BruceM
Glad to hear you got it working, but I'm not sure what you did. You declared a string variable, assigned it the value in a text box, and never used it again. I wonder about how the text box got a value to be used in the form's Load event. I suppose it is either an expression such as DLookup, or else it was assigned in the form's Open event.

I see that your Case statements reference numbers rather than text. Is UserType a lookup field?

In general it is a good idea to use the Me prefix where you can, such as in identifying the command buttons to be hidden/unhidden. Access can usually sort it out, but can do it more efficiently at least if you use the Me prefix, which tells Access to look for a property of the form rather than searching other collections for a function, variable, or other usage.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.