bound combo box filtering

[elfudge35]

I have a three table structure with this setup

tblPolicy
PolicyID
Policy#

tblEndorsement
EndorsementID
Endorsement#
PolicyID

tblTransaction
TransactionID
TransactionDetails
EndorsementID
PolicyID

Some of the EndorsementIDs in tblTransaction refer to the first Endorsement# in their policy and I need to have somebody check them and change where necessary

I have an unbound main form with PolicyID and Policy#

The bound subform needs to have a Combo box displaying the Endorsement# from tblEndorsement corresponding to the EndorsementID in tblTransaction and displaying all of the Endorsement# possibilities from tblEndorsement for that PolicyID and that PolicyID only so it can update the correct EndorsementID

and once the user chooses the EndorsementID foreign key needs to update

I can't seem to figure this out, any help would be appreciated

 

[cabbey77]

Could you use continuous forms on the subform rather than a combo box?

 

[elfudge35]

I'm not sure what you mean or if that answers what I need

This is a sample of what tblTransaction could look like

TrID TrDet EnID PoID En#
1 NY 1 1 000
2 NJ 2 1 001
3 VA 3 2 000
4 NY 3 2 000
5 MI 3 2 000
6 GA 3 2 000
7 FL 6 3 000
8 NC 7 3 001
9 SC 7 3 001

The Endorsement# field is not a part of tblTransaction, it needs to be looked up from tblEndorsement, but if you look at Policy 2, the EndorsementID and Endorsement# are wrong, not all of the transactions come from the same endorsement, I need the combo box so somebody can go through the policies and pick the right Endorsement# to correct the database and thus update the EndorsementID

 

[elfudge35]

I have this as the SQL Statement for the Combo box and it accomplishes all that I need except for limiting the combo box choices to the available Endorsements for the current policy

SELECT dbo_Endorsement.Endorsement_ID, dbo_Endorsement.Policy_ID, dbo_Endorsement.Endorsement
FROM dbo_InsuredTransaction INNER JOIN dbo_Endorsement ON dbo_InsuredTransaction.Policy_ID = dbo_Endorsement.Policy_ID
GROUP BY dbo_Endorsement.Endorsement_ID, dbo_Endorsement.Policy_ID, dbo_Endorsement.Endorsement
ORDER BY dbo_Endorsement.Policy_ID, dbo_Endorsement.Endorsement;

I could just go with this, but I'm afraid of a user not realizing they are picking the endorsement number for a different policy and screwing everything up

 

[cabbey77]

Ahh, I understand better. What you need is one combo box that limits the fields in a second combo box that actually filters the form.

There is lots of information on this board describing just that issue.

Try a search for "one combo limits another", that should get you pointed in the right direction!

 

[cabbey77]

Try this

SELECT dbo_Endorsement.Endorsement_ID, dbo_Endorsement.Policy_ID, dbo_Endorsement.Endorsement
FROM dbo_InsuredTransaction INNER JOIN dbo_Endorsement ON dbo_InsuredTransaction.Policy_ID = dbo_Endorsement.Policy_ID
GROUP BY dbo_Endorsement.Endorsement_ID, dbo_Endorsement.Policy_ID, dbo_Endorsement.Endorsement
ORDER BY dbo_Endorsement.Policy_ID, dbo_Endorsement.Endorsement;

Change your "inner join" to a left join.

 

[elfudge35]

Left join doesn't work, I still get every single Endorsement#, not just the End#'s for the current policy, but I'll look into adding a second combo box, was hoping I didn't have to

 

[elfudge35]

I would think changing my SQL statement to this would work, but it can't find the field in my Form for some reason

SELECT dbo_Endorsement.Endorsement_ID, dbo_Endorsement.Policy_ID, dbo_Endorsement.Endorsement
FROM dbo_InsuredTransaction LEFT JOIN dbo_Endorsement ON dbo_InsuredTransaction.Policy_ID = dbo_Endorsement.Policy_ID
GROUP BY dbo_Endorsement.Endorsement_ID, dbo_Endorsement.Policy_ID, dbo_Endorsement.Endorsement
HAVING (((dbo_Endorsement.Policy_ID)=[Form]![InsTranEndQA_subform]![Policy_IDb]))
ORDER BY dbo_Endorsement.Policy_ID, dbo_Endorsement.Endorsement;

 

[cabbey77]

Remou (TechnicalUser) 6 Aug 06 17:01
Have you seen the FAQs? For example:
Combo Box - dependant on another Combo Box
FAQ702-4289 "

See the above FAQ....

 

[PHV]

And have a look here:

http://support.microsoft.com/?kbid=209099

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886