Filter Combo Box B based on Combo Box A Selection

  • Thread starter Thread starter Stacey Crowhurst
  • Start date Start date
S

Stacey Crowhurst

Hi. I have a form with two combo boxes.
Combo Box A does this:
SELECT tblAccountCodes.acAccountCodeID, tblAccountCodes.acAccountCodeDesc
FROM tblAccountCodes ORDER BY tblAccountCodes.acAccountCodeID;

Combo Box B does this:
SELECT tblBudgetCodes.bcBudgetCodeID, tblBudgetCodes.bcBudgetCodeDesc FROM
tblBudgetCodes ORDER BY tblBudgetCodes.bcBudgetCodeID;

What I would like to happen is that when the user picks their choice
[acAccountCodeID] for Combo Box A, the list [bcBudgetCodeID]given for Combo
Box B is appropriately filtered.

Here is how the Account Code and Budget Code relate.

The last three characters of the account code are the first three characters
of the budget code. Like so...

Account Code Budget Code
540105 105-100
540105 105-101
540110 110-200
540115 115-300
540115 115-301 etc. etc.

So if a user picks 540105 I only want the budget codes that begin with
105-xxx to show up Combo Box B.

Any ideas or suggestions? Please let me know if you need more information.

Thanks!!!
 
Do you have a relationship established between the two tables
with the acAccountCodeID field in tblBudgetCodes as a
foreign key?
 
Yes. It is called bcAccountCodeID in the budget code table.

Beetle said:
Do you have a relationship established between the two tables
with the acAccountCodeID field in tblBudgetCodes as a
foreign key?
--
_________

Sean Bailey


Stacey Crowhurst said:
Hi. I have a form with two combo boxes.
Combo Box A does this:
SELECT tblAccountCodes.acAccountCodeID, tblAccountCodes.acAccountCodeDesc
FROM tblAccountCodes ORDER BY tblAccountCodes.acAccountCodeID;

Combo Box B does this:
SELECT tblBudgetCodes.bcBudgetCodeID, tblBudgetCodes.bcBudgetCodeDesc FROM
tblBudgetCodes ORDER BY tblBudgetCodes.bcBudgetCodeID;

What I would like to happen is that when the user picks their choice
[acAccountCodeID] for Combo Box A, the list [bcBudgetCodeID]given for Combo
Box B is appropriately filtered.

Here is how the Account Code and Budget Code relate.

The last three characters of the account code are the first three characters
of the budget code. Like so...

Account Code Budget Code
540105 105-100
540105 105-101
540110 110-200
540115 115-300
540115 115-301 etc. etc.

So if a user picks 540105 I only want the budget codes that begin with
105-xxx to show up Combo Box B.

Any ideas or suggestions? Please let me know if you need more information.

Thanks!!!
Click to expand...
Click to expand...
 
Then you can just add that field as another column in the
row source query for combo box B, and in the criteria row
for that column put a reference to combo box A using syntax
like;

Forms!frmYourForm!ComboA

So the SQL might look like;

SELECT tblBudgetCodes.bcBudgetCodeID,
tblBudgetCodes.bcBudgetCodeDesc, tblBudgetCodes.bcAccountCodeID
FROM tblBudgetCodes
WHERE bcAccountCodeID = Forms!frmYourForm!ComboA
ORDER BY tblBudgetCodes.bcBudgetCodeID;

Then, in the After Update event of combo box A you would
requery combo box B

Private Sub ComboA_AfterUpdate ()

Me.ComboB.Requery

End Sub

--
_________

Sean Bailey


Stacey Crowhurst said:
Yes. It is called bcAccountCodeID in the budget code table.

Beetle said:
Do you have a relationship established between the two tables
with the acAccountCodeID field in tblBudgetCodes as a
foreign key?
--
_________

Sean Bailey


Stacey Crowhurst said:
Hi. I have a form with two combo boxes.
Combo Box A does this:
SELECT tblAccountCodes.acAccountCodeID, tblAccountCodes.acAccountCodeDesc
FROM tblAccountCodes ORDER BY tblAccountCodes.acAccountCodeID;

Combo Box B does this:
SELECT tblBudgetCodes.bcBudgetCodeID, tblBudgetCodes.bcBudgetCodeDesc FROM
tblBudgetCodes ORDER BY tblBudgetCodes.bcBudgetCodeID;

What I would like to happen is that when the user picks their choice
[acAccountCodeID] for Combo Box A, the list [bcBudgetCodeID]given for Combo
Box B is appropriately filtered.

Here is how the Account Code and Budget Code relate.

The last three characters of the account code are the first three characters
of the budget code. Like so...

Account Code Budget Code
540105 105-100
540105 105-101
540110 110-200
540115 115-300
540115 115-301 etc. etc.

So if a user picks 540105 I only want the budget codes that begin with
105-xxx to show up Combo Box B.

Any ideas or suggestions? Please let me know if you need more information.

Thanks!!!
Click to expand...
Click to expand...
Click to expand...
 
PERFECT! Thanks much :)

Beetle said:
Then you can just add that field as another column in the
row source query for combo box B, and in the criteria row
for that column put a reference to combo box A using syntax
like;

Forms!frmYourForm!ComboA

So the SQL might look like;

SELECT tblBudgetCodes.bcBudgetCodeID,
tblBudgetCodes.bcBudgetCodeDesc, tblBudgetCodes.bcAccountCodeID
FROM tblBudgetCodes
WHERE bcAccountCodeID = Forms!frmYourForm!ComboA
ORDER BY tblBudgetCodes.bcBudgetCodeID;

Then, in the After Update event of combo box A you would
requery combo box B

Private Sub ComboA_AfterUpdate ()

Me.ComboB.Requery

End Sub

--
_________

Sean Bailey


Stacey Crowhurst said:
Yes. It is called bcAccountCodeID in the budget code table.

Beetle said:
Do you have a relationship established between the two tables
with the acAccountCodeID field in tblBudgetCodes as a
foreign key?
--
_________

Sean Bailey


:

Hi. I have a form with two combo boxes.
Combo Box A does this:
SELECT tblAccountCodes.acAccountCodeID, tblAccountCodes.acAccountCodeDesc
FROM tblAccountCodes ORDER BY tblAccountCodes.acAccountCodeID;

Combo Box B does this:
SELECT tblBudgetCodes.bcBudgetCodeID, tblBudgetCodes.bcBudgetCodeDesc FROM
tblBudgetCodes ORDER BY tblBudgetCodes.bcBudgetCodeID;

What I would like to happen is that when the user picks their choice
[acAccountCodeID] for Combo Box A, the list [bcBudgetCodeID]given for Combo
Box B is appropriately filtered.

Here is how the Account Code and Budget Code relate.

The last three characters of the account code are the first three characters
of the budget code. Like so...

Account Code Budget Code
540105 105-100
540105 105-101
540110 110-200
540115 115-300
540115 115-301 etc. etc.

So if a user picks 540105 I only want the budget codes that begin with
105-xxx to show up Combo Box B.

Any ideas or suggestions? Please let me know if you need more information.

Thanks!!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top