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

Posted on 2011-03-23
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
  • 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.
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.


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 500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ms access 2010 vba, copy table from db1 to db2 from db3 27 39
How to use DLookup with IsNull Function 4 22
Mysql Left Join Case 10 44
Mongo DB 18 35
Read about achieving the basic levels of HRIS security in the workplace.
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

813 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now