Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    47

    Unanswered: APPEND Query Problem

    I am building an attendance database which is supposed to keep track of students in the class.

    So we have 4 tables in total: students, classes, roster, attendance.

    The idea was to query the Roster and display it in a subform according to the class. That part is done. That the user has to select the student that is missing. That part is done also. Now the problem comes, that when done, the user can click on a SUBMIT button in the main form and all the records from the subform should be written to the "attendance" table

    I was planning to do it with an APPEND statement but I guess the problem is that it can't add multiple records...

    INSERT INTO tblBRIDGEAttendance ( ClassNo, StuID, Attendance, [Date] )
    VALUES (Forms.frmsubStudentRosterPerClass.ClassNo, Forms.frmsubStudentRosterPerClass.StuID, Forms.frmsubStudentRosterPerClass.chkPresent, #2/2/2007#);

    Can someone give me a hint where I am going wrong and what should I be doing?

    a.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Append queries can add multiple lines...
    I have a database somewhere that adds 40 lines based on a single criteria (if only I could find it) - I only remember it's existence because it was a pig learning how to turn the messagebox off that asked if you were sure about appending...

    However, an alternative solution maybe to use VB code purely.
    You can use a doCmd.RUNSQL("INSERT INTO...") combined with a loop to go through your records.
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    INSERT INTO ... SELECT ... FROM ...

    Does the trick also ...
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Or M Owen's suggestion (which is far cleverererer)
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by M Owen
    INSERT INTO ... SELECT ... FROM ...

    Does the trick also ...
    Or M Owen's suggestion (which is far cleverererer)
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    a.
    You didn't say why the SQL you posted would not append the data you wanted, but looking at the SQL, what you were asking for (the Values) to be appended would only give you one record. By using the SELECT clause and selecting from the table the subform uses, you will get all the records rather than only the one your current SQL is refering to. Of course, you will need a WHERE clause in the SELECT statement that matches the filtering (filtering plus Master/Child Links) used in the subform.
    HTH,

  7. #7
    Join Date
    Sep 2004
    Posts
    47

    I got it but the form but..

    Ok thanks for the input. Here is the new query:

    INSERT INTO tblBRIDGEAttendance ( StuID, ClassNo, Attendance )
    SELECT StuID, ClassNo, Attendance
    FROM tblRoster
    WHERE tblRoster.StuID=Forms.frmsubStudentRosterPerClass. StuID And tblRoster.ClassNo=Forms.frmsubStudentRosterPerClas s.ClassNo;


    The problem is when I click SUBMIT on the main form, I still receive popup messages to input values for the filters in the WHERE clauses (StuID and ClassNo). If I put them in....it appends the query, but it can't do it by itself. I wanted it to run down the list and find the checked off fields and add those. I guess I am stuck where to put frmsubStudentRosterPerClass.Attendance = True or tblRoster.Attendance = True (both are the same).

    a.

  8. #8
    Join Date
    Sep 2004
    Posts
    47

    Scratch that

    Scratch that... I've been looking at this for a long time...my fault. I fixed it.

    I just added WHERE tblRoster.Attendance = True;

    Works...

    a.

Posting Permissions

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