Insert Into 2 tables from a form & subform

[al416 via AccessMonster.com]

Any help would be appreciated:

I'm trying to add lines to two different tables in two steps. One line to the
first table and multiple lines to the second. The number of lines equals the
same quantity of lines showing in the subform. Step one works fine (first
INSERT INTO stement).

The second doesn't work:
1. The X = ... statement fails. Syntax error?
2. The INSERT INTO statement comes back with key violation errors.
The three varibles in the second statement are all primary keys for that
table. They are the only primary keys for that table. I'm trying to add the
same number of questions to the table as exists in the open subform
[sfrmAuditQuestionResults].

Example: If there are 5 questions displayed on the form, I want to add
questions 1 through 5 to the table



Private Sub cmdAuditComplete_Click()
Dim mySQL As String
Dim X As Integer
Dim N As Integer
Dim NewAudit As Integer


'Add the next Audit at the next Due Date (NewDate = DueDate+Frequency)
mySQL = "INSERT INTO tblAuditsScheduled([AuditID],[DueDate])VALUES(forms!
[frmScheduledauditResults]![AuditID],forms![frmScheduledauditResults]!
[NewDate])"
DoCmd.RunSQL mySQL

'Add corresponding Audit questions to newly schedule audit
X = Forms![sfrmAuditQuestionResults]!Max([QuestionNumber]).Value

NewAudit = Forms![frmScheduledauditResults]![AuditNumber] + 1

For N = 1 To X
mySQL = "INSERT INTO tblAuditQuestionResults([AuditID],[AuditNumber],
[QuestionNumber])VALUES(forms![frmScheduledauditResults]![AuditID],NewAudit,
N)"
DoCmd.RunSQL mySQL
Next N


End Sub

 

[Jeanette Cunningham]

Hi al416,
Using insert into, there are 2 different methods to use:
1. insert into MyTable ( PK, F1, F2 ) Values (1, "ant", "JohnDoe" )
This type of insert statement will run OK for 1 record only.


2. if you wish to insert more than one record at the same time, you need to
use a slightly different statement.
Instead of the Values bit from 1., you need to use a query.
Check out help on insert into - it is explained there.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia