form/subform link master/child

[msarnie]

I have a form (Frm Trade Tickets) and a subform (Frm Trade Tickets Subform Detail) that has a link child (PTS) and master (User ID) relationship which I set as an event procedure when the form is open

Me.Frm_Trade_Tickets_Subform_Detail.LinkChildFields = "PTS"
Me.Frm_Trade_Tickets_Subform_Detail.LinkMasterFields = "User ID"

I am struggling to figure out how to do the following:

In the subform I have a button that I want to be able to when clicked and the text on the button says "ALL" that the child / master relationship would be empty and when the text on the button says "Filter" that the child / master relationship would be populated.

'Execute condition if Button Caption = All

If Me.Change_Coverage_Command_Button.Caption = "All" Then
Me.Change_Coverage_Command_Button.Caption = "Filter"

?????????? LinkChildFields = " "
?????????? LinkMasterFields = " "

'Else Button Caption = Filter

Else
Me.Change_Coverage_Command_Button.Caption = "All"

?????????? LinkChildFields = "PTS"
?????????? LinkMasterFields = "User ID"

End If

I appreciate any help. - thanks

 

[SOS]

1. You should not be changing the master/child relationships like this. If the records are related they would be related in a certain way, not based on changeable criteria.

2. You can filter records, but the question comes back to what are you actually doing with the main form and the subform. Are you using a subform as it is intended (display the many side of a one-to-many or many-to-many relationship) or are you trying to use it as a listbox would be used? Your usage is not clear based on your post.

 

[msarnie]

The subform list account responsibility based on user id.
The problem is that the subform listing needs to be changable for coverage. So, I need to be able to limit the subform to show only those accounts that are assigned to the user id and also allow the user to pick additional accounts for coverage. I wanted to do this within the subform.

So I was going to create a button within the subform to switch between showing only those accounts assigned to the user and showing all accounts. Once the user picks the accounts they are covering and click the button to switch back - the original plus the accounts they selected for coverage will only show.

I created a holding table (Tbl tmp Account responsibility) that is the record source for the subform. The holding table is a copy of the original table (Tbl Account Responsibility) which is updated when the form is opened. This allows the user to update their account responsibility without changing the original table.

 

[SOS]

I think you really should not be using a subform for this, but a listbox. Your main form should drive what is in the subform, not the other way around.