By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,154 Members | 2,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,154 IT Pros & Developers. It's quick & easy.

Too few parameters error using Execute dbFailOnError

P: n/a
Hi there,
I am trying to insert 4 fields into a table grabbing the 4 fields from a form,
here is my code:
Private Sub LastPaymentAmount_AfterUpdate()

Dim CurrentProject As DAO.Database
Set CurrentProject = CurrentDB()

'CurrentProject.Execute "Insert into TransactionHistory values(FirstName,
LastName, LastPaymentDate, LastPaymentAmount)", dbFailOnError

End Sub

and I keep getting a too few parameters error on the Execute statement.
Also, I just pasted my code in, the Execute statement is actually all on one
line,
Thanks in advance for your help!
GM

Sep 3 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Per gmazza:
>'CurrentProject.Execute "Insert into TransactionHistory values(FirstName,
LastName, LastPaymentDate, LastPaymentAmount)", dbFailOnError
This is strictly a knee-jerk reaction... but it seems like the
query needs to know both the value tb inserted and the name of
the field the value goes into.

When I do something like that, it's more in the form of:

------------------------------------------------------------------
Private Sub cmdAdd_Click()
1000 debugStackPush Me.Name & ": cmdAdd_Click"
1001 On Error GoTo cmdAdd_Click_err

' PURPOSE: To add a new record to the table in question after
computing
' the next available CollateralManagerID
'
' NOTES: 1) FormOpen routine sets gModalAddDialogOutcome
semaphore=FALSE,
' we set it to TRUE to tell calling routine that
the add succeeded.

1002 Dim myQuery As DAO.QueryDef

Dim myUserID As String
Dim myNewRecordID As Long

1010 myUserID = CurrentUserGet()
1019 myNewRecordID = IdGetNext_CollateralManager()

1020 Set myQuery =
CurrentDb.QueryDefs("qryAddRec_CollateralManager")
1030 With myQuery
1031 .Parameters("theRecordID") = myNewRecordID
1032 .Parameters("theName") = Me.txtName
1033 .Parameters("theStreetAddress") = Me.txtStreetAddress
1034 .Parameters("theCity") = Me.txtCity
1035 .Parameters("theState") = Me.cboState
1039 .Parameters("theZip") = Me.txtZip
1040 .Parameters("theCountry") = Me.cboCountry
1041 .Parameters("theComments") = Me.txtComments
1042 .Parameters("theUserID") = myUserID
1043 .Parameters("theTimeStamp") = Now()
1044 .Execute dbFailOnError
1049 End With

1990 gModalDialogOutcome = True
1991 Me.Visible = False
1992 MsgBox "'" & Me.txtName & "' Added", 0, "Done!"
1999 DoCmd.Close acForm, Me.Name

cmdAdd_Click_xit:
DebugStackPop
On Error Resume Next
Set myQuery = Nothing
Exit Sub

cmdAdd_Click_err:
If Err = gError_DuplicateValue Then
MsgBox "That name already exists", 48, "Cannot Add Duplicate"
Else
BugAlert True, ""
End If
Resume cmdAdd_Click_xit
End Sub
------------------------------------------------------------------

Where qryAddRec_CollateralManager looks like this:
------------------------------------------------------------------
PARAMETERS
theName Text ( 255 ),
theStreetAddress Text ( 255 ),
theCity Text ( 255 ),
theState Text ( 255 ),
theZip Text ( 255 ),
theCountry Text ( 255 ),
theComments Text ( 255 ),
theUserID Text ( 255 ),
theTimeStamp DateTime,
theRecordID Long;

INSERT INTO tlkpCollateralManager
(
CollateralManagerID,
CollateralManagerName,
StreetAddress, City,
State,
Zip,
Country,
Comments,
CreatedBy,
CreatedAt
)

SELECT
[theRecordID] AS Expr10,
[theName] AS Expr9,
[theStreetAddress] AS Expr1,
[theCity] AS Expr2,
[theState] AS Expr3,
[theZip] AS Expr4,
[theCountry] AS Expr5,
[theComments] AS Expr6,
[theUserID] AS Expr7,
[theTimestamp] AS Expr8;
------------------------------------------------------------------
--
PeteCresswell
Sep 3 '07 #2

P: n/a
read your SQL statement. it names the fields for values to be inserted
into....but where are the values? you need to either write in hard-coded
values, or references that will return values, as

CurrentProject.Execute "INSERT INTO TransactionHistory " _
& "( FirstName, LastName, LastPaymentDate, " _
& "LastPaymentAmount ) SELECT '" & Me!ControlName _
& "', '" & Me!NextControlName & "', #" _
& Me!NextControlName & "#, " & Me!LastControlName, _
dbFailOnError

one way to get the correct syntax for SQL statements (for those of us who
aren't SQL whizzes), is to create the query in query Design view first, then
open the SQL View pane and copy/paste the statement into VBA - then break
out the statement into concatenated strings.

hth
"gmazza" <u37142@uwewrote in message news:77af4ff7945cb@uwe...
Hi there,
I am trying to insert 4 fields into a table grabbing the 4 fields from a
form,
here is my code:
Private Sub LastPaymentAmount_AfterUpdate()

Dim CurrentProject As DAO.Database
Set CurrentProject = CurrentDB()

'CurrentProject.Execute "Insert into TransactionHistory values(FirstName,
LastName, LastPaymentDate, LastPaymentAmount)", dbFailOnError

End Sub

and I keep getting a too few parameters error on the Execute statement.
Also, I just pasted my code in, the Execute statement is actually all on
one
line,
Thanks in advance for your help!
GM

Sep 3 '07 #3

P: n/a
Thanks a ton Tina, worked perfect!!

tina wrote:
>read your SQL statement. it names the fields for values to be inserted
into....but where are the values? you need to either write in hard-coded
values, or references that will return values, as

CurrentProject.Execute "INSERT INTO TransactionHistory " _
& "( FirstName, LastName, LastPaymentDate, " _
& "LastPaymentAmount ) SELECT '" & Me!ControlName _
& "', '" & Me!NextControlName & "', #" _
& Me!NextControlName & "#, " & Me!LastControlName, _
dbFailOnError

one way to get the correct syntax for SQL statements (for those of us who
aren't SQL whizzes), is to create the query in query Design view first, then
open the SQL View pane and copy/paste the statement into VBA - then break
out the statement into concatenated strings.

hth
>Hi there,
I am trying to insert 4 fields into a table grabbing the 4 fields from a form,
[quoted text clipped - 14 lines]
>Thanks in advance for your help!
GM
Sep 3 '07 #4

P: n/a
you're welcome :)
"gmazza" <u37142@uwewrote in message news:77aff661a098e@uwe...
Thanks a ton Tina, worked perfect!!

tina wrote:
read your SQL statement. it names the fields for values to be inserted
into....but where are the values? you need to either write in hard-coded
values, or references that will return values, as

CurrentProject.Execute "INSERT INTO TransactionHistory " _
& "( FirstName, LastName, LastPaymentDate, " _
& "LastPaymentAmount ) SELECT '" & Me!ControlName _
& "', '" & Me!NextControlName & "', #" _
& Me!NextControlName & "#, " & Me!LastControlName, _
dbFailOnError

one way to get the correct syntax for SQL statements (for those of us who
aren't SQL whizzes), is to create the query in query Design view first,
then
open the SQL View pane and copy/paste the statement into VBA - then break
out the statement into concatenated strings.

hth
Hi there,
I am trying to insert 4 fields into a table grabbing the 4 fields from
a form,
[quoted text clipped - 14 lines]
Thanks in advance for your help!
GM

Sep 4 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.