I'm just playing at the moment with getting things right on a new db so don't expect it to look good. I've attached it (I hope) and would appreciate pointers.
Basically, a volunteer can have zero or many clients. A client can have zero or one volunteer. The combinations of volunteer to client change over time.
What I have so far does roughly what I want.
However, opening frmVolunteers, the clients who work for this volunteer is displayed in the subform. OK fine. However, rather than type in the Client's name etc each time (producing duplicates probably), I want to select from Clients already on the database - a kind of a combo box lookup in the subform. I've tried making a client form with a combo box search and then using this as the subform to frmVolunteers but it doesn't work - get cannot edit an Autonumber etc.
Or have I gone about this completely the wrong way?
For true data normalization, you should divorce any volunteer information from the client table. If you make a seperate table (maybe called tblMatches), include the two fields VolID and ClientID, and set your form to use that table, your life will be made considerably easier. You can get as detailed as you want in this third table: date matched, date disengaged, qty of sessions, average length of sessions, total session hours, ad amnauseum. If your form uses that table, you should be home free.
You can also make that table a main table, including only the client and volunteer info, and a Primary Key. Then add a fourth table including only the PK and put all the details there. There are many ways to skin the cat.
Oh, yes, don't forget to make a relationship between the client table and the new table, and between the volunteer table and the new table. As I started off saying, copy the volid from the client table and put it in the new table, together with the clientid. Then delete the volid field from the client table altogether.
Just wanted to let you know that I figured out how to do what you suggested Sam - I guess it should have been obvious to me in the first place, but needed someone elses input. The trouble I think with the way I had originally set it up is that the subform combo box could ONLY use an autonumber field which of course wouldn't work.
Took quite a bit of fiddling about, but now have it all working and does exactly what I want it to do now.