Unanswered: Sorry but a beginner question about related tables
I am making a database of several tables:
Essentially a student comes to us from a program being referred by a consultant. So it seems obvious that I need a couple of one-many relationships. The many side being on the student....
Program (one) ------ (many) student (many) -------- (one) consultant
Ok, so after setting this up, I hit a brick wall when I try and make a form. It seemed logical to me to use a "student" form and then have "consultant" and "program" subforms. After doing this however, the form does not seem to be linking the students to a particular program and particular consultant.
The only workaround I can manage is to have a field in the form from the Student table denoting the consultant (its in both tables obviously), thne have the subform. The user browses through the records on the subform. Once they find the right consultant, they enter that ID on the form which enters that into the *student* table. If its a new consultant, they would enter in that information in the subform and copy/carry across the new ID they have assigned.
Sooo, basically, I need a more elegant way to have a 2nd table in a form, to be able to associate a specific record/consultant with that student, or if its a new consultant, be able to enter in a new record/consultant.
um, reading back, I am not sure this makes alot of sense :/
When you add a student you do have to select which consultant and program they cam from. I would use cmbo boxes, instead of subforms though, to select the Consultant and Program. My personnal preference is to add the Consultant using a different form (especially since it doesn't happen many times a day).
That's more or less what I am trying now. One thing I would like to have on the form is have another box automatically filled in with the consultants telephone number when I select which consultant it is. I am doing a lookup thingy for the ID field and it is showing the name rather than the ID. Can you do this on a form?
Note the consultant tel number is in the consultant table (er, obviously )
If the Consultant is a Combo Box put the phone number into a Column query
then in code you can call it by
me.phone = Me.CboConsultand.Column(column number in query).
hope this help
See clear as mud
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Progaming environment: Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010 VB based on my own environment: vb6 sp5 ASP based on my own environment: 5.6 VB-NET based on my own environment started 2007 SQL-2005 based on my own environment started 2008 MYLE YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.