Something wrong with my code.

[Guest]

I recently asked a question about deleting records from one table and moving
them to a different one. I was given a prompt response which seemed to
answer my question. However, I get the following error whenever I try to run
the code:

"Too few parameters, expected 2"

Here is the code:

Public Function AddDelete(stTable As String, stDeleting As String, stform As
String)

Dim strSQLAdd As String
Dim strSQLDel As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim ws As DAO.Workspace
Dim bInTrans As Boolean

bInTrans = False

'I imagine my problem may be in the code below, as I'm not
'all that proficient in writing SQL.

strSQLAdd = "INSERT INTO [DEL" & stTable & "] SELECT * FROM [tbl" & stTable
& "] WHERE [tbl" & stTable & "].[" & stDeleting & "ID]=[Forms]![" & stform &
"].[txt" & stDeleting & "ID];"

strSQLDel = "DELETE * FROM [tbl" & stTable & "] WHERE [tbl" & stTable &
"].[" & stDeleting & "ID]=[Forms]![" & stform & "].[txt" & stDeleting & "ID];"

' for safety's sake create a Transaction to run both queries
' This will ensure that they either both run, or neither runs

Set ws = DBEngine(0) ' current workspace
Set db = CurrentDb
On Error GoTo Proc_Error
ws.BeginTrans
bInTrans = True ' for error trapping; you're now in a Transaction
Set qd = db.CreateQuerydef("", strSQLAdd) ' new nameless query
qd.Execute dbFailOnError ' run it
Set qd = db.CreateQuerydef("", strSQLDel)
qd.Execute dbFailOnError
' if no error occurs commit the transaction to finalize
ws.CommitTrans
Proc_Exit: Exit Function
Proc_Error:
MsgBox Err.Description
If bInTrans Then ' are we in a transaction?
ws.Rollback ' roll it back, i.e. cancel any pending changes
End If
Resume Proc_Exit

End Function

 

[Bruce M. Thompson]

I recently asked a question about deleting records from one table and moving
them to a different one. I was given a prompt response which seemed to
answer my question. However, I get the following error whenever I try to run
the code:

"Too few parameters, expected 2"

Here is the code:

Public Function AddDelete(stTable As String, stDeleting As String, stform As
String)

Dim strSQLAdd As String
Dim strSQLDel As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim ws As DAO.Workspace
Dim bInTrans As Boolean

bInTrans = False

'I imagine my problem may be in the code below, as I'm not
'all that proficient in writing SQL.

strSQLAdd = "INSERT INTO [DEL" & stTable & "] SELECT * FROM [tbl" & stTable
& "] WHERE [tbl" & stTable & "].[" & stDeleting & "ID]=[Forms]![" & stform &
"].[txt" & stDeleting & "ID];"

strSQLDel = "DELETE * FROM [tbl" & stTable & "] WHERE [tbl" & stTable &
"].[" & stDeleting & "ID]=[Forms]![" & stform & "].[txt" & stDeleting & "ID];"

' for safety's sake create a Transaction to run both queries
' This will ensure that they either both run, or neither runs

Set ws = DBEngine(0) ' current workspace
Set db = CurrentDb
On Error GoTo Proc_Error
ws.BeginTrans
bInTrans = True ' for error trapping; you're now in a Transaction
Set qd = db.CreateQuerydef("", strSQLAdd) ' new nameless query
qd.Execute dbFailOnError ' run it
Set qd = db.CreateQuerydef("", strSQLDel)
qd.Execute dbFailOnError
' if no error occurs commit the transaction to finalize
ws.CommitTrans
Proc_Exit: Exit Function
Proc_Error:
MsgBox Err.Description
If bInTrans Then ' are we in a transaction?
ws.Rollback ' roll it back, i.e. cancel any pending changes
End If
Resume Proc_Exit

End Function

I suspect the source of your trouble is that you are passing a reference to a
form object in you SQL statement instead of the actual value contained in that
form object. SQL can't resolve this as it doesn't understand "forms", so instead
of passing:

[Forms]![" & stform & "].[txt" & stDeleting & "ID];"

....you will need to, instead, pass the *value* in that reference instead
(calculate and assign the value to a variable and use that variable to pass the
value to the SQL statement. For further information and some examples, see the
following page at The Access Web:

Queries: Too few parameters, expected n

http://www.mvps.org/access/queries/qry0013.htm