Update query doesn't work

[Nicolae Fieraru]

Hi All,

I was trying to update a field in a table, based on the results from a
query.
The table to be updated is tblCustomers and the query is qrySelect. This
query has two parameters, provided by a form.
I created an update query called qryUpdate, which has both tblCustomers and
qrySelect (linked by CustomerID).
And I try to update one of the fields in tblCustomers.
If I open the form, and I click on a button, I can open qrySelect and I get
the records I am interested in. Then I can go in qryUpdate and if I execute
it manually, everything works fine.
But in the moment I try to execute it from code, I get the error: "Too few
parameters.Expected 2"

Regards,
Nicolae

======================================

' Here is the code I am using:

Private Sub btnMailMerge_Click()
On Error GoTo Err_btnMailMerge_Click

Dim oApp As Object
Dim strDocName As String

Set db = CurrentDb

Set oApp = CreateObject("Word.Application")

Set qdf = db.QueryDefs("qryClearSelectedForMailout")
qdf.Execute dbFailOnError

Dim dtDate As Date
Dim sBrand As String

sBrand = [Forms]![frmCustomersByBrandAndDate]![cboBrand]
dtDate = [Forms]![frmCustomersByBrandAndDate]![DTPicker]

strQueryName = "qryCustByBrandAndEnterDate" ' this query works by itself
strUpdateQueryName = "qryUpdateCustByBrandAndEnterDate" ' this query works
by itself

DoCmd.OpenQuery strQueryName, acNormal, acEdit 'this one works ok

Set qdf = db.QueryDefs(strUpdateQueryName)
'qdf.Parameters("cboBrand") = sBrand ' if I provide these parameters, it
will come up with no results
'qdf.Parameters("DTPicker") = dtDate
qdf.Execute dbFailOnError ' *********************** This is the line
which raises the error about too few parameters

strDocName = "C:/Databases/Docs/MailMerge.doc"

oApp.Visible = True
oApp.Application.Documents.Open strDocName
Set oApp = Nothing

Exit_btnMailMerge_Click:
Exit Sub

Err_btnMailMerge_Click:
MsgBox Err.Description
Resume Exit_btnMailMerge_Click
End Sub

[Jim Allensworth]

You didn't Dim qdf as an object variable.
It's hard to tell about the rest of it.

- Jim

On Fri, 9 Jul 2004 15:11:24 +1000, "Nicolae Fieraru"
<no****@please.cxm> wrote:

Hi All,

I was trying to update a field in a table, based on the results from a
query.
The table to be updated is tblCustomers and the query is qrySelect. This
query has two parameters, provided by a form.
I created an update query called qryUpdate, which has both tblCustomers and
qrySelect (linked by CustomerID).
And I try to update one of the fields in tblCustomers.
If I open the form, and I click on a button, I can open qrySelect and I get
the records I am interested in. Then I can go in qryUpdate and if I execute
it manually, everything works fine.
But in the moment I try to execute it from code, I get the error: "Too few
parameters.Expected 2"

Regards,
Nicolae

======================================

' Here is the code I am using:

Private Sub btnMailMerge_Click()
On Error GoTo Err_btnMailMerge_Click

Dim oApp As Object
Dim strDocName As String

Set db = CurrentDb

Set oApp = CreateObject("Word.Application")

Set qdf = db.QueryDefs("qryClearSelectedForMailout")
qdf.Execute dbFailOnError

Dim dtDate As Date
Dim sBrand As String

sBrand = [Forms]![frmCustomersByBrandAndDate]![cboBrand]
dtDate = [Forms]![frmCustomersByBrandAndDate]![DTPicker]

strQueryName = "qryCustByBrandAndEnterDate" ' this query works by itself
strUpdateQueryName = "qryUpdateCustByBrandAndEnterDate" ' this query works
by itself

DoCmd.OpenQuery strQueryName, acNormal, acEdit 'this one works ok

Set qdf = db.QueryDefs(strUpdateQueryName)
'qdf.Parameters("cboBrand") = sBrand ' if I provide these parameters, it
will come up with no results
'qdf.Parameters("DTPicker") = dtDate
qdf.Execute dbFailOnError ' *********************** This is the line
which raises the error about too few parameters

strDocName = "C:/Databases/Docs/MailMerge.doc"

oApp.Visible = True
oApp.Application.Documents.Open strDocName
Set oApp = Nothing

Exit_btnMailMerge_Click:
Exit Sub

Err_btnMailMerge_Click:
MsgBox Err.Description
Resume Exit_btnMailMerge_Click
End Sub

[James Fortune]

"Nicolae Fieraru" <no****@please.cxm> wrote in message news:<40********@duster.adelaide.on.net>...

Hi All,

I was trying to update a field in a table, based on the results from a
query.
The table to be updated is tblCustomers and the query is qrySelect. This
query has two parameters, provided by a form.
I created an update query called qryUpdate, which has both tblCustomers and
qrySelect (linked by CustomerID).
And I try to update one of the fields in tblCustomers.
If I open the form, and I click on a button, I can open qrySelect and I get
the records I am interested in. Then I can go in qryUpdate and if I execute
it manually, everything works fine.
But in the moment I try to execute it from code, I get the error: "Too few
parameters.Expected 2"

Regards,
Nicolae

Private Sub cmdTest_Click()
Dim db As Database
Dim qdf As QueryDef
Dim strUpdateQueryName As String

Set db = CurrentDb
strUpdateQueryName = "qryUpdateCustByBrandAndEnterDate"
Set qdf = db.QueryDefs(strUpdateQueryName)
qdf.Parameters("cboBrand") = cboBrand.Value
qdf.Parameters("DTPicker") = DTPicker.Value
qdf.Execute dbFailOnError
MsgBox("Done.")
End Sub

This code allowed updates for me with qryCustByBrandAndEnterDate.SQL =
"PARAMETERS DTPicker DateTime, cboBrand Text; UPDATE tblCustomers SET
tblCustomers.theDate = [DTPicker], tblCustomers.Brand = [cboBrand];"

Maybe having the same name for both controls and parameters was
causing the problem. Also, perhaps using the querydef.Execute to run
the query is not a good way to do it since I have never run into this
problem when using DoCmd.RunSQL strSQL or MyDB.Execute strSQL and the
values were sitting on the form.

James A. Fortune

[Nicolae Fieraru]

Hi Jim,

Thank you very much for your comments. You are right about that. I forgot to
set Option Explicit. But that didn't solve the problem.

Regards,
Nicolae

"Jim Allensworth" <ji****@datacentricsolutions.com> wrote in message
news:40****************@news.west.earthlink.net...

You didn't Dim qdf as an object variable.

[Nicolae Fieraru]

Hi James,

Thank you very much, you are a lifesaver :-)
Based on your suggestion, I solved the problem. I changed the parameters
names in the query. I am not sure yet exactly what was the problem, but the
fact that you told me the code you created worked for you helped me to solve
mine as well.
There was no need for the .Value
I don't think there is much difference between running a query or an sql
string. I suppose that when I run an existing query via qdf.Execute, this
command gets the query SQL string as well. Besides, I consider it is much
easier to use the query designer to modify the query, instead of creating
the query by hand (I have problems understanding the Left Join, etc :-) ).
I think that my mistake was that I was trying to use in the query value
straight from the form for parameters. There is no need for that, since I
can provide them when I call the query from VBA.

Best regards,
Nicolae
"James Fortune" <ja******@oakland.edu> wrote in message
news:a6**************************@posting.google.c om...

"Nicolae Fieraru" <no****@please.cxm> wrote in message news:<40********@duster.adelaide.on.net>...

Private Sub cmdTest_Click()
Dim db As Database
Dim qdf As QueryDef
Dim strUpdateQueryName As String

Set db = CurrentDb
strUpdateQueryName = "qryUpdateCustByBrandAndEnterDate"
Set qdf = db.QueryDefs(strUpdateQueryName)
qdf.Parameters("cboBrand") = cboBrand.Value
qdf.Parameters("DTPicker") = DTPicker.Value
qdf.Execute dbFailOnError
MsgBox("Done.")
End Sub

This code allowed updates for me with qryCustByBrandAndEnterDate.SQL =
"PARAMETERS DTPicker DateTime, cboBrand Text; UPDATE tblCustomers SET
tblCustomers.theDate = [DTPicker], tblCustomers.Brand = [cboBrand];"

Maybe having the same name for both controls and parameters was
causing the problem. Also, perhaps using the querydef.Execute to run
the query is not a good way to do it since I have never run into this
problem when using DoCmd.RunSQL strSQL or MyDB.Execute strSQL and the
values were sitting on the form.

James A. Fortune

[Jim Allensworth]

Setting Option Explicit had nothing to do with my comment! However it
is a recommended thing to do.

I simply noted that you did not have the object variable dimmed in the
procedure. Perhaps it was a module level variable - which seems kind
of strange. At any rate it sounds like you have solved your problem -
glad to hear it.

- Jim

On Mon, 12 Jul 2004 10:01:18 +1000, "Nicolae Fieraru"
<no****@please.cxm> wrote:

Hi Jim,

Thank you very much for your comments. You are right about that. I forgot to
set Option Explicit. But that didn't solve the problem.

Regards,
Nicolae

"Jim Allensworth" <ji****@datacentricsolutions.com> wrote in message
news:40****************@news.west.earthlink.net.. .

You didn't Dim qdf as an object variable.

[James Fortune]

"Nicolae Fieraru" <no****@please.cxm> wrote in message news:<40******@duster.adelaide.on.net>...

Hi James,
...
There was no need for the .Value
It's good practice to put the .Value in so that if the default for the
combobox changes in a future version of Access the code will survive
the conversion process providing .Value still exists.
I don't think there is much difference between running a query or an sql
string. I suppose that when I run an existing query via qdf.Execute, this
command gets the query SQL string as well. Besides, I consider it is much
easier to use the query designer to modify the query, instead of creating
the query by hand (I have problems understanding the Left Join, etc :-) ).
I think that my mistake was that I was trying to use in the query value
straight from the form for parameters. There is no need for that, since I
can provide them when I call the query from VBA.

In theory there's no difference between theory and practice, but in
practice there is. --- Rebecca Riordan

I'm glad you found something that works for you.

James A. Fortune