Updating subform based on combo box change

[kev921hs]

Help!



I have the following tables: COMPANY and PROSPECT. I've set up a one-to-many relationship between them. I have a COMPANY_VIEW form and on it a bunch of subforms - one of which is PROSPECT_VIEW. A PROSPECT has about 50 fields. I have a Combo Box that is bound to PROSPECT_NAME, and populated by a query that works. The other 50 fields populate when you first load; it loads the first PROSPECT by default.

Here's what I want: when you select a different PROSPECT_NAME in the Combo Box, it should refresh all of the other fields from the PROSPECT with that PROSPECT_NAME.

I've had the PROSPECT_NAME combo box on the PROSPECT_VIEW subform, but tried moving it to the COMPANY_VIEW form and it still didn't work.

I'm guessing I need to do something with the Combo Box's AfterChange event but every "requery" solution I found online and tried did nothing. I think that's because it's re-running the original query, which just populates the Combo Box and selects the first PROSPECT by default. But I don't know how to force it to select a different PROSPECT. Any thoughts?

Thanks in advance,
Kevin

[maximus]

I have two tables
1) Main: {FraudRef AutoNumber PK, Details, de, da, dc}
2) Salve: {Link_id Number FK, de, det, deta}

Tables are linke one to manny that is:

One FraudRef ID may have many PolicyNumber

Data entered:
Main:
FraudRef: 1,2,3
Details: Maximus, Kevin, RuralGuy

Slave:
Likid=1 has following policyNumber 123,456,789
Linkid=2 has following policyNumber1011,1213,1314
Linkid=3 has following policyNumber1516,1718,1920


Now I have a Mainform RecordSource Main and a Subform to the Main Slave RecordSource Slave.

One combo Box is provided in the Subform. This will update all the entries in the subform.

e.g. if you Select Policy Number 123 and the Fraudref is 1 in the main form
Result 1 Record
e.g.if you Select Policy Number 1516 and the Fraudref is 1 in the main form
Result 0 records as this Number has a Fraudref link as 3 not 1
e.g. if you Click Clear Button Fraudref is 1 in the main form
Result 3 Records all policy number belonging to FraudRef=1

No The Combo Box on the main Form:

This updates both Subfrom and the Main form on the basis of policy Numbers.

Select 1011 in the Combobox
FraudRef becomes 2
Subform Shows three records 1011,1213,1415
Clicking Clear restores the form to the state when it is first opened.

Attaching a sample mdb for your reference. If this solves your problem mark the thread solved.

[kev921hs]

Maximus -

That sample DB was a great help. One of my problems was most likely that I had the ComboBox bound; I mimicked your setup, unbounded it and populated it with a query instead. I added the code you had, and it works perfectly.

Thanks so much!!

[maximus]

glad to help