I started this thread in FormsProgramming, but got no response.

Maybe somebody in VBA can help me out
Thanks.

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

--
Message posted via http://www.accessmonster.com

When you run a query, Jet has no idea about VBA variables. You need to put
the value in the string, not the name of the variable:

mySQL = "INSERT INTO tblAuditQuestionResults([AuditID],[AuditNumber],
[QuestionNumber])VALUES(forms![frmScheduledauditResults]![AuditID]," &
NewAudit & "," & N& ")"


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"al416 via AccessMonster.com" <u48770@uwe> wrote in message
news:9eea4d0d7e682@uwe...
>I started this thread in FormsProgramming, but got no response.
>
> Maybe somebody in VBA can help me out
> Thanks.
>
> 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
>
> --
> Message posted via http://www.accessmonster.com
>