Too few parameters. Expected 2

[debraj007]

Hello, and thanks for your efforts in advance . I have written an append
query that runs when you click on a button in my form (appends the current
record to a different table). The query works independently, but when I try
to run from the form, I get the error "Too few parameters. Expected 2". Here
is the code I used on the on click procedure

Private Sub cmdCopyItemInfo_Click()
On Error GoTo Err_cmdCopyItemInfo_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
CurrentDb.Execute ("query1"), dbFailOnError

Exit_cmdCopyItemInfo_Click:
Exit Sub

Err_cmdCopyItemInfo_Click:
MsgBox Err.Description
Resume Exit_cmdCopyItemInfo_Click

End Sub

Does anyone have any suggestions to help me correct this issue? Thanks!

Deb

 

[debraj007]

I forgot to post my query info, so here is the sql of the query i created

INSERT INTO tblCraft ( CraftNo, CraftInitials )
SELECT [ Forms]!frmEditItem!NewCraftNumber AS NewCraftNumber,
Forms!frmEditQuote!CraftInitials AS CraftInitials;

thanks,
Deb

 

[Douglas J. Steele]

Try

Private Sub cmdCopyItemInfo_Click()
On Error GoTo Err_cmdCopyItemInfo_Click

Dim strSQL As String

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


strSQL = "INSERT INTO tblCraft ( CraftNo, CraftInitials ) " & _
"VALUES(" & [ Forms]!frmEditItem!NewCraftNumber & ", " & _
Chr$(34) & Forms!frmEditQuote!CraftInitials & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError

Exit_cmdCopyItemInfo_Click:
Exit Sub

Err_cmdCopyItemInfo_Click:
MsgBox Err.Description
Resume Exit_cmdCopyItemInfo_Click

End Sub

strSQL

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I forgot to post my query info, so here is the sql of the query i created

INSERT INTO tblCraft ( CraftNo, CraftInitials )
SELECT [ Forms]!frmEditItem!NewCraftNumber AS NewCraftNumber,
Forms!frmEditQuote!CraftInitials AS CraftInitials;

thanks,
Deb

Hello, and thanks for your efforts in advance . I have written an
append
query that runs when you click on a button in my form (appends the
current
record to a different table). The query works independently, but when I
try
to run from the form, I get the error "Too few parameters. Expected 2".
Here
is the code I used on the on click procedure

Private Sub cmdCopyItemInfo_Click()
On Error GoTo Err_cmdCopyItemInfo_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
CurrentDb.Execute ("query1"), dbFailOnError

Exit_cmdCopyItemInfo_Click:
Exit Sub

Err_cmdCopyItemInfo_Click:
MsgBox Err.Description
Resume Exit_cmdCopyItemInfo_Click

End Sub

Does anyone have any suggestions to help me correct this issue? Thanks!

Deb

 

[Rick Brandt]

I forgot to post my query info, so here is the sql of the query i
created

INSERT INTO tblCraft ( CraftNo, CraftInitials )
SELECT [ Forms]!frmEditItem!NewCraftNumber AS NewCraftNumber,
Forms!frmEditQuote!CraftInitials AS CraftInitials;

When run a query with the Execute method you are using only DAO/Jet and do not
have the Expression Service. The Expression Service is what lets an Access
query figure out a form reference like Forms!frmEditItem!NewCraftNumber. Pure
DAO/Jet cannot figure that out.

If you used DoCmd.OpenQuery it would work because then the Expression Service is
back in the game, but I don't like that solution. With a query that short I
would just build the SQL in my code and let VBA evaluate the form expression and
build a SQL statement that contains the VALUE returned from the form reference.

Dim sql as String

sql = "INSERT INTO tblCraft ( CraftNo, CraftInitials ) " & _
"VALUES(" & Forms!frmEditItem!NewCraftNumber & ", '" & _
Forms!frmEditQuote!CraftInitials & "')"

CurrentDb.Execute sql, dbFailOnError

 

[debraj007]

That worked perfectly! Thanks so much for the prompt response.

Deb

Try

Private Sub cmdCopyItemInfo_Click()
On Error GoTo Err_cmdCopyItemInfo_Click

Dim strSQL As String

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


strSQL = "INSERT INTO tblCraft ( CraftNo, CraftInitials ) " & _
"VALUES(" & [ Forms]!frmEditItem!NewCraftNumber & ", " & _
Chr$(34) & Forms!frmEditQuote!CraftInitials & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError

Exit_cmdCopyItemInfo_Click:
Exit Sub

Err_cmdCopyItemInfo_Click:
MsgBox Err.Description
Resume Exit_cmdCopyItemInfo_Click

End Sub

strSQL

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I forgot to post my query info, so here is the sql of the query i created

INSERT INTO tblCraft ( CraftNo, CraftInitials )
SELECT [ Forms]!frmEditItem!NewCraftNumber AS NewCraftNumber,
Forms!frmEditQuote!CraftInitials AS CraftInitials;

thanks,
Deb

Hello, and thanks for your efforts in advance . I have written an
append
query that runs when you click on a button in my form (appends the
current
record to a different table). The query works independently, but when I
try
to run from the form, I get the error "Too few parameters. Expected 2".
Here
is the code I used on the on click procedure

Private Sub cmdCopyItemInfo_Click()
On Error GoTo Err_cmdCopyItemInfo_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
CurrentDb.Execute ("query1"), dbFailOnError

Exit_cmdCopyItemInfo_Click:
Exit Sub

Err_cmdCopyItemInfo_Click:
MsgBox Err.Description
Resume Exit_cmdCopyItemInfo_Click

End Sub

Does anyone have any suggestions to help me correct this issue? Thanks!

Deb

 

[debraj007]

I screwed something up . I (stupidly) decided to add a few more
paramenters to my Insert Into code and am now getting a 3134 syntax error,
any suggestions?


Private Sub cmdCopyItemInfo_Click()
On Error GoTo Err_cmdCopyItemInfo_Click

Dim strSQL As String

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Me!JobCreated = True

strSQL = "INSERT INTO tblCrafts ( [CraftNo], [CraftInitials],
[MfgCompany], [MfgAddress], [MfgCity], [MfgZip], [MfgState], [ProjectName],
[VendorAddress], [VendorCity], ) " & _
"VALUES(" & Forms!frmEditQuote!NewCraftNumber & ", '" & _
Forms!frmEditQuote!NewInitials & ", '" & _
Forms!frmEditQuote!MfgCompany & ", '" & _
Forms!frmEditQuote!MfgAddress & ", '" & _
Forms!frmEditQuote!MfgCity & ", '" & _
Forms!frmEditQuote!MfgZip & ", '" & _
Forms!frmEditQuote!MfgState & ", '" & _
Forms!frmEditQuote!ProjectName & ", '" & _
Forms!frmEditQuote!VendorAddress & ", '" & _
Forms!frmEditQuote!VendorCity & ", '" & ""

CurrentDb.Execute strSQL, dbFailOnError


Exit_cmdCopyItemInfo_Click:
Exit Sub

Err_cmdCopyItemInfo_Click:
MsgBox "Update Failed" & vbNewLine & "With Error " & Err.Number & " - "
& Err.Description
GoTo MyRoutine_Exit
Resume Exit_cmdCopyItemInfo_Click
MyRoutine_Exit:

Exit Sub

End Sub

That worked perfectly! Thanks so much for the prompt response.

Deb

Try

Private Sub cmdCopyItemInfo_Click()
On Error GoTo Err_cmdCopyItemInfo_Click

Dim strSQL As String

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


strSQL = "INSERT INTO tblCraft ( CraftNo, CraftInitials ) " & _
"VALUES(" & [ Forms]!frmEditItem!NewCraftNumber & ", " & _
Chr$(34) & Forms!frmEditQuote!CraftInitials & Chr$(34) & ")"
CurrentDb.Execute strSQL, dbFailOnError

Exit_cmdCopyItemInfo_Click:
Exit Sub

Err_cmdCopyItemInfo_Click:
MsgBox Err.Description
Resume Exit_cmdCopyItemInfo_Click

End Sub

strSQL

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I forgot to post my query info, so here is the sql of the query i created

INSERT INTO tblCraft ( CraftNo, CraftInitials )
SELECT [ Forms]!frmEditItem!NewCraftNumber AS NewCraftNumber,
Forms!frmEditQuote!CraftInitials AS CraftInitials;

thanks,
Deb



:

Hello, and thanks for your efforts in advance . I have written an
append
query that runs when you click on a button in my form (appends the
current
record to a different table). The query works independently, but when I
try
to run from the form, I get the error "Too few parameters. Expected 2".
Here
is the code I used on the on click procedure

Private Sub cmdCopyItemInfo_Click()
On Error GoTo Err_cmdCopyItemInfo_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
CurrentDb.Execute ("query1"), dbFailOnError

Exit_cmdCopyItemInfo_Click:
Exit Sub

Err_cmdCopyItemInfo_Click:
MsgBox Err.Description
Resume Exit_cmdCopyItemInfo_Click

End Sub

Does anyone have any suggestions to help me correct this issue? Thanks!

Deb

 

[debraj007]

Thankd Rick for another excellent answer and quick response!

Deb

I screwed something up . I (stupidly) decided to add a few more
paramenters to my Insert Into code and am now getting a 3134 syntax
error, any suggestions?

Most of your form references have a single quote only on the leading side and no
quote on the trailing side. I have added spaces below to make these single
quotes easier to see. You need to add a single quote before each comma in most
of those places.

You also have a last trailing comma after VendorCity that doesn't need to be
there at all.

You need to envision the statement how it will look when those form references
are replaced with the string values they point at. As a troubleshooting guide
you can add a line right after building the variable strSQL of...

Debug.Print strSQL

That will print the entire sql string to the immediate window where you can
examine it. Mistakes like you have here are easy to spot then.

strSQL = "INSERT INTO tblCrafts ( [CraftNo], [CraftInitials],
[MfgCompany], [MfgAddress], [MfgCity], [MfgZip], [MfgState],
[ProjectName], [VendorAddress], [VendorCity], ) " & _
"VALUES(" & Forms!frmEditQuote!NewCraftNumber & ", ' " & _
Forms!frmEditQuote!NewInitials & ", ' " & _
Forms!frmEditQuote!MfgCompany & ", ' " & _
Forms!frmEditQuote!MfgAddress & ", ' " & _
Forms!frmEditQuote!MfgCity & ", ' " & _
Forms!frmEditQuote!MfgZip & ", ' " & _
Forms!frmEditQuote!MfgState & ", ' " & _
Forms!frmEditQuote!ProjectName & ", ' " & _
Forms!frmEditQuote!VendorAddress & ", ' " & _
Forms!frmEditQuote!VendorCity & ", '" & " "

CurrentDb.Execute strSQL, dbFailOnError