Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2004
    Posts
    33

    Unanswered: Sorry but a beginner question about related tables

    Hi,

    I am making a database of several tables:

    Student
    Consultant
    Program

    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 :/

  2. #2
    Join Date
    Nov 2003
    Posts
    267
    Three Tables

    Program: ProgramID (PK)
    Consultant: ConsultantID(PK)
    Student: StudentID(PK), Consultant (FK), ProgramID(FK)


    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).

    S-

  3. #3
    Join Date
    Feb 2004
    Posts
    33

    ok

    mmm, thanks

    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 )
    Last edited by northbrit; 02-09-04 at 22:17.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    2 ways

    1.
    me.Phone = dlookup("[Phone]","[Tablename]","[ConsultantID] = 1")


    me.Phone = dlookup("[Phone]","[Tablename]","[ConsultantID] = " & me.feildname )

    Or

    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


    StePhan McKillen
    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.

  5. #5
    Join Date
    Feb 2004
    Posts
    33

    um

    me.Phone = dlookup("[Phone]","[Tablename]","[ConsultantID] = 1")


    me.Phone = dlookup("[Phone]","[Tablename]","[ConsultantID] = " & me.feildname )
    er, sorry to be dumb, but where do these go :/

  6. #6
    Join Date
    Feb 2004
    Posts
    33

    Re: um

    bump :/

  7. #7
    Join Date
    Feb 2004
    Posts
    33

    Re: um

    bumpity bump.....

  8. #8
    Join Date
    Feb 2004
    Posts
    33

    Re: um

    anyone can help me on this last bit?

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: um

    Originally posted by northbrit
    anyone can help me on this last bit?
    Try in the combobox's AfterUpdate event ...

  10. #10
    Join Date
    Feb 2004
    Posts
    33

    Re: um

    me.Phone = dlookup("[Phone]","[Tablename]","[ConsultantID] = 1")


    me.Phone = dlookup("[Phone]","[Tablename]","[ConsultantID] = " & me.feildname )
    which do i put in, the first or last or both?

  11. #11
    Join Date
    Feb 2004
    Posts
    33
    When i put in both it says you have an operand without an operator....

Posting Permissions

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