Unanswered: Refresh main form filter on sub form combos?
I have a main form that cycles records Deals. You can enter information about the deal.
I have a singers subform and a recorders sub form so you can add 1 or more singers and recorders to a deal. Then on this 3rd subform you are assigning singers and/or recorders to contract representation. I only want to assign representation to a singer or recorder for a deal if they are in this deal.
On this 3rd subform you chose from a combo (FKPartyType) if the party type you will assign representation for, is a singer or recorder. If a singer, then the singer combo (cboSinger) becomes visible with names of singers. If a recorder is chosen, then a different combo (cboRecorder) with the names of the recorders becomes available. Both of these combos are tied to the same record source of cboParty in their table for capturing the entered info. Depending on the FKPartyType field, the value in the cboParty field is pulling the id for a singer or recorder.
I have all that working right (believe it or not). My problem now is that when I open the main Deal form, and cycle to a 2nd deal, the Singers/recorders combos are not filtering to the singers and recorders that are available for that deal. They filter based on the first deal, but don't update to the 2nd. Each singer or recorder can be on multiple deals. So there might be 5 singers in my singers table. 1,2 and 3 might be in deal 1, and 1,3 and 5 might be available in deal 2. When I open the main form to deal 1, I get 1,2 and 3. When I cycle to the 2nd deal, the subform still gives me singers 1,2 and 3.
I'm sorry if I am not explaining this well. I feel like I am but I might not be.
My rowsource for cboSinger and cboRecorder both have a where for the FKDeal in their lookup table to filter to the deal FK on the subform.
Thanks if you can make sense of this and help me out. I have this in the load of the Deal main form and the after update:
Thanks so much for all of your advice. I have been reading some of these threads and they have been very useful to me. However I do have a question about how to requery the main form and maintain the same page.
This is probably a really quick fix but for the life of me my mind is a blank and I haven't a clue as to how how to proceed. So if you could help me out I would really appreciate it.
I have a form called Orders. And in it is a subform, Container_Assignments. When an action is performed in Container_Assignments, then I have used the following action in AfterUpdate:
This works fine if I am on the first record as it refreshes the Form to that first record. However when I am on other form records and it requeries then I am sent back to the main form.
I'm sorry I am sure this is pretty basic but if someone could explain what I need to do I would really appreciate it. Thanks so much.
I just don't know if that's the answer. I think I need to requery the main form because you see the main for draws calculations from the subform's textfields to come to a total calculation of the order's fees.
The purpose of the container_assignments subform is to allow the user to add in information as to whether a container's work has been completed. When it is completed the information gets calculated in the subform. And then I take the total and pull that into the form.
So if there's a way to bookmark the main form, Orders, so that I do not refresh back to the Order's first record then I think I will have my answer. Sorry in advance. I bet this is probably such a novice question. Thanks,