Link to home
Create AccountLog in
Avatar of Frank Freese
Frank FreeseFlag for United States of America

asked on

Combo two based upon combo one

Experts (Happy 2011),
I have two tables:
tblDivision
    GJDivisionID (PK) autonumber
    GJDivision (number)
tblContract
    ContractID (PK) autonumber
    Contract (number)
    GJDivisionID (lookup from tblDivision)
In my form I have two controls
    cboDivision
    cboContract

When I select a division (cboDivision) I need to only show those contracts associated with that division populating cboContract so I can select that contract for that division.
Avatar of puppydogbuddy
puppydogbuddy


the situation you described with results of the second combobox dependent on the results of the first combobox is known as "cascading" comboxes.
 
try placing the code below in the AfterUpdate event of cboDivision as the rowsource of cboContract.

Private Sub cboDivision_AfterUpdate()
cboContract.RowSource = "Select Contract, GJDivisionID From tblContract Where GJDivisionID = Me!cboDivision.Column(0)"

End Sub
Avatar of Frank Freese

ASKER

I got an object required error
i may have found my problem
My first table is tblGJDivision not tblDivision
Please consider this code for my record source for cboContract - I'm getting an invalid bracketing

SELECT tblContract.ContractID, [Contract] & " " & [Contract_Description] AS ContractDescrip, tblGJDivision.GJDivision
FROM tblContract INNER JOIN tblGJDivision ON tblContract.Division = tblGJDivision.GJDivisionID
WHERE (((tblGJDivision.GJDivision)=[forms]![frmChartOfAccounts].[GJDivision.Column(0)]))
ORDER BY [Contract] & " " & [Contract_Description];
Avatar of Hamed Nasr
remove all the brackets except (0).

You need a cboContract.Requery statement in the AfterUpdate of cboDivision.
hnasr:
Is this correct?

SELECT tblContract.ContractID, Contract & " " & Contract_Description AS ContractDescrip, tblGJDivision.GJDivision
FROM tblContract INNER JOIN tblGJDivision ON tblContract.Division = tblGJDivision.GJDivisionID
WHERE (((tblGJDivision.GJDivision)=forms!frmChartOfAccounts.GJDivision.Column(0)))
ORDER BY Contract & " " & Contract_Description;
I got it?

SELECT tblContract.ContractID, [Contract] & " " & [Contract_Description] AS ContractDescrip, tblContract.Division
FROM tblContract
WHERE (((tblContract.Division)=[forms]![frmChartOfAccounts]![cboGJID]))
ORDER BY [Contract] & " " & [Contract_Description];

and requery
ASKER CERTIFIED SOLUTION
Avatar of Hamed Nasr
Hamed Nasr
Flag of Oman image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
perfectly thanks