Results 1 to 14 of 14

Thread: List Box (2000)

  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    List Box (2000)

    I have a list box with the following code behind it:

    Private Sub CLNames_DblClick(Cancel As Integer)
    Me.RecordsetClone.FindFirst "[DataID] = " & Me![CLNames]
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End Sub
    (This list box shows all the records in the database) One of the fields in this listbox is the Rep Last Name

    What I would like to do is for the list box to be invisible when the form is open. The first field on the form is a dropdown list of Rep last names. Once a name is selected from the list, I would like only the records pertaining to that rep to appear. Is it possible to tweak the code above to do this? Thanks for all your help...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Box (2000)

    Set the Visible property of the list box to No in the design of the form.
    Create a query that selects the fields you need for the list box, and set the criteria for the Rep Last Name field to [Forms]![frmMyForm]![cbxMyDropdown] where frmMyForm is the name of your form, and cbxMyDropdown the name of the dropdown list on the form from which the user selects a Rep.
    Create an After Update event handler for the dropdown list:

    Private Sub cbxMyDropdown_AfterUpdate()
    If IsNull(Me.cbxMyDropdown) Then
    Me.CLNames.Visible = False
    Else
    Me.CLNames.Visible = True
    Me.CLNames.Requery
    End If
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box (2000)

    It isn't working. I must be doing something wrong. Do I still keep the code that is originally behind the CLNames list box? There is a query behind the listbox and that is where I put the criteria for the Rep last Name field.

    I open the form and the list box is invisible. When I choose a name from the drop down list - nothing shows in the list box?

    Sorry Hans - It is working - I must have been doing something wrong when I originally did it. Thanks a bunch........

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box (2000)

    When I open the form there is a record showing. Is there any way to open the form with a blank record and still be able to be in Edit mode?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Box (2000)

    If you set the DataEntry property of the form to Yes, you will ONLY see a new, blank record when the form opens. If you want existing records to be available, you can move to a new record when the form opens:

    Private Sub Form_Load()
    RunCommand acCmdRecordsGoToNew
    End Sub

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box (2000)

    I need to put a password on this form so that before they select their name from the dropdown list, they have to put their password in. The table that has the names for this dropdown list also has a password assigned to each name. Is there a way so that when they type the password in before selecting their name, they can only select the name that matches the password? I really don't want to get into Access security for this database.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Box (2000)

    What is the point of having a dropdown list from which you can select only one name?

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box (2000)

    When they select their name from the drop down list, there is a list box that shows only their records. So in theory they can select someone else's name and bring up their records. If there was a field before the dropdown list that had to coincide with their name in the dropdown list then they could only see their records. Maybe an unbound text box before the dropdown list could have some code behind it that they have to type in a password so they only can pick their name. They would have to know someones password to pick the other names. Say the 4 names in the dropdown list were Smith, Brown, Peters, White and the passwords were Red, Blue, Orange and Peach respectively. If they typed in Peach then they could only pick the name White. Maybe I should just take the list box off the form. This is a nightmare.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Box (2000)

    But why would they have to pick a name anyway? You seem to give them access on the basis of the password alone. Usually, you test if the combination of username and password is valid - somebody has to know both to get in. But if the username corresponding to the password pops up automatically in the list, you might as well not show it, and display the records belonging to the password. Am I missing something?

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box (2000)

    I know you must think I am crazy. This database is making me hallucinate. They are picking their name so only their records show in the corresponding list box. If they pick someone elses name, they can see that other persons records in the corresponding list box . I just want an unbound text box before they can select their name so they can't select someone elses name.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Box (2000)

    But they don't NEED to select a name. There is only ONE name corresponding to a valid password, so you (as programmer) KNOW which name it is. You can simply display ONLY the records that belong to the name that belongs to the password. If the password is invalid, there is no name corresponding to the password, so you can't display any records.

    In your example: the user types "Orange" as password. You look up the corresponding name in the table, i.e. "Peters", and display the records for "Peters".
    Another user types "Lavender" as password. Since there is no matching name, the password is invalid, and you don't display any records.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box (2000)

    Can you tell me how to do this using the form I have already created? Can you look at the database. I don't know how to change from a list box back to a text box without redoing the whole database. I have stripped the reports and some queries off of this database so it is just the form and tables and queries that go with the form. If I have to, I will start from scratch. If I put the password field on the form, it doesn't let me enter anything. Thanks for your help.
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: List Box (2000)

    Does the attached help? I used an unbound text box to enter the password; the RepID is set in the After Update event, as is the visibility of CLNames.
    Attached Files Attached Files

  14. #14
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: List Box (2000)

    Thanks Hans - it works. I am forever in your debt. Hopefully when I show this to my manager, she will be satisfied.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •