Combo box enabling

[lskuff]

I neeed to make a form with 2 combo boxes.
Ex.
cb1 cb2

I want the 2nd combo box to be disabled until someone makes a selection in the first combo box. Then, after they make a selection I need the second combo box to become enabled and have selections based on what was selected in the first combo box.

I know that is kind of confusing, but if someone could give me a little help it would be appreciated. Thanks.

 

[dpimental]

First, you start out with ....
cb1 enabled
cb2.enabled = False

After update of cb1
cb2 becomes enabled
cb2.enabled = True

The a field in the query for cb2 is based on the value of cb1.

So, after update ...
cb2.enabled = True
cb2.requery

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[dpimental]

Sorry, I typed too quickly.

I meant to say ...
The query of cb2 is based upon the value of cb1

For example ...
cb1 may select cities.

cb2 may look up the state for the city in cb1.

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[lskuff]

Okay thanks. I understand the enable part just fine. But could someone explain in a little more detail how to do the querry part? Thanks.

 

[dpimental]

Actually, this is a common practice. I believe there is an example in the Solutions or NorthWind database examples shipped with MsAccess.

It's called limiting one combo box by the values selected from another combobox. Or something like that.

The only difference here is that your second combo box remains disabled until the first combo box has been updated.

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[dpimental]

What is the relationship between the information in cb1 and cb2?

You built the first combo box how.

In design mode you can right click the combo box and select properties.

Put your cursor in the RowSource Property and click the build button (the one with 3 dots).

This will show you the query of the first combo box.

Do the same for the second combo box; this time, the query should contain the field used in the first combo box and an additional field. Set the field used in the first combo box to "Forms![YourFormName]![comboboxonename]".

Then when the first combo box is updated, it will enable the 2nd combo box and requery the 2nd combo box,which will look for the value of combo box one and select the additional field based on that value.

Clear as Mud?


David Pimental
(US, Oh)
dpimental@checkfree.com

 

[dpimental]

Try this from AccessWeb

---Posted by Dev Ashish---

------------------------------------------------------------
Forms: Limit content of combo/list boxes
------------------------------------------------------------

(Q)    How can I limit the contents of one combo/list box based on what's selected in another combo/list box?

(A)    An easy way to do this would be to assign a dynamic SQL statment to the RowSource of the secondary combo/list box at runtime.

    Let's say you have two comboboxes, cbxCombo1 and cbxCombo2.   The RowSourceType of cbxCombo1 is set to "Field List" and RowSource to a table Category.  cbxCombo2 doesn't have anything under RowSource.

    In this case,  you can put code in the AfterUpdate event of cbxCombo1 that assigns the proper RowSource to cbxCombo2.

'**************** Code Start *************
Private Sub cbxCombo1_AfterUpdate()
Dim strSQL As String
    strSQL = "Select " & Me!cbxCombo1
    strSQL = strSQL & " from Categories"
    Me!cbxCombo2.RowSourceType = "Table/Query"
    Me!cbxCombo2.RowSource = strSQL
End Sub
'**************** Code End *************
 

     To filter records in a combo/listbox based on the value selected in another combo/listbox,  you can use a stored query which uses the first control's value as a parameter. For example,

Select PeopleID, PeopleName from tblPeople Where PeopleID = Forms!FormName!NameOfFirstControl;

Then all you need to do is issue a Requery on the second combo/listbox in this first control's AfterUpdate event.

Private Sub NameOfFirstControl_AfterUpdate()
    Me!NameOfSecondControl.Requery
End Sub

David Pimental
(US, Oh)
dpimental@checkfree.com

 

[RoyVidar]

The Access Web is a great recourse. In the future [blue]dpimental[/blue], why don't you link to the page Forms: Limit content of combo/list boxes. Posting it here might violate the Terms Of Use.

Roy-Vidar

 

[lskuff]

Thanks for all the replies. I just have one more question. The first combo box selects a field from a table the second combo box has all the values from that field. I just am wondering how I can write my query to make sure there are no duplicate values in the second combo box. Thanks - Lenni

 

[dpimental]

Thanks RoyVidar. Sorry, wasn't aware I was violating any rules.

David Pimental
(US, Oh)
dpimental@checkfree.com