John W. Vinson[MVP] - Combo box problem continued

[Nick]

I looked at the url that John W. Vinson[MVP] told me to
look at and I cant get it working!!!

The site says:

"The RowSourceType of cbxCombo1 is set to "Field List" and
RowSource to a table Category. cbxCombo2 doesn't have
anything under RowSource."

^^^ Does this mean that cbxCombo2 has RowSourceType set
to "Field List" aswell or is it set to "Table/Query"???

The next bit of code goes into the After Update of the
first combo box:

"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"

^^^ So all I change here is the cbxCombo1 & 2 to the names
of my combo boxes, or do I need to change something else???

Lastly, where do I put and what do I need to change with
this:

"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 thats left to do is put this bit of code in the
After Update of the second combo box right???:

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

^^^
Also, what needs changing here???

Sorry, I know I sound dumb, but if I put obvious questions
down then I wont have to ask again if I dont get it working

Thanks for your time

Nick

 

[Guest]

Nick

I was wondering if there is a way to manipulate a combo
box list depending on what was selected in the combo box
before it

Yes, its called cascading combo boxes
This is how to hook them together. You will need to change the table, form and combo box names to the names you use

Let's say your branch & employee info is in a table named tblBranchEmp, with two fields: Branches and Employees and a form named frmAddInfo has two combo boxes: cboBranches and cboEmployees

Open the form in design view. Click on cboBranches. Open the properties window. Click on the Data tab. The Row Source Type field should be "Table/Query". Click in the Row source field and type (or copy and paste

Select Distinct Branches From tblBranchEmp

Click on the Event tab. Click in the AfterUpdate field, click on the down arrow and select [Event Procedure]. Click on the builder (...). Enter this in the subroutin

Me.cboEmployees.Requer
Me.cboEmployees.SetFocu

Close the VBA editor.

Now click on cboEmployees and Select the Data tab. The Row Source Type field should be "Table/Query". Copy this line
(should be one line)

SELECT tblBranchEmp.Employees, tblBranchEmp.Branches FROM tblBranchEmp WHERE tblBranchEmp.Branches=[Forms]![frmNewInfo].[cboBranchs]

and paste it in the Row Source field (remember to change the names to what you use)
Click on the Format tab. In the Column Count field enter 2. In Column Widths enter 1;

Click on the Event tab. In the On Got Focus row, select [Event Procedure]. Click on the builder (...). Ente

Me.cboEmployees.Dropdow

in the GotFocus subroutine

Close and save the form. Open the form and test the combo boxes

HT

Stev