Forms dealing with many-to-many junction table

[SimonD]

Click the button and go to the Charge form which shows 1 record from the Charge table. It has a subform on it that shows all records in tblJunction related to that charge. In that subform you can add/edit records to tblJunction.

"all records in the tblJunction related to that charge" is empty when the user is filling this for the first time, and all that tbljunction has is a bunch a number that the user will not understand (I do since they represent the chargeID and the memberID, but the user never sees those.

 

[plog]

Correct on all accounts. In a form though the user doesn't need to know numbers. You use drop downs to help them choose data.

Let's say they've already added members to tblMember for example: ID=17; Name=John Smith. On the tblJunction subform they will have a drop down to select the member. They will see 'John Smith', but the data that gets put into tblJunction is 17. Drop downs can show one thing and use another.

 

[SimonD]

Correct on all accounts. In a form though the user doesn't need to know numbers. You use drop downs to help them choose data.

Let's say they've already added members to tblMember for example: ID=17; Name=John Smith. On the tblJunction subform they will have a drop down to select the member. They will see 'John Smith', but the data that gets put into tblJunction is 17. Drop downs can show one thing and use another.

Now I will try to do this! thanks for the guidance so far!!!

 

[SimonD]

Click the button and go to the Charge form which shows 1 record from the Charge table.

I know this is a basic question but: How do you get the button to open on the active record?
(I'm searching on how to do this, now)

It has a subform on it that shows all records in tblJunction related to that charge. In that subform you can add/edit records to tblJunction.

the Junction subform will show (if any) the records of the active charge to a member and have the option to add more association with other members. Now I have to figure out how to add a combobox to the junction subform so the user can pick from a list of members and not from a list of memberID!

 

[SimonD]

OMG if I had created a button through the wizard, it shows there!

I usually create button from scratch

 

[SimonD]

Let's say they've already added members to tblMember for example: ID=17; Name=John Smith. On the tblJunction subform they will have a drop down to select the member. They will see 'John Smith', but the data that gets put into tblJunction is 17. Drop downs can show one thing and use another.

I found an article that shows how to do this, I'm trying to duplicate the results!

 

[SimonD]

Your idea works brilliantly!!! either with records already there or to associate new charges to member(s)!

Thank you so much! Now that I understand this, I'm trying to make this more user friendly. in most cases (pun intended) there is only one member per case (joint trial are fairly rare, 1 every two years, maybe).

But since I have (most of the time) more than one charge per case, it would be easier if I could do the reverse, from a member, associate the charges. It would be easier to input rather than for each charge assigning the member. The most charge in one case in the past years was 56. Imagine, after entering the 56 charges, now you have to assign it 56 times by opening the subform, picking the member, saving and returning to the form, changing the charge, opening the subform, assigning to member, saving and closing the subform, re opening the form with the next charge... so on and so forth.

I would rather have one member on the screen, and selecting the charges that belongs to him/her, like some sort of check mark.

I'll try to think of a way to do it, with lots of trial and error! I'm sure :banghead: