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.