A2K - simple append of one record to another table needed

[Deano]

Problem is that there are lots and lots of fields in an employee record. I
specify a form control as the criterion for one of those fields which is the
value of the primary key for that record. The idea is to copy the current
record I am viewing in the form, to another table.

I want to simple execute a stored query but I get a run-time error 3061, too
few parameters. Expected 1. So even though I've specified the control
within the query it's not finding the current control's value.

I don't want to cut and paste the string, mainly as I don't think it will
work - too long and all that and it would look a bit messy if I do get it
working.
Google groups is just saying that criteria should be specified but I can't
find anything else specific to my little problem.

Any way to fix my saved query?

thanks
Martin

[Allen Browne]

Solution 1
Concatenate the value into the SQL string in your code:
Dim strSql As String
strSql = "INSERT INTO ... WHERE ([SomeField] = " & _
Forms!Form1!Text0 & ");"
dbEngine(0)(0).Execute strSql, dbFailOnError

If SomeField is a Date/Time field, use # as the delimiter, and format the
date value:
strSql = "INSERT INTO ... WHERE ([SomeField] = #" & _
Format(Forms!Form1!Text0, "mm\/dd\/yyyy") & "#);"
If it is a Text type field, use " as the delimiter, doubled up (because
embedded in the string:
strSql = "INSERT INTO ... WHERE ([SomeField] = """ & _
Forms!Form1!Text0 & """);"

Solution 2:
If you prefer a saved query, explicitly supply the parameter:
Dim qdf As QueryDef
Set qdf = dbEngine(0)(0).QueryDefs("Query1")
qdf.Parameters("[Forms]![Form1]![Text0]") = [Forms]![Form1]![Text0]
qdf.Execute

Solution 3
Use RunSQL instead of Execute:
DoCmd.RunSQL "Query1"
Although simple, it has these disadvantages:
- Programmatically, you do not know if the insert succeeded.
- Confirmation dialog (can be suppressed by turning off SetWarnings.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Deano" <de***@mailinator.com> wrote in message
news:43**********************@ptn-nntp-reader02.plus.net...

Problem is that there are lots and lots of fields in an employee record.
I
specify a form control as the criterion for one of those fields which is
the
value of the primary key for that record. The idea is to copy the current
record I am viewing in the form, to another table.

I want to simple execute a stored query but I get a run-time error 3061,
too
few parameters. Expected 1. So even though I've specified the control
within the query it's not finding the current control's value.

I don't want to cut and paste the string, mainly as I don't think it will
work - too long and all that and it would look a bit messy if I do get it
working.
Google groups is just saying that criteria should be specified but I can't
find anything else specific to my little problem.

Any way to fix my saved query?

thanks
Martin

[Deano]

Allen Browne wrote:

Solution 1
Concatenate the value into the SQL string in your code:
Dim strSql As String
strSql = "INSERT INTO ... WHERE ([SomeField] = " & _
Forms!Form1!Text0 & ");"
dbEngine(0)(0).Execute strSql, dbFailOnError

If SomeField is a Date/Time field, use # as the delimiter, and format
the date value:
strSql = "INSERT INTO ... WHERE ([SomeField] = #" & _
Format(Forms!Form1!Text0, "mm\/dd\/yyyy") & "#);"
If it is a Text type field, use " as the delimiter, doubled up
(because embedded in the string:
strSql = "INSERT INTO ... WHERE ([SomeField] = """ & _
Forms!Form1!Text0 & """);"

Solution 2:
If you prefer a saved query, explicitly supply the parameter:
Dim qdf As QueryDef
Set qdf = dbEngine(0)(0).QueryDefs("Query1")
qdf.Parameters("[Forms]![Form1]![Text0]") =
[Forms]![Form1]![Text0] qdf.Execute

Solution 3
Use RunSQL instead of Execute:
DoCmd.RunSQL "Query1"
Although simple, it has these disadvantages:
- Programmatically, you do not know if the insert succeeded.
- Confirmation dialog (can be suppressed by turning off SetWarnings.)

Thanks Allen, great post. I'm using Solution 2 successfully.