execute append query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

i have an existing append query called qryTempPurchaseOrder which appends
data from another query qryPurchaseOrders to a table TempPurchaseOrder. i
have added a criteria within qryTempPurchaseOrder to the field
PurchaseOrderID as follows:
=[forms]![purchaseorders]![purchaseorderid]

this works fine when running from within the append query, with the form
PurchaseOrders open in the background, but trying to run with VBA using the
following code brings up error "Too few parameters. expected 1":

Dim qdfAppend As DAO.QueryDef
Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
qdfAppend.Execute

what am i doing wrong and/or is there a better way to approach it?

thanks
 
The Expression Service resolves the reference to the control on the form
when you execute the query normally, but the ES is not available in DAO.

You can tell DAO how to read the parameter like this:
qdfAppend.Parameters("[forms]![purchaseorders]![purchaseorderid]") =
[forms]![purchaseorders]![purchaseorderid]

If the purchaseorderid field is Text (not Number), you will need additional
quotes.

It may also help to declare the parameter:
- Open the query in design view.
- Choose Parameters on the Query menu.
- In the dialog enter something like this:
[forms]![purchaseorders]![purchaseorderid] Long

My personal preference is to use a query string in VBA code instead of a
saved query. There are fewer saved queries to manage, and fewer dependencies
(e.g. if you copy a form to reuse it elsewhere). You concatenate the value
into the string, like this:

Dim strSql As String
strSql = "INSERT INTO ... WHERE [PurchaseOrderID] = " & _
Forms![purchaseorders]![purchaseorderid] & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...
 
Hi Allen

had tried what you suggested below including declaring parameter in query,
and while it removed the "too few parameters" error it appended all records
not just the one where purchaseorderid = the purchaseorderid on the form

any other thoughts???

thanks
--
David Benjamin


Allen Browne said:
The Expression Service resolves the reference to the control on the form
when you execute the query normally, but the ES is not available in DAO.

You can tell DAO how to read the parameter like this:
qdfAppend.Parameters("[forms]![purchaseorders]![purchaseorderid]") =
[forms]![purchaseorders]![purchaseorderid]

If the purchaseorderid field is Text (not Number), you will need additional
quotes.

It may also help to declare the parameter:
- Open the query in design view.
- Choose Parameters on the Query menu.
- In the dialog enter something like this:
[forms]![purchaseorders]![purchaseorderid] Long

My personal preference is to use a query string in VBA code instead of a
saved query. There are fewer saved queries to manage, and fewer dependencies
(e.g. if you copy a form to reuse it elsewhere). You concatenate the value
into the string, like this:

Dim strSql As String
strSql = "INSERT INTO ... WHERE [PurchaseOrderID] = " & _
Forms![purchaseorders]![purchaseorderid] & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
i have an existing append query called qryTempPurchaseOrder which appends
data from another query qryPurchaseOrders to a table TempPurchaseOrder. i
have added a criteria within qryTempPurchaseOrder to the field
PurchaseOrderID as follows:
=[forms]![purchaseorders]![purchaseorderid]

this works fine when running from within the append query, with the form
PurchaseOrders open in the background, but trying to run with VBA using
the
following code brings up error "Too few parameters. expected 1":

Dim qdfAppend As DAO.QueryDef
Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
qdfAppend.Execute

what am i doing wrong and/or is there a better way to approach it?

thanks
Click to expand...
Click to expand...
 
Did you declare the parameter in the query?

Was there a value of a suitable type (a number?) in the text box at the time
you executed the code?

If all else fails, can you build the string dynamically (as suggested) so
you can:
Debug.Print strSQL
to see what's going on?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Hi Allen

had tried what you suggested below including declaring parameter in query,
and while it removed the "too few parameters" error it appended all
records
not just the one where purchaseorderid = the purchaseorderid on the form

any other thoughts???

thanks
--
David Benjamin


Allen Browne said:
The Expression Service resolves the reference to the control on the form
when you execute the query normally, but the ES is not available in DAO.

You can tell DAO how to read the parameter like this:
qdfAppend.Parameters("[forms]![purchaseorders]![purchaseorderid]") =
[forms]![purchaseorders]![purchaseorderid]

If the purchaseorderid field is Text (not Number), you will need
additional
quotes.

It may also help to declare the parameter:
- Open the query in design view.
- Choose Parameters on the Query menu.
- In the dialog enter something like this:
[forms]![purchaseorders]![purchaseorderid] Long

My personal preference is to use a query string in VBA code instead of a
saved query. There are fewer saved queries to manage, and fewer
dependencies
(e.g. if you copy a form to reuse it elsewhere). You concatenate the
value
into the string, like this:

Dim strSql As String
strSql = "INSERT INTO ... WHERE [PurchaseOrderID] = " & _
Forms![purchaseorders]![purchaseorderid] & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError

in
message
i have an existing append query called qryTempPurchaseOrder which
appends
data from another query qryPurchaseOrders to a table TempPurchaseOrder.
i
have added a criteria within qryTempPurchaseOrder to the field
PurchaseOrderID as follows:
=[forms]![purchaseorders]![purchaseorderid]

this works fine when running from within the append query, with the
form
PurchaseOrders open in the background, but trying to run with VBA using
the
following code brings up error "Too few parameters. expected 1":

Dim qdfAppend As DAO.QueryDef
Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
qdfAppend.Execute

what am i doing wrong and/or is there a better way to approach it?

thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi Allen

yes i did declare the parameter in the query and yes there was a suitable
value (number) in the textbox on the form.
the string is rather long which is why i kept it as a query, but will
attempt this (will have to join several strings) and see what is happening.

thanks
--
David Benjamin


Allen Browne said:
Did you declare the parameter in the query?

Was there a value of a suitable type (a number?) in the text box at the time
you executed the code?

If all else fails, can you build the string dynamically (as suggested) so
you can:
Debug.Print strSQL
to see what's going on?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Hi Allen

had tried what you suggested below including declaring parameter in query,
and while it removed the "too few parameters" error it appended all
records
not just the one where purchaseorderid = the purchaseorderid on the form

any other thoughts???

thanks
--
David Benjamin


Allen Browne said:
The Expression Service resolves the reference to the control on the form
when you execute the query normally, but the ES is not available in DAO.

You can tell DAO how to read the parameter like this:
qdfAppend.Parameters("[forms]![purchaseorders]![purchaseorderid]") =
[forms]![purchaseorders]![purchaseorderid]

If the purchaseorderid field is Text (not Number), you will need
additional
quotes.

It may also help to declare the parameter:
- Open the query in design view.
- Choose Parameters on the Query menu.
- In the dialog enter something like this:
[forms]![purchaseorders]![purchaseorderid] Long

My personal preference is to use a query string in VBA code instead of a
saved query. There are fewer saved queries to manage, and fewer
dependencies
(e.g. if you copy a form to reuse it elsewhere). You concatenate the
value
into the string, like this:

Dim strSql As String
strSql = "INSERT INTO ... WHERE [PurchaseOrderID] = " & _
Forms![purchaseorders]![purchaseorderid] & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError

in
message

i have an existing append query called qryTempPurchaseOrder which
appends
data from another query qryPurchaseOrders to a table TempPurchaseOrder.
i
have added a criteria within qryTempPurchaseOrder to the field
PurchaseOrderID as follows:
=[forms]![purchaseorders]![purchaseorderid]

this works fine when running from within the append query, with the
form
PurchaseOrders open in the background, but trying to run with VBA using
the
following code brings up error "Too few parameters. expected 1":

Dim qdfAppend As DAO.QueryDef
Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
qdfAppend.Execute

what am i doing wrong and/or is there a better way to approach it?

thanks
Click to expand...
Click to expand...
Click to expand...
 
Hi Allen (anyone else)

copied sql from query (without parameter) into strings in vba in form,
joined strings and ended with:
"... WHERE PurchaseOrderID = " & me!purchaseorderID

so code now reads:

Dim qdfAppend as querydef
Dim A, B, C, D, E As String
Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
A = "INSERT INTO TempPurchaseOrders ..."
B = "..."
....
E = "...WHERE PurchaseOrderID = " & me!purchaseorderID
qdfAppend.SQL = A & B & C & D & E
qdfAppend.Execute

this gave the desired result of only copying the 1 record whose
purchaseorderid = purchaseorderID on the form.

and the depug.print qdfappend.sql showed the same result

not sure why it didn't work the other way.

any further thoughts would be appreciated as i'd much rather use the
existing query

thanks
--
David Benjamin


[email protected] said:
Hi Allen

yes i did declare the parameter in the query and yes there was a suitable
value (number) in the textbox on the form.
the string is rather long which is why i kept it as a query, but will
attempt this (will have to join several strings) and see what is happening.

thanks
--
David Benjamin


Allen Browne said:
Did you declare the parameter in the query?

Was there a value of a suitable type (a number?) in the text box at the time
you executed the code?

If all else fails, can you build the string dynamically (as suggested) so
you can:
Debug.Print strSQL
to see what's going on?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Hi Allen

had tried what you suggested below including declaring parameter in query,
and while it removed the "too few parameters" error it appended all
records
not just the one where purchaseorderid = the purchaseorderid on the form

any other thoughts???

thanks
--
David Benjamin


:

The Expression Service resolves the reference to the control on the form
when you execute the query normally, but the ES is not available in DAO.

You can tell DAO how to read the parameter like this:
qdfAppend.Parameters("[forms]![purchaseorders]![purchaseorderid]") =
[forms]![purchaseorders]![purchaseorderid]

If the purchaseorderid field is Text (not Number), you will need
additional
quotes.

It may also help to declare the parameter:
- Open the query in design view.
- Choose Parameters on the Query menu.
- In the dialog enter something like this:
[forms]![purchaseorders]![purchaseorderid] Long

My personal preference is to use a query string in VBA code instead of a
saved query. There are fewer saved queries to manage, and fewer
dependencies
(e.g. if you copy a form to reuse it elsewhere). You concatenate the
value
into the string, like this:

Dim strSql As String
strSql = "INSERT INTO ... WHERE [PurchaseOrderID] = " & _
Forms![purchaseorders]![purchaseorderid] & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError

in
message

i have an existing append query called qryTempPurchaseOrder which
appends
data from another query qryPurchaseOrders to a table TempPurchaseOrder.
i
have added a criteria within qryTempPurchaseOrder to the field
PurchaseOrderID as follows:
=[forms]![purchaseorders]![purchaseorderid]

this works fine when running from within the append query, with the
form
PurchaseOrders open in the background, but trying to run with VBA using
the
following code brings up error "Too few parameters. expected 1":

Dim qdfAppend As DAO.QueryDef
Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
qdfAppend.Execute

what am i doing wrong and/or is there a better way to approach it?

thanks
Click to expand...
Click to expand...
Click to expand...
 
Just a comment:

Dim A, B, C, D, E As String

doesn't do what you probably want it to.

That declares A, B, C and D as variants: only E is a string. To make them
all strings, you need:

Dim A As String, B As String, C As String, D As String, E As String

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



[email protected] said:
Hi Allen (anyone else)

copied sql from query (without parameter) into strings in vba in form,
joined strings and ended with:
"... WHERE PurchaseOrderID = " & me!purchaseorderID

so code now reads:

Dim qdfAppend as querydef
Dim A, B, C, D, E As String
Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
A = "INSERT INTO TempPurchaseOrders ..."
B = "..."
...
E = "...WHERE PurchaseOrderID = " & me!purchaseorderID
qdfAppend.SQL = A & B & C & D & E
qdfAppend.Execute

this gave the desired result of only copying the 1 record whose
purchaseorderid = purchaseorderID on the form.

and the depug.print qdfappend.sql showed the same result

not sure why it didn't work the other way.

any further thoughts would be appreciated as i'd much rather use the
existing query

thanks
--
David Benjamin


[email protected] said:
Hi Allen

yes i did declare the parameter in the query and yes there was a suitable
value (number) in the textbox on the form.
the string is rather long which is why i kept it as a query, but will
attempt this (will have to join several strings) and see what is
happening.

thanks
--
David Benjamin


Allen Browne said:
Did you declare the parameter in the query?

Was there a value of a suitable type (a number?) in the text box at the
time
you executed the code?

If all else fails, can you build the string dynamically (as suggested)
so
you can:
Debug.Print strSQL
to see what's going on?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"(e-mail address removed)" <[email protected]>
wrote in
message
Hi Allen

had tried what you suggested below including declaring parameter in
query,
and while it removed the "too few parameters" error it appended all
records
not just the one where purchaseorderid = the purchaseorderid on the
form

any other thoughts???

thanks
--
David Benjamin


:

The Expression Service resolves the reference to the control on the
form
when you execute the query normally, but the ES is not available in
DAO.

You can tell DAO how to read the parameter like this:

qdfAppend.Parameters("[forms]![purchaseorders]![purchaseorderid]") =
[forms]![purchaseorders]![purchaseorderid]

If the purchaseorderid field is Text (not Number), you will need
additional
quotes.

It may also help to declare the parameter:
- Open the query in design view.
- Choose Parameters on the Query menu.
- In the dialog enter something like this:
[forms]![purchaseorders]![purchaseorderid] Long

My personal preference is to use a query string in VBA code instead
of a
saved query. There are fewer saved queries to manage, and fewer
dependencies
(e.g. if you copy a form to reuse it elsewhere). You concatenate the
value
into the string, like this:

Dim strSql As String
strSql = "INSERT INTO ... WHERE [PurchaseOrderID] = " & _
Forms![purchaseorders]![purchaseorderid] & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError

"(e-mail address removed)" <[email protected]>
wrote
in
message

i have an existing append query called qryTempPurchaseOrder which
appends
data from another query qryPurchaseOrders to a table
TempPurchaseOrder.
i
have added a criteria within qryTempPurchaseOrder to the field
PurchaseOrderID as follows:
=[forms]![purchaseorders]![purchaseorderid]

this works fine when running from within the append query, with
the
form
PurchaseOrders open in the background, but trying to run with VBA
using
the
following code brings up error "Too few parameters. expected 1":

Dim qdfAppend As DAO.QueryDef
Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
qdfAppend.Execute

what am i doing wrong and/or is there a better way to approach it?

thanks
Click to expand...
Click to expand...
Click to expand...
 
mmmm...didn't know that...seemed to work anyway

any thoughts on my original problem?

thanks
--
David Benjamin


Douglas J. Steele said:
Just a comment:

Dim A, B, C, D, E As String

doesn't do what you probably want it to.

That declares A, B, C and D as variants: only E is a string. To make them
all strings, you need:

Dim A As String, B As String, C As String, D As String, E As String

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



[email protected] said:
Hi Allen (anyone else)

copied sql from query (without parameter) into strings in vba in form,
joined strings and ended with:
"... WHERE PurchaseOrderID = " & me!purchaseorderID

so code now reads:

Dim qdfAppend as querydef
Dim A, B, C, D, E As String
Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
A = "INSERT INTO TempPurchaseOrders ..."
B = "..."
...
E = "...WHERE PurchaseOrderID = " & me!purchaseorderID
qdfAppend.SQL = A & B & C & D & E
qdfAppend.Execute

this gave the desired result of only copying the 1 record whose
purchaseorderid = purchaseorderID on the form.

and the depug.print qdfappend.sql showed the same result

not sure why it didn't work the other way.

any further thoughts would be appreciated as i'd much rather use the
existing query

thanks
--
David Benjamin


[email protected] said:
Hi Allen

yes i did declare the parameter in the query and yes there was a suitable
value (number) in the textbox on the form.
the string is rather long which is why i kept it as a query, but will
attempt this (will have to join several strings) and see what is
happening.

thanks
--
David Benjamin


:

Did you declare the parameter in the query?

Was there a value of a suitable type (a number?) in the text box at the
time
you executed the code?

If all else fails, can you build the string dynamically (as suggested)
so
you can:
Debug.Print strSQL
to see what's going on?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"(e-mail address removed)" <[email protected]>
wrote in
message
Hi Allen

had tried what you suggested below including declaring parameter in
query,
and while it removed the "too few parameters" error it appended all
records
not just the one where purchaseorderid = the purchaseorderid on the
form

any other thoughts???

thanks
--
David Benjamin


:

The Expression Service resolves the reference to the control on the
form
when you execute the query normally, but the ES is not available in
DAO.

You can tell DAO how to read the parameter like this:

qdfAppend.Parameters("[forms]![purchaseorders]![purchaseorderid]") =
[forms]![purchaseorders]![purchaseorderid]

If the purchaseorderid field is Text (not Number), you will need
additional
quotes.

It may also help to declare the parameter:
- Open the query in design view.
- Choose Parameters on the Query menu.
- In the dialog enter something like this:
[forms]![purchaseorders]![purchaseorderid] Long

My personal preference is to use a query string in VBA code instead
of a
saved query. There are fewer saved queries to manage, and fewer
dependencies
(e.g. if you copy a form to reuse it elsewhere). You concatenate the
value
into the string, like this:

Dim strSql As String
strSql = "INSERT INTO ... WHERE [PurchaseOrderID] = " & _
Forms![purchaseorders]![purchaseorderid] & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError

"(e-mail address removed)" <[email protected]>
wrote
in
message

i have an existing append query called qryTempPurchaseOrder which
appends
data from another query qryPurchaseOrders to a table
TempPurchaseOrder.
i
have added a criteria within qryTempPurchaseOrder to the field
PurchaseOrderID as follows:
=[forms]![purchaseorders]![purchaseorderid]

this works fine when running from within the append query, with
the
form
PurchaseOrders open in the background, but trying to run with VBA
using
the
following code brings up error "Too few parameters. expected 1":

Dim qdfAppend As DAO.QueryDef
Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
qdfAppend.Execute

what am i doing wrong and/or is there a better way to approach it?

thanks
Click to expand...
Click to expand...
Click to expand...
 
Okay, you have it working. Good.

It may be that adding dbFailOnError to your original Execute statement would
at least have let you know it was failing.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Hi Allen (anyone else)

copied sql from query (without parameter) into strings in vba in form,
joined strings and ended with:
"... WHERE PurchaseOrderID = " & me!purchaseorderID

so code now reads:

Dim qdfAppend as querydef
Dim A, B, C, D, E As String
Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
A = "INSERT INTO TempPurchaseOrders ..."
B = "..."
...
E = "...WHERE PurchaseOrderID = " & me!purchaseorderID
qdfAppend.SQL = A & B & C & D & E
qdfAppend.Execute

this gave the desired result of only copying the 1 record whose
purchaseorderid = purchaseorderID on the form.

and the depug.print qdfappend.sql showed the same result

not sure why it didn't work the other way.

any further thoughts would be appreciated as i'd much rather use the
existing query

thanks
--
David Benjamin


[email protected] said:
Hi Allen

yes i did declare the parameter in the query and yes there was a suitable
value (number) in the textbox on the form.
the string is rather long which is why i kept it as a query, but will
attempt this (will have to join several strings) and see what is
happening.

thanks
--
David Benjamin


Allen Browne said:
Did you declare the parameter in the query?

Was there a value of a suitable type (a number?) in the text box at the
time
you executed the code?

If all else fails, can you build the string dynamically (as suggested)
so
you can:
Debug.Print strSQL
to see what's going on?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"(e-mail address removed)" <[email protected]>
wrote in
message
Hi Allen

had tried what you suggested below including declaring parameter in
query,
and while it removed the "too few parameters" error it appended all
records
not just the one where purchaseorderid = the purchaseorderid on the
form

any other thoughts???

thanks
--
David Benjamin


:

The Expression Service resolves the reference to the control on the
form
when you execute the query normally, but the ES is not available in
DAO.

You can tell DAO how to read the parameter like this:

qdfAppend.Parameters("[forms]![purchaseorders]![purchaseorderid]") =
[forms]![purchaseorders]![purchaseorderid]

If the purchaseorderid field is Text (not Number), you will need
additional
quotes.

It may also help to declare the parameter:
- Open the query in design view.
- Choose Parameters on the Query menu.
- In the dialog enter something like this:
[forms]![purchaseorders]![purchaseorderid] Long

My personal preference is to use a query string in VBA code instead
of a
saved query. There are fewer saved queries to manage, and fewer
dependencies
(e.g. if you copy a form to reuse it elsewhere). You concatenate the
value
into the string, like this:

Dim strSql As String
strSql = "INSERT INTO ... WHERE [PurchaseOrderID] = " & _
Forms![purchaseorders]![purchaseorderid] & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError

"(e-mail address removed)" <[email protected]>
wrote
in
message

i have an existing append query called qryTempPurchaseOrder which
appends
data from another query qryPurchaseOrders to a table
TempPurchaseOrder.
i
have added a criteria within qryTempPurchaseOrder to the field
PurchaseOrderID as follows:
=[forms]![purchaseorders]![purchaseorderid]

this works fine when running from within the append query, with
the
form
PurchaseOrders open in the background, but trying to run with VBA
using
the
following code brings up error "Too few parameters. expected 1":

Dim qdfAppend As DAO.QueryDef
Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
qdfAppend.Execute

what am i doing wrong and/or is there a better way to approach it?

thanks
Click to expand...
Click to expand...
Click to expand...
 
I have a similar situation as in this thread except my append query uses a
HAVE clause. I used the suggestion from Allen Browne to use the query in VBA
but I get a "too few parameters. Expected 1" error. The code is this:

Private Sub SQE_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO tblTAAir ( InfoID, PO_NUM, PO_ITM ) SELECT
tblTAInfo.InfoID, tblTAInfo.PO_NUM, tblTAInfo.PO_ITM From tblTAInfo GROUP BY
tblTAInfo.InfoID, tblTAInfo.PO_NUM, tblTAInfo.PO_ITM HAVING
(((tblTAInfo.InfoID)=[Forms]![frmTravelAuthorization]![fsubTAInfo]![ID]));"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

I copied the SQL Statement directly from my query I used to test. The query
itself works. I am not very adept with VBA or programming for that matter.
Any suggestions?

Allen Browne said:
The Expression Service resolves the reference to the control on the form
when you execute the query normally, but the ES is not available in DAO.

You can tell DAO how to read the parameter like this:
qdfAppend.Parameters("[forms]![purchaseorders]![purchaseorderid]") =
[forms]![purchaseorders]![purchaseorderid]

If the purchaseorderid field is Text (not Number), you will need additional
quotes.

It may also help to declare the parameter:
- Open the query in design view.
- Choose Parameters on the Query menu.
- In the dialog enter something like this:
[forms]![purchaseorders]![purchaseorderid] Long

My personal preference is to use a query string in VBA code instead of a
saved query. There are fewer saved queries to manage, and fewer dependencies
(e.g. if you copy a form to reuse it elsewhere). You concatenate the value
into the string, like this:

Dim strSql As String
strSql = "INSERT INTO ... WHERE [PurchaseOrderID] = " & _
Forms![purchaseorders]![purchaseorderid] & ";"
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
i have an existing append query called qryTempPurchaseOrder which appends
data from another query qryPurchaseOrders to a table TempPurchaseOrder. i
have added a criteria within qryTempPurchaseOrder to the field
PurchaseOrderID as follows:
=[forms]![purchaseorders]![purchaseorderid]

this works fine when running from within the append query, with the form
PurchaseOrders open in the background, but trying to run with VBA using
the
following code brings up error "Too few parameters. expected 1":

Dim qdfAppend As DAO.QueryDef
Set qdfAppend = CurrentDb.QueryDefs("qryTempPurchaseOrder")
qdfAppend.Execute

what am i doing wrong and/or is there a better way to approach it?

thanks
Click to expand...
Click to expand...
 
SAP2 said:
I have a similar situation as in this thread except my append query uses a
HAVE clause. I used the suggestion from Allen Browne to use the query in
VBA
but I get a "too few parameters. Expected 1" error. The code is this:

Private Sub SQE_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO tblTAAir ( InfoID, PO_NUM, PO_ITM ) SELECT
tblTAInfo.InfoID, tblTAInfo.PO_NUM, tblTAInfo.PO_ITM From tblTAInfo GROUP
BY
tblTAInfo.InfoID, tblTAInfo.PO_NUM, tblTAInfo.PO_ITM HAVING
(((tblTAInfo.InfoID)=[Forms]![frmTravelAuthorization]![fsubTAInfo]![ID]));"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

I copied the SQL Statement directly from my query I used to test. The
query
itself works. I am not very adept with VBA or programming for that
matter.
Any suggestions?
Click to expand...


Since you're building the whole SQL statement in code, you can just "build
in" the value of the form control:

Private Sub SQE_AfterUpdate()

Dim strSql As String

strSql = _
"INSERT INTO tblTAAir ( InfoID, PO_NUM, PO_ITM ) " & _
"SELECT InfoID, PO_NUM, PO_ITM From tblTAInfo " & _
"GROUP BY InfoID, PO_NUM, PO_ITM " & _
"HAVING InfoID=" & _
[Forms]![frmTravelAuthorization]![fsubTAInfo]![ID]

DBEngine(0)(0).Execute strSql, dbFailOnError

End Sub


That assumes that the InfoID field is numeric. If it's text, a small change
must be made to incorporate quotes around the value.
 
It did not work in the After Update Event but did in the After Insert Event.
The problem I had was due to me just copying and pasting the the SQL from a
query? Because it looks like my biggest problem was that I didn't writ eit
correctly in VBA.

Thank you very much for your time.

Dirk Goldgar said:
SAP2 said:
I have a similar situation as in this thread except my append query uses a
HAVE clause. I used the suggestion from Allen Browne to use the query in
VBA
but I get a "too few parameters. Expected 1" error. The code is this:

Private Sub SQE_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO tblTAAir ( InfoID, PO_NUM, PO_ITM ) SELECT
tblTAInfo.InfoID, tblTAInfo.PO_NUM, tblTAInfo.PO_ITM From tblTAInfo GROUP
BY
tblTAInfo.InfoID, tblTAInfo.PO_NUM, tblTAInfo.PO_ITM HAVING
(((tblTAInfo.InfoID)=[Forms]![frmTravelAuthorization]![fsubTAInfo]![ID]));"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

I copied the SQL Statement directly from my query I used to test. The
query
itself works. I am not very adept with VBA or programming for that
matter.
Any suggestions?
Click to expand...


Since you're building the whole SQL statement in code, you can just "build
in" the value of the form control:

Private Sub SQE_AfterUpdate()

Dim strSql As String

strSql = _
"INSERT INTO tblTAAir ( InfoID, PO_NUM, PO_ITM ) " & _
"SELECT InfoID, PO_NUM, PO_ITM From tblTAInfo " & _
"GROUP BY InfoID, PO_NUM, PO_ITM " & _
"HAVING InfoID=" & _
[Forms]![frmTravelAuthorization]![fsubTAInfo]![ID]

DBEngine(0)(0).Execute strSql, dbFailOnError

End Sub


That assumes that the InfoID field is numeric. If it's text, a small change
must be made to incorporate quotes around the value.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
SAP2 said:
It did not work in the After Update Event but did in the After Insert
Event.
The problem I had was due to me just copying and pasting the the SQL from
a
query? Because it looks like my biggest problem was that I didn't writ
eit
correctly in VBA.
Click to expand...


I don't think I have enough information about what you're trying to do, and
the context of it, to say why it didn't work in the AfterUpdate event.
Since we are talking, I believe, about the AfterUpdate event of a control,
not of the form itself, it may be that the record you wanted to insert was
not yet saved. You need to explain, in these cases, what you mean by "it
did not work".

The original error was due to the fact queries executed by DAO (that is, by
the Execute method) don't know anything about forms and the controls on
them. That's why you get the error about unresolved parameters -- as far as
DAO is concerned, the form/control reference is a parameter for which no
value has been provided. The revision I posted replaced the control
reference with the literal value of the control, thereby eliminating the
parameter altogether.
 
Ok, in the future I will be clearer.

Regardless, thank you for your help and very prompt responses. Good weekend!
 
Back
Top