Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamically Updating a List Box (2000)

    Hi all,

    I feel like I should be able to do this but I can't figure it out! Maybe someone out there can help?

    I am trying to create a form into which supervisors can put their part-time employee's time. I have a combo box on my form that asks the user to select the department that they are going to enter hours into. From there, I want the list box to read which department has been selected, and list the names of the employees in that specific department. The source of the form is a table that I have created called "TIMECARDS". The source of the list box is "PT_STAFF".

    Here's the SQL for the list box:
    SELECT [PT_STAFF].[ID_NO], [PT_STAFF].[LAST_NAME], [PT_STAFF].[FIRST_NAME] FROM PT_STAFF;

    I tried to add a "WHERE" clause onto that to read the combo box, which works, but if I select a different department from the combo box, the contents of the list box do not change.

    Thanks much,
    Kindra

  2. #2
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically Updating a List Box (2000)

    Hm, I tried doing what you have suggested and the contents of the list box are still not changing. In the VB code, shouldn't I be requerying the list box and not the combo box?

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamically Updating a List Box (2000)

    Never mind -- I changed the VB code to read "Me.lstNames.Requery" and it works like a charm. Thanks Hans!!!

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

    Re: Dynamically Updating a List Box (2000)

    Oops, yes, as you found out, it should be the list box. Sorry about that, but I see that you got it fixed.

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

    Re: Dynamically Updating a List Box (2000)

    <P ID="edit" class=small>(Edited by HansV on 16-Oct-03 21:16. Corrected After Update code.)</P>Adding a WHERE condition to the row source for the list box is correct. I don't know the name of the department field and of the combo box and the form, but it should look like this:

    SELECT [PT_STAFF].[ID_NO], [PT_STAFF].[LAST_NAME], [PT_STAFF].[FIRST_NAME] FROM PT_STAFF WHERE [PT_STAFF].[department] = [Forms]![MyForm]![MyComboBox]

    You must requery the list box when the combo box changes. This takes one line of VBA code:

    Open the form in design view.
    Select the combo box.
    Activate the Event tab of the Properties window.
    Click in the After Update event.
    Select Event Procedure from the dropdown list.
    Click the Builder button (the three dots to the right of the dropdown arrow)
    You will be taken to the Visual Basic Editor.
    Complete the code to look like this, with the name of the combo box and list box substituted:

    Private Sub MyComboBox_AfterUpdate()
    Me.MyListBox.Requery ' originally had combo box here - it should be the list box
    End Sub

    Switch back to Access (Alt+F11)
    Save the form.

Posting Permissions

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