B
Biggles
I am trying to run the following (all these are dimmed at the beginning):
audent = RST!txtAENO
audgrp = RST!Auditgroup
yr1sc = Nz(RST!YR1SCOPE, "X")
yr2sc = Nz(RST!YR2SCOPE, "X")
yr3sc = Nz(RST!YR3SCOPE, "X")
yr4sc = Nz(RST!YR4SCOPE, "X")
tgtqtr = Nz(RST!txtTARGET_QTR, "X")
fullhrs = RST!intFULL_SCOPE_HRS
ltdhrs = RST!intLTD_SCOPE_HRS
fullit = RST!intFULL_SCOPE_IT
ltdit = RST!intLTD_SCOPE_IT
soxhrs = RST!intSOX_404_HRS
cahrs = RST!intCONTINUOUS_AUD_HRS
other = RST!intOTHER
Call INSERT_SQL
-----------------------------------
The INSERT_SQL public sub
Private Sub INSERT_SQL(audent As String, audgrp As String, yr1sc As String,
yr2sc As String, yr3sc As String, yr4sc As String, tgtqtr As String, fullhrs
As Integer, ltdhrs As Integer, fullit As Integer, ltdit As Integer, soxhrs As
Integer, cahrs As Integer, other As Integer)
SSQL1 = "INSERT INTO ttblAUDITLIST (txtAENO, AUDITGROUP,
YR1SCOPE,YR2SCOPE,YR3SCOPE,YR4SCOPE, " & _
"txtTARGET_QTR, intFULL_SCOPE_HRS, intLTD_SCOPE_HRS,
" & _
"intFULL_SCOPE_IT, intLTD_SCOPE_IT,intSOX_404_HRS, "
& _
"intSOX_404_IT, intCONTINUOUS_AUD_HRS, intOTHER) " & _
"VALUES (" & "'" & audent & "'" & "," & "'" & audgrp & "'" & ","
& _
"'" & yr1sc & "'" & "," & "'" & yr2sc & "'" & "," & _
"'" & yr3sc & "'" & "," & "'" & yr4sc & "'" & "," & "'" &
tgtqtr & "'" & "," & _
fullhrs & "," & ltdhrs & "," & fullit & "," & _
ltdit & "," & soxhrs & "," & SOXIT & "," & cahrs &
"," & other & ")"
MsgBox "THE CURRENT AUD ENT IS " & audent
MsgBox SSQL1
CurrentDb.Execute SSQL1, dbfailonerror
End Sub
When I run the command without the call, it runs fine, but when I try to
call the insert_sql I get the argument not optional. Since this could be
used in three different places, I would really like to have it be a call.
Thanks
audent = RST!txtAENO
audgrp = RST!Auditgroup
yr1sc = Nz(RST!YR1SCOPE, "X")
yr2sc = Nz(RST!YR2SCOPE, "X")
yr3sc = Nz(RST!YR3SCOPE, "X")
yr4sc = Nz(RST!YR4SCOPE, "X")
tgtqtr = Nz(RST!txtTARGET_QTR, "X")
fullhrs = RST!intFULL_SCOPE_HRS
ltdhrs = RST!intLTD_SCOPE_HRS
fullit = RST!intFULL_SCOPE_IT
ltdit = RST!intLTD_SCOPE_IT
soxhrs = RST!intSOX_404_HRS
cahrs = RST!intCONTINUOUS_AUD_HRS
other = RST!intOTHER
Call INSERT_SQL
-----------------------------------
The INSERT_SQL public sub
Private Sub INSERT_SQL(audent As String, audgrp As String, yr1sc As String,
yr2sc As String, yr3sc As String, yr4sc As String, tgtqtr As String, fullhrs
As Integer, ltdhrs As Integer, fullit As Integer, ltdit As Integer, soxhrs As
Integer, cahrs As Integer, other As Integer)
SSQL1 = "INSERT INTO ttblAUDITLIST (txtAENO, AUDITGROUP,
YR1SCOPE,YR2SCOPE,YR3SCOPE,YR4SCOPE, " & _
"txtTARGET_QTR, intFULL_SCOPE_HRS, intLTD_SCOPE_HRS,
" & _
"intFULL_SCOPE_IT, intLTD_SCOPE_IT,intSOX_404_HRS, "
& _
"intSOX_404_IT, intCONTINUOUS_AUD_HRS, intOTHER) " & _
"VALUES (" & "'" & audent & "'" & "," & "'" & audgrp & "'" & ","
& _
"'" & yr1sc & "'" & "," & "'" & yr2sc & "'" & "," & _
"'" & yr3sc & "'" & "," & "'" & yr4sc & "'" & "," & "'" &
tgtqtr & "'" & "," & _
fullhrs & "," & ltdhrs & "," & fullit & "," & _
ltdit & "," & soxhrs & "," & SOXIT & "," & cahrs &
"," & other & ")"
MsgBox "THE CURRENT AUD ENT IS " & audent
MsgBox SSQL1
CurrentDb.Execute SSQL1, dbfailonerror
End Sub
When I run the command without the call, it runs fine, but when I try to
call the insert_sql I get the argument not optional. Since this could be
used in three different places, I would really like to have it be a call.
Thanks