Argument not optional - why? what?

G

Guest

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
 
M

Marshall Barton

Biggles said:
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.
Click to expand...


The problem is that you declared the procedure with
arguments, but you did not provide values for those
arguments in the Call statement. There is no need to assign
the values to local variables. Change the calling code to
just:

Call INSERT_SQL(RST!txtAENO, RST!Auditgroup, _
Nz(RST!YR1SCOPE, "X"), Nz(RST!YR2SCOPE, "X"), _
Nz(RST!YR3SCOPE, "X"), Nz(RST!YR4SCOPE, "X"), _
Nz(RST!txtTARGET_QTR, "X"), RST!intFULL_SCOPE_HRS, _
RST!intLTD_SCOPE_HRS, RST!intFULL_SCOPE_IT, _
RST!intLTD_SCOPE_IT, RST!intSOX_404_HRS, _
RST!intCONTINUOUS_AUD_HRS, RST!intOTHER
 
G

Guest

Thanks for the answer. Now I have a formatting question.

I see in the CALL INSERT_SQL statement, you used the underscore to be able
to wrap the list. Can I do the same in the SUB INSERT_SQL? i.e will this
work?

PRIVATE SUB INSERT_SQL(AUDENT AS STRING,_
AUDGRP AS STRING,_ ETC.

Also, is there way to group the strings together, such as AUDENT, AUDGRP AS
STRING?

Thanks
 
M

Marshall Barton

Biggles said:
I see in the CALL INSERT_SQL statement, you used the underscore to be able
to wrap the list. Can I do the same in the SUB INSERT_SQL? i.e will this
work?

PRIVATE SUB INSERT_SQL(AUDENT AS STRING,_
AUDGRP AS STRING,_ ETC.
Click to expand...

Not quite like that. The line continuation is Space
Underscore Make sure you use it in a line where an
arbitrary number of spaces can be used and never inside
quotes. To continue a long quoted string, concatenate the
pieces:

strSQL = "SELECT . . . " _
& "FROM . . . " & _
"WHERE . . ."

Also, is there way to group the strings together, such as AUDENT, AUDGRP AS
STRING?
Click to expand...

No, You need to specify each variable's type with the
variable.
 
Top