Populating a Subform by Combo Box selection on the Form. And the Form is broken.

[IncidentalProgrammer]

So I've gotten myself into something else fun to try and figure out.



In the database that I'm working on, one of the key features is to be able to look up insurance policies, and get a pretty complete look at everything related to them. I've used a set of three subforms to display the Account, Insurance Company, and Producer/Agent data. It worked perfectly. Then the team told me there can be multiple Producers and Accounts per policy, and we need to see them all. Then I broke my form, trying to do it.

So here's what I'm trying to do now. I've added two unbound fields over the subforms I need to make searchable: one is a text box to display the number of such entities attached to the policy, and the other is a combo box to select which entity's data the user wishes to view in the subform. I need the subform to stay as-is, and just the information to change to the applicable entity's depending on the user's selection from the combo box.

The first problem I've run into with it, is that the combo box is not displaying only the entities related to this policy. I've got the Producer one in place, and it just takes in every dummy Producer I made. I thought setting it up based on the policy number would work, but it's not. I haven't used any queries yet, but is this a place where I need too?

I think I can code the entity counter boxes, but I can't get the combo box to control the data in the subform. I've got code on another form that controls the subform used based on a combo box selection, but I just need the data to change, in this one. I think part of the problem getting this to work may stem from another problem I'm having, caused by the original setup of this form. When I was under the impression it was only going to be one per policy, I controled the data in the subforms by placing invisible text boxes on the form that were linked to the appropriate Entity IDs; the boxes took the Entity IDs that were listed on the Policy record, and linked them to the Entity IDs of the 3 different Entities to populate the combo boxes. When I found that I would need multiples per policy, I did away with those fields in the Form and Table, and even went so far as to replace the subform. However, whenever I open the policy form now, I get an "Enter Parameter Value" popup that's asking for the field that used to populate the Producer subform, and mentioning the Policy table where it used to be. I've checked, and it's no longer there. I even ran the analyzer and got a 105 page report that it didn't show up on, but now I have to enter one of the Producer's Entity IDs just to open the Policy form. From there, whenever I try to flip through the combo box and select another entity, the subform just sits on the one I entered to get into the page.

Does anyone have experience with these issues, and know how to beat them into submission? Because I'm totally stuck.

[ranman256]

1. when you have a working form, then want to change it completely, always SAVE AS (frmMyform v1 Working)
THEN modify it for v2.

2.
You may want to make your sub forms, as datasheets. So you can easily see the multi records.
Make sure all subforms are linked via CHILD REFERENCE FIELD, and MASTER FIELD.
So all subform records relate to the MASTER record.

[IncidentalProgrammer]

Rookie mistake; lesson learned. Fortunately, I just tried on a whim to recreate the form by copying and pasting the fields onto another form. Holy crap, it worked. No more popup.

That's what I was looking for! Now it works.

The only thing left is not being able to get the unbound field to only pick Producers that are affiliated with the subject Policy. I have a policy number field in the producer table it draws from, but it's still just taking all the producers from the table. Am I going to need to use a query for this, or set up the control a little different? Or code?

[IncidentalProgrammer]

And I'm using datasheet subforms for some of the other items on this form, like the coverages and changes, but these particular items are only going to average between one and three entries per policy, and the Producer subform has a LOT of data in it, so I really prefer the regular view for them.

[ranman256]

When the policy changes, you need to update the cboProducers, so it can filter on that 1 policy.

policy_afterupdate()
cboProducers.requery
end sub

the cboProducers query looks at the policy so it can filter only those.

[IncidentalProgrammer]

I must be going wrong somewhere. Still pretty new to programming, so let me make sure I have this straight.

"policy" is the policy number field, and "cboProducers" is the unbound combo box to select the producer data to display, right?

Do I need to build a query first?