Show only certain related records in combo box

[gschimek]

I have a feeling this is a common and probably easy question, but I couldn't find anything on it in the forums.

I have a main customer form with multiple subforms on it to keep track of my clients insurance policies and claims. I enter their contact data in the main form, and the policy data on one of the subforms. Then when they have a claim, I enter the policy information on the claim subform as well.

How could I have the combo boxes in the claims subform (which is storing data in a claims table) only show the policy numbers (stored in a policies table) assigned to that particular person? I would assume I need to somehow filter the policies query so it only shows records for that particular person, but I'm not sure how to do that on the fly.

 

[Luddite Lad]

What you need is a cascading Combo Box set.

Do a search on cascading combo boxes.

Briefly though you will need to write a query that selects the results you want to show in the second combo box based on the selection of the first combo box. You will need to requery your second box via the after update event of the first.

 

[ansentry]

If you have the following;

Main form (with customer details) -tblCustomers
subform1 (Policies Details)-tblPilicies
subform2 (Claims Details) -tblClaims

and have the keys and relationships set up correctly then the following will happen;

Main form = Customer Mr Smith
Subform1 =Will show all policies issued to Mr Smith
Subform2 = Will show claims related to EACH of the policies show in Subform1

I posted a sample of mine Here for someone else. It will show you how it works.

Hope this helps.

 

[gschimek]

OK, I think I'm trying to make this harder than it really is. I realized I don't need to have multiple cascading combo boxes for what I need. All I really need to do is have 1 combo box that only shows policy numbers for that particular customer's ID.

This code works perfectly for the row source:
SELECT qryHeldPolicies.PolicyNumber, qryHeldPolicies.CustomerID FROM qryHeldPolicies WHERE (((qryHeldPolicies.CustomerID)=12));

Where 12 is one of the customer's IDs. What I need is to change the 12 so that it reads the CustomerID of the currrent person and only show's their policy numbers.

I've got a CustomerID field on the Claims subform, where this code and combo box are going, and I also have a CustomerID field on the main form with all the other customer's data.

So I think all I need to know is what is the code to tell it to read from a text box on the current form?

Thanks!

 

[gschimek]

Anybody have any idea? I have a feeling it'll be something simple that I should have figured out.

 

[ansentry]

Did you have a look at my sample?

It will show you how is should be set up.

 

[gemma-the-husky]

you are there aren't you, already

in your combo box code, instead of 12 have this as the customer key from the main form

so not

SELECT qryHeldPolicies.PolicyNumber, qryHeldPolicies.CustomerID FROM qryHeldPolicies WHERE (((qryHeldPolicies.CustomerID)=12));

but

SELECT qryHeldPolicies.PolicyNumber, qryHeldPolicies.CustomerID FROM qryHeldPolicies WHERE (((qryHeldPolicies.CustomerID)=me.thiscustomer));

you don't need the me, just making it clear. I suspect you have this already though

but then you need attached to the on current event for the main form, or any other way you navigate thorugh the main form records a

combobox.requery.

that should do it i think