none
How to reference the individual choices of a dropdown box containing text. What is the value?! RRS feed

  • Question

  • Hello guys,

    I know this question has probably been asked many times in different contexts, but I have been breaking my head over this for 5 days now and I officially need help. I'm just a "wanna-be" developer; therefore, I have no real understanding of the coding, but I have managed to program a fairly complex database with logic and tons of research. I'm an RN in the military as my full-time job, this is a hobby of mine. So... I'm working on this database with a login. I want to make it so when the person who creates an account for someone they can select what type of access they want that person to have i.e. Developer, Student, General User, and Supervisor. I have a dropdown with these options when that person creates the username, the issue is I cant reference to those options in my login form. For example... if User_Access in table tblPatientDatabase is Developer then do this.... or if is Student then do this... I have managed to do this with numerical values but not with text.

    the If rs!EmployeeType_ID = 4 Then blah blah blah works, but I want to make EmployeeType_ID into a dropdown that has text as options and not numerical values so the end user can choose "Developer, Student, General User, or Supervisor" instead of "1, 2, 3, 4"

    P.S. I have tried If rs!EmployeeType.ListIndex = "" and rs!EmployeeType.ItemData = "" without success.

    
    Private Sub cmdLogin_Click()
        Dim rs As Recordset
        
        Set rs = CurrentDb.OpenRecordset("tbl1UserAccounts", dbOpenSnapshot, dbReadOnly)
        rs.FindFirst "UserName='" & Me.txtUserName & "'"
        
        If rs.NoMatch Then
            'Me.lblWrongUser.Visible = True
            MsgBox "The username you entered is not valid, please try again.", vbCritical, "INVALID USERNAME!"
            Me.txtUserName.SetFocus
            Exit Sub
        End If
        'Me.lblWrongUser.Visible = False
        
        If rs!Password <> Nz(Me.txtPassword, "") Then
            'Me.lblWrongPass.Visible = True
            MsgBox "The password entered does not match, please try again.", vbCritical, "INVALID PASSWORD!"
            Me.txtPassword.SetFocus
            Exit Sub
        End If
        'Me.lblWrongPass.Visible = False
        
        If rs!EmployeeType_ID = 4 Then
            Dim prop As Property
            On Error GoTo SetProperty
            Set prop = CurrentDb.CreateProperty("AllowBypassKey", dbBoolean, False)
            
            CurrentDb.Properties.Append prop
    
    SetProperty:
        If MsgBox("Would you like to turn on the bypass key?", vbYesNo, "Allow Bypass") = vbYes Then
            CurrentDb.Properties("AllowBypassKey") = True
        Else
            CurrentDb.Properties("AllowBypassKey") = False
        End If
    
    End If
        DoCmd.OpenForm "frmStartShell"
        DoCmd.Close acForm, Me.Name
    End Sub



    • Edited by InnVis Thursday, June 8, 2017 10:17 PM
    Thursday, June 8, 2017 9:54 PM

Answers

  • Hi,

    Using numbers in your table and code should be fine (and better). To display text on the dropdown, simply adjust the Row Source to include the text columns from the table and then adjust the number of columns and their widths in design view. For example, if the Row Source pulls two columns (ID and Text) from the table, make sure Column Count is set to 2 and the Column Widths is set to 0";2" (or similar, but make sure the first one is zero).

    Hope it helps...

    • Marked as answer by InnVis Friday, June 9, 2017 12:44 PM
    Thursday, June 8, 2017 10:02 PM

All replies

  • Hi,

    Using numbers in your table and code should be fine (and better). To display text on the dropdown, simply adjust the Row Source to include the text columns from the table and then adjust the number of columns and their widths in design view. For example, if the Row Source pulls two columns (ID and Text) from the table, make sure Column Count is set to 2 and the Column Widths is set to 0";2" (or similar, but make sure the first one is zero).

    Hope it helps...

    • Marked as answer by InnVis Friday, June 9, 2017 12:44 PM
    Thursday, June 8, 2017 10:02 PM
  • Well what I want is to reference text only not numbers. The way I have it now is a dropdown with the values 1, 2, 3, and 4... then the end user is able to pick these numbers and each number means something 4 = Developer, 1 = Student, 2 = General User, and 3 = Supervisor. This is not user friends, so I want to display just the text in the dropdown and have the user choose what they want instead if picking a number from 1-4. The issue is, I can't figure out a way to reference that text's value in the validation section of my login form. Instead of rs!EmployeeType_ID = 4... I want it to be something like rs!Employee_ID = "Developer"

    I wish it was this simple, but doesn't work >.<



    • Edited by InnVis Thursday, June 8, 2017 10:29 PM
    Thursday, June 8, 2017 10:27 PM
  • Oh wow, I think I understand it now!! This just blew my mind and it's going to give me a lot more possibilities. So I Made a field in my main table which includes all of the data I want and I named it INSERTION STATUS, I made that field a numerical drop down box with no values in it at all. Then I created a separate table named tblInsertionStatus and made an ascending numerical field named ID and a second field named INSERTION STATUS then I created he following entries: PENDING, SUCCESSFUL, UNSUCCESSFUL, and CANCELLED... then I linked the tblInsertionStatus visa relationship to the INSERTION STATUS drop down field in my main table, then I followed your advice above to not display the numerical value only the text, but I was still able to reference to the text with numerical value of the ID field via vba code! Love it, thank you thank you thank you!
    Friday, June 9, 2017 12:13 AM
  • Hi,

    You're welcome. Glad we could assist. Good luck with your project.

    Friday, June 9, 2017 4:34 AM
  • Hi J. Alexander Batista,

    from your last post , I can see that your issue is solved now.

    but this thread is still open.

    I suggest you to mark the answer.

    so that we can close this thread.

    if you do not mark the answer then it will remain open.

    so help us to close this thread.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 9, 2017 5:49 AM
    Moderator