Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2005
    Posts
    31

    Red face Unanswered: Need a bit of help with this please

    Hello

    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?

    Any help or suggestions appreciated.
    BB
    Attached Files Attached Files

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    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.

    Sam

  3. #3
    Join Date
    Jul 2005
    Posts
    31
    Thanks for your suggestion help and time. I've had a go, but still can't get it working properly.

    Thanks anyhow.

    Regards
    BB

  4. #4
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    I took a look at the form/subform.

    It looks to me that one should just set the VolID on the clients form perhaps with a combobox.

    And with the volunteer form / client subform the subform fields might be read-only.

    But the way it is now with the form / subform one would be trying to add a new client record even if just trying to choose an existing client.

    Well that's just my quick $0.02 worth.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  5. #5
    Join Date
    Jul 2005
    Posts
    31
    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.

    Thanks for your help.
    Appreciated!

    BB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •