narrow selection with two list boxes

[billwagnon]

I am new to Access and need help! I've done a lot of VB work but the bound nature of Access forms has me stymied.

I have two tables - "Projects" and "ProjectItems".

Projects contains the customer id, the project name, and a description field.

ProjectItems contains all of the items for the project.

I am trying to design a form that will allow the user to first select a customer, then select projects that exist for that customer. When the project is selected the user will see the description of the project and a subform showing the project items.

I used the wizard but I have been unable to set up a project list box that will contain only projects for the customer and allow selection of a project to show the project details.

Thanks for any light!

 

[Rob.Mills]

You have two listboxes, correct?

The first one lists all the customers. You need to create a recourdsource for the 2nd listbox that refers to the 1st listbox for a parameter. Then you need to setup a procedure for the 1st box that when the user selects a customer the 2nd box will be requeried. And that will show only the projects assigned to that customer.

 

[billwagnon]

do I need a special query for the 2nd listbox? I am using the click event for the first listbox.

I also don't know whether to set the 2nd listbox rowsource in code, or in the properties of the listbox.

Thanks for your help!

 

[Rob.Mills]

I prefer to use SQL statements for recordsources of all my forms, comboboxes and listboxes. You're correct in using the click event. Also you'll need to setup a click event for the 2nd that will requery the form which should have a recordsource that refers to the 2nd box as a parameter.

 

[billwagnon]

here is my click event:

    Dim sCriteria As String
    Dim bFound As Boolean
    
    'On Error Resume Next
    sCriteria = "Customer='" & cboCustomer & "'"
    bFound = (DCount("Customer", "_Project", sCriteria) = 1)
    
    If bFound Then
        Filter = sCriteria
        FilterOn = True
        lsbProject.RowSource = "SELECT Project, ProjectName FROM _Project " & _ 
" WHERE Customer='" & Customer & "'"
        lsbProject.Requery
    Else
        Beep
    End If

My form recordsource is simply "_Project" - I tried making it a sql query where project=project but it didn't like that!

 

[Rob.Mills]

Ouch! Way too complicated. Good try, though.

Are you familiar with the query builder inside a form. Make sure there's nothing in the recordsource and click the button at the end with the three dots. This will open a query builder. Build the query there but don't hit the save button. Just press the close button and it will ask if you want to save it as a Select Statement... click yes.

The 1st box you can use the customer table to make things simple. The second box setup a select statement where the criteria in the customer id field is: Forms!FormName!1stListboxName

That way everytime a recordsource is generated for the 2nd box it's going to filter based on the customer selected in the 1st box.

In your click event for the 1st box type:

Private Sub ListboxName_Click()

Me!2ndListboxName.Requery

End Sub


Do something similar for the 2nd box and the form itself.

 

[billwagnon]

progress!

thanks for all your help, I'm actually getting somewhere! I've been banging my head on this a couple days and progress is exciting!

couple of problems -

once I select a project, I can still select from the customer combobox, and if I change it, the customer on the project is changed too! If there's not a better way I will code a lock on the customer combobox.

on the comboboxes, when I am selecting I can see the id and the description field (or whatever field I want). I need to use the id for the value, but can I show the name (ie, "Test Project ABE") in the combobox instead of just "9" when it's not in dropdown mode?

here's a screenshot:

 

[Rob.Mills]

What you need to do is leave the bound column to 1, Columns to 2, and then set the column widths to this:

0";1"

Or whatever size you want for the second. This will make only the description show but the actual value of the combobox will be the id #

 

[billwagnon]

that shows the name in the combobox, but I want to show everything in the drop down

I am planning to code on the click to change column widths.

 

[billwagnon]

okay, I got it I just changed the query to pull all the goodies for the second column as a concatenated string, with the id column being 0 width

 

[Rob.Mills]

If you want to show more fields then increase the column count.