Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How do you have a drop down grey out depending who is on the form.

Posted on 2011-03-23
Medium Priority
Last Modified: 2013-11-27
I have a database that has a switchboard with 16 forms on it. There is a log in  process before the voters can get into the forms.
Now that the records have been entered, into the forms. I need a way for them to vote on the records that have been typed in the forms.
 I have 4 drop downs for the voters on each form. They can either vote: Authenticated, limited use, no test , pending.

The problem is I need a way that when they get on the forms to vote they can only vote in their section. So I would need the rest of the voters voting options to be grayed out except for the voter that is voting.

I’m thinking that I would need an if statement which would start at the log in process. Once they log in the drop down for the voters would turn gray except for the voter who logged in. So depending on who they longed in as depends what is grayed in on the voting selection of the form.

What code will I need to use , and where does the code go in my log in process. How does the voting drop down  only available to that one voter and the rest is grayed out? Here is the log in code.
I have an if statement that determines log in process.

Private Sub Command5_GotFocus()
If Text1 = "aec" And Text3 = "aec" Then
MsgBox "Welcome,Access Granted!", vbInformation, "Login_Form"
MsgBox "Welcome to FBCB2 JCR LUT Database", vbInformation, "FBCB2"
DoCmd.OpenForm "frm_FBCB2"

ElseIf Text1 = "otc" And Text3 = "otc" Then
MsgBox "Welcome,Admin!", vbInformation, "FBCB2"
MsgBox "Welcome to FBCB2 JCR LUT Form Statistics", vbInformation, "FBCB2"
'DoCmd.CloseForm "frm_Login"
DoCmd.OpenForm "frm_FBCB2 JCR LUT _Status"
Question by:gigifarrow
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2

Author Comment

ID: 35200192
Hello , can anybody answer my question?
LVL 12

Expert Comment

ID: 35200654
So there is a form for Authenticated, a form for limited use, one for no test and one for pending...and each of these have 4 drop downs?


Author Comment

ID: 35200717
yes,I need them to be able to vote with out being able to touch the other voters dropdowns.
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.


Author Comment

ID: 35200749
I meant no, you have a form with drop downs of authenticated, pending etc. All this on one form. They are voting on the data on the form. The voting dropdowns are on every form they are voting on.
LVL 12

Accepted Solution

rgn2121 earned 2000 total points
ID: 35200842
Well, either you create a little structure to hold the user name and access level throughout the app or you you set something somewhere in a table so you know who is logged in...

For me, I create a my own type and then use that when I need it.
In a module I would do the following

Private Type myAppUser
accessLevel As AccessLevels
myUserID As Integer
End Type

Public Enum AccessLevels
End Enum
Public curMyAppUser As myAppUser

Now...inside the login form...once you know who has logged in and what there access level is, you need to set the properties for curMyAppUser

curMyAppUser.myUserID = the users id
curMyAppUser.accessLevel = the users access level

Then curMyAppUser has all the info you need and it can be used throughout the application.  On all of the form loads, just use a select case to enable/disable or set the controls to visible or not.

select case curMyAppUSer.accessLevel

case is = Admin

case is = Limited


Expert Comment

ID: 35203371
This takes 3 steps.
You need to look up the department for the user when they log in.
Store that department on the switchboard so you can access it when you need it.
When the voting form is loaded disable the controls based on the department stored on the switchboard.

This assumes your switchboard is always open. If not then you might want to consider having a full time hidden "admin" form to store this kind of data on. Let me know if you need help setting this up.

On the switchboard form create a hidden textbox to hold the department of the user
ie .txtUserDept

Add the code below to the log on form. It needs to go right after the user's log on is successful and the switchboard is opened.  

   Dim strCriteria As String

   strCriteria = "Login = '" & Me.tbUser & "'"
   Forms!frmSwitchboard.Controls!txtUserDept = DLookup("Department", "tblEmployee", strCriteria)

me.tbUser is the textbox on your current log on form where the user enters there name or ID
tblEmployee is the table with your employees names and department is stored.
txtUserDept is the hidden textbox you created on the switchboard

The department name is now loaded into the hidden texbox. This code can vary a lot depending on how the log on is validated, user ID or user first name or full name etc.

Now on the load event of the voting form, you need a case statement looking at the department value to disable the combo boxes for other departments. Something like this.

Private Sub Form_Load()
   Dim strDepartment As String
   strDepartment = Forms!frmSwitchboard.Controls!txtUserDept

   Select Case strDepartment
   Case "Development"
      Me.cbxDept01.Enabled = False
   Case "Admin"
      Me.cbxDept02.Enabled = False
   Case "HR"
      Me.cbxDept03.Enabled = False
   Case "Production"
      Me.cbxDept04.Enabled = False
   Case Else
      MsgBox ("I'm sorry, I didn't understand that.")
   End Select

End Sub

Again this example is pretty generic. It would be helpful to know more about how the log on form works to really dial in the solution.

Author Comment

ID: 35208662

This is the code for the log in process:

Private Sub Command5_GotFocus()
If Text1 = "aec" And Text3 = "aec" Then
MsgBox "Welcome,Access Granted!", vbInformation, "Login_Form"
MsgBox "Welcome to FBCB2 JCR LUT Database", vbInformation, "FBCB2"
DoCmd.OpenForm "frm_FBCB2"

ElseIf Text1 = "otc" And Text3 = "otc" Then
MsgBox "Welcome,Admin!", vbInformation, "FBCB2"
MsgBox "Welcome to FBCB2 JCR LUT Form Statistics", vbInformation, "FBCB2"
'DoCmd.CloseForm "frm_Login"
DoCmd.OpenForm "frm_FBCB2 JCR LUT _Status"

Expert Comment

ID: 35211720
Can you walk me through the log in process. From the code I see the form frm_Login and it looks like you have probably have 3 textboxes (at least Text1 and Text3) and the command button that the above code it attached to.

What information is entered into Text1, Text2 (if there is one) and Text3 and how does it get there (user types it in or you are giving it a default value)? It looks like you aren't logging in users but what I'd call a security group. AEC being any user and OTC being any admin. Are these the only two valid groups that the voters will be logging in with? I don't see anything in your code to handle a failed login other than the form stays open and they can try again?

You are opening a different form for each log in. Are "frm_FBCB2"
and  "frm_FBCB2 JCR LUT _Status" the forms where the combo boxes and voting are being done?

Is there an reason you are using the GotFocus event of the command button? Typically code executed by buttons would be in the "On Click" event.

It's good practice to name all your controls to make it easier to troubleshoot.Include a prefix with the type of control. As an example your Text1 and Text3 would be tbSecurityGroup or txtVoterGroup, with tb and txt being popular prefixes for textboxes. I have a list of the standard prefixes but not here at home.
 Here are a few changes to get you started. Mainly combining your two message boxes into one and you should open new forms before you close the current one. You need an end if statement.

Private Sub Command5_GotFocus()
'Text1.SetFocus (I remarked this out - not sure you need it)
If Text1 = "aec" And Text3 = "aec" The
     MsgBox "Welcome, User access granted to" & vbCrLf & _
      "FBCB2 JCR LUT Database", vbInformation, "FBCB2"
     DoCmd.OpenForm "frm_FBCB2"
     DoCmd.CloseForm "frm_Login"
ElseIf Text1 = "otc" And Text3 = "otc" Then
     MsgBox "Welcome, Admin access granted to" & vbCrLf & _
     "FBCB2 JCR LUT Form Statistics", vbInformation, "FBCB2"
     DoCmd.OpenForm "frm_FBCB2 JCR LUT _Status"
     DoCmd.CloseForm "frm_Login"
end if

I would be a big help if you can zip up your database and attach it here so I can see the big picture.

Author Comment

ID: 35212468
Here is my database. It is my first one. I have been patching code together to make it work. One of the voters are from a company called aec. There will be a drop down that gives the voters selections. I havent put the drop downs on the forms yet.

Expert Comment

ID: 35216429
The login process can be tricky business. It seems a simple process but the more you try and make it do, the more complex it gets. Here is a simple example that works similar to what you need to do.

There is a table with the employees that is bound to the login form and the user and department is looked up and then hidden. The table also contains the name of the form that should be opened for each person. Having the form name in the table makes it a lot easier to change than going in and changing the code.
You'll see the case statement in the user and admin forms and how it references the hidden login form to determine which combo boxes to disable.

I added a lot of code to the textbox for the login. This is some of the "complex" part. The login value in the table is all lowercase so I want to control what characters the user can enter and make any uppercase letters they enter into lowercase one (it's a simple cool trick as well as being useful). The password can be anything.

Take a look in the employee table to see the valid logins and passwords then give it a try.  

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question