Forms - having a sort applied from a pull down menu

[Newton]

I have a table where the primary key is a combination of the project name and an assigned room number. This works logically because in our building we dont have duplicate numbers in the same building.

the rooms are all kept in one room table, and there is a key field for project name so that there is always a unique record. I can query this list and sort out by project.

I have a problem with my form however,

I have them pick the project name from a pull down list, and then when the pick the room number they are all there to choose from (including several room 110's for example) is there a way that my room pull down field can be only the list that would appear after a filter of the project (selected in a prior field)

Sort of a dynamic pull down list?

I am having troubles searching for the help because if it does exist I am not sure what this feature is called.

thanks in advance

Newton

 

[raskew]

This sounds like a case of cascading combo-boxes.

Here's an example, using Northwind, which you could adapt for your purposes.

(1) In Northwind, create a new, unbound form.

(2) With the Wizard off, add a combo box (combo0). Set:
Bound column: 1
Column count: 2
Column width: 0"; 2.0"
Row Source Type: Table/Query
Row Source: SELECT DISTINCT [CategoryID], [CategoryName] FROM [Categories];

After-Update Event: (copy/paste the following)
Private Sub Combo0_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT [ProductID], [ProductName] FROM [Products] " _
& "WHERE categoryID = " & Me!Combo0 & ";"
With Me![Combo2]
.RowSource = strSQL
.SetFocus
.Dropdown
End With
End Sub

(3) Highlight combo0. Click on Edit/Duplicate. This should place another combo box (combo2) beneath Combo0.

(4) Return to Form View and run the form. If all goes well, you'll find that combo2 selections apply only to the category selected in combo0.

Does this help?

 

[Newton]

That sounds like exactly what I want to do...but can I do it? that remains to be seen. Thank you for pointing me in the right direction, I 'll let you guys know if I run into problems

 

[Newton]

I just cant get it to work totally, I have changed the field names to match my table instead of the northwinds example, but It keeps bringing back to here in debugger

1st when i go to pull the field in the form it prompts me for the unit number? I dont know why it is doing that I changed the columb width around to make it hide the unit number from the pick list, and just display the Project name

anyway here is my mucked up version I must not know enough about it to make simple changes

Private Sub Combo79_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT [ProjectName], [UnitNumber] FROM [Unit] " _
& "WHERE ProjectName = " & Me!Combo0 & ";"
With Me![Combo2]
.RowSource = strSQL
.SetFocus
.Dropdown
End With
End Sub

 

[Newton]

Sorry I got a little to exuberant I should have outlined what I think I dont know

Oh yeah I have messed a bit with it

The line With Me![Combo2] - What is that statement doing? I just want to have another combo box sort a list based on this combo boxes selection...I dont have a [combo2] box.

Do I need to copy this one first?

Private Sub Combo79_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT DISTINCT [ProjectName], [UnitNumber] FROM [Unit] " _
& "WHERE ProjectName = " & Me!Combo79 & ";"
With Me![Combo2]
.RowSource = strSQL
.SetFocus
.Dropdown
End With
End Su