Results 1 to 7 of 7

Thread: linked table help

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Posts
    114

    linked table help

    I have a table with student info with studentID field and I have another table with grades with studentID field linked to student table. How would I get the data with student's name show when I load grades from the table. MidYear is the name table with grades in it. Do I use INNER JOIN and call student ID?


    VB Code:
    1. Set rs = New ADODB.Recordset 'as we did with the connection
    2.         rs.Open " MidYear ", cn, adOpenKeyset, adLockOptimistic, adCmdTable

  2. #2
    Hyperactive Member
    Join Date
    Feb 2006
    Location
    Philippines
    Posts
    468

    Re: linked table help

    Set rs = New ADODB.Recordset
    rs.Open " select student_table.*, midyear.* from student_table inner join midyear on student_table.studentid=midyear.studentid", cn, adOpenstatic, adLockOptimistic, adCmdText

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Posts
    114

    Re: linked table help

    thanks that worked. I know the thread should be marked as resolved and I will do that but I have one more question. How would I get a new record created for MidYear table whenever I add a record in Student_table.


    example

    (When I create a new student it generates ID in studenttable and I want a blank record created in MidYear table that matches the new studentID)

  4. #4
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: linked table help

    Do both INSERTS together (assuming you are using code to create records).

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Posts
    114

    Re: linked table help

    how would I get it to delete both records if student is deleted?

    If student 1 is student_table is deleted student 1 records in midyear_table are also deleted

  6. #6
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    Re: linked table help

    Quote Originally Posted by graphixphantix
    how would I get it to delete both records if student is deleted?

    If student 1 is student_table is deleted student 1 records in midyear_table are also deleted
    What is the one thing they both have in common?

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Feb 2006
    Posts
    114

    Re: linked table help

    This is what I wrote but it's not working right


    VB Code:
    1. If MsgBox("Are you sure you want to delete this record?", vbYesNo + vbQuestion, "Delete?") = vbNo Then      'check if you really want to delete this record
    2.      Exit Sub 'exit the command
    3.      Else
    4.         If Not (rs.BOF = True Or rs.EOF = True ) Then
    5.                  'delete the current record
    6.                 rs1.Delete: rs.Delete
    7.             If Not (rs.BOF = True Or rs.EOF = True) Then
    8.                  rs.MoveNext 'move next
    9.                 If rs.EOF Then rs.MoveLast

Posting Permissions

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



Click Here to Expand Forum to Full Width