combo box list based on the selection in another combo box

[Guest]

I have a form in which I have two combo boxes. The first combo selects a
clients office. The second combo selects a clients name that works in the
office.

Is there a way to make the second combo box list just the people in the
office selected in the first combo box?

presently the lists of offices and clients are in seperate unrelated tables.

Thanks! I would appreciate any help on this.

 

[Guest]

This link will show you one method

http://www.databasedev.co.uk/filter_combo_boxes.html

=========================
Another way will be
In the RowSource of the second combo you can create a reference to the first
one

Select Field1 From TableNAme Where Field2 = Forms![FormName]![Combo1Name]

On the after update event of the first combo you run the code to refresh the
list in the second combo

Me.[Combo2Name].Requery

 

[Guest]

Your Awesome! I used your solution and it worked. thanks!

Now I am having a problem with the second combo box. I need to allow the
user to add an item that isn't in the list.

I created a maco that opens a form with a txt box control and a command
button to add the record and requery the list (which is based on the
selection in the first combo box).

here is the code and the err message. Neither the table or the list gets
updated.

Private Sub CmdAddID_Click()
On Error GoTo Err_CmdAddID_Click

DoCmd.GoToRecord , , acNewRec
DoCmd.Requery
DoCmd.Close


Exit_CmdAddID_Click:
Exit Sub

Err_CmdAddID_Click:
MsgBox Err.Description
Resume Exit_CmdAddID_Click

End Sub

Err: You can't use the GoToRecord action or method on a object in Design view

This link will show you one method

http://www.databasedev.co.uk/filter_combo_boxes.html

=========================
Another way will be
In the RowSource of the second combo you can create a reference to the first
one

Select Field1 From TableNAme Where Field2 = Forms![FormName]![Combo1Name]

On the after update event of the first combo you run the code to refresh the
list in the second combo

Me.[Combo2Name].Requery

--
Good Luck
BS"D

I have a form in which I have two combo boxes. The first combo selects a
clients office. The second combo selects a clients name that works in the
office.

Is there a way to make the second combo box list just the people in the
office selected in the first combo box?

presently the lists of offices and clients are in seperate unrelated tables.

Thanks! I would appreciate any help on this.

 

[Guest]

Hi Marianne
Try this link on how to add an item to a combo

http://www.databasedev.co.uk/not_in_list.html

--
Good Luck
BS"D

Your Awesome! I used your solution and it worked. thanks!

Now I am having a problem with the second combo box. I need to allow the
user to add an item that isn't in the list.

I created a maco that opens a form with a txt box control and a command
button to add the record and requery the list (which is based on the
selection in the first combo box).

here is the code and the err message. Neither the table or the list gets
updated.

Private Sub CmdAddID_Click()
On Error GoTo Err_CmdAddID_Click

DoCmd.GoToRecord , , acNewRec
DoCmd.Requery
DoCmd.Close


Exit_CmdAddID_Click:
Exit Sub

Err_CmdAddID_Click:
MsgBox Err.Description
Resume Exit_CmdAddID_Click

End Sub

Err: You can't use the GoToRecord action or method on a object in Design view

This link will show you one method

http://www.databasedev.co.uk/filter_combo_boxes.html

=========================
Another way will be
In the RowSource of the second combo you can create a reference to the first
one

Select Field1 From TableNAme Where Field2 = Forms![FormName]![Combo1Name]

On the after update event of the first combo you run the code to refresh the
list in the second combo

Me.[Combo2Name].Requery

--
Good Luck
BS"D

I have a form in which I have two combo boxes. The first combo selects a
clients office. The second combo selects a clients name that works in the
office.

Is there a way to make the second combo box list just the people in the
office selected in the first combo box?

presently the lists of offices and clients are in seperate unrelated tables.

Thanks! I would appreciate any help on this.