Parameter query in VBA

[hbean]

Hi -

I'm trying to run a query as part of a VBA procedure, and what I want
it to do is to grab the value for a parameter (the current month) from
another part of the procedure. I clearly am missing something,
however. Here's what I have:

Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset
Dim strCurrentMonth As String
Dim strMsg As String
Dim qryPar As QueryDef

a bunch of stuff...

Set qryPar = db.QueryDefs("qryMonthlyAccrualUpdate")
qryPar.Parameters("Month") = rs3!Month
DoCmd.OpenQuery "qryMonthlyAccrualUpdate", acViewNormal

The value of rs3!Month should be whatever month's data they have just
entered (this is a database for tracking sick/annual accrual). The
query is designed to then calculate how many hours they have accrued
for that month and add it to a table so that their current balances
can be calculated. What is happening, however, is that I'm getting an
error that the rows can't be appended because of an access key
violation - I think this is because there isn't a value for the
month. I have both "month" and "ID" set as primary keys in the table.

Any help would be greatly appreciated. Also, if this is the wrong
forum for this message, I apologize.

TIA,
Heather

[storrboy]

On Mar 5, 6:39 pm, "hbean" <[e-mail]:

Hi -

I'm trying to run a query as part of a VBA procedure, and what I want
it to do is to grab the value for a parameter (the current month) from
another part of the procedure. I clearly am missing something,
however. Here's what I have:

Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset
Dim strCurrentMonth As String
Dim strMsg As String
Dim qryPar As QueryDef

a bunch of stuff...

Set qryPar = db.QueryDefs("qryMonthlyAccrualUpdate")
qryPar.Parameters("Month") = rs3!Month
DoCmd.OpenQuery "qryMonthlyAccrualUpdate", acViewNormal

The value of rs3!Month should be whatever month's data they have just
entered (this is a database for tracking sick/annual accrual). The
query is designed to then calculate how many hours they have accrued
for that month and add it to a table so that their current balances
can be calculated. What is happening, however, is that I'm getting an
error that the rows can't be appended because of an access key
violation - I think this is because there isn't a value for the
month. I have both "month" and "ID" set as primary keys in the table.

Any help would be greatly appreciated. Also, if this is the wrong
forum for this message, I apologize.

TIA,
Heather

If there are no records for Month (which is a reserved word - function
- and may cause even more problems) then you can't update. A key
violation can also mean that you are attempting to create duplicate
values in a field that is set to No Duplicates.

[Allen Browne]

The error occurs because the OpenQuery does not relate to the specific
instance of the QueryDef you assigned the parameter to.

The QueryDef object has an OpenRecordset method, so you can use the approach
you attempted with OpenRecordset. It does not have an OpenQuery method, so
you will need to take an alternative approach.

Some alternatives:
a) Open a form. Assign the value to the form, e.g.:
Forms!Form1!txtMonth = rs!Month
and in the Criteria of the form, use:
Forms!Form1!txtMonth
as the parameter.

b) Assign the SQL of the entire query, e.g.:
Const strcStub = "SELECT * FROM Table1 WHERE ([Month] = "
Const strcTail = ") ORDER BY SomeField;"
Currentdb.QueryDefs("qryMonthlyAccrualUpdate").SQL = strcStub &
rs3!Month & strcTail

c) Design a form (in datasheet view if you wish) instead of the query, and
assign its RecordSource (as above.)

--
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.

"hbean" <be*****@gmail.comwrote in message
news:11**********************@30g2000cwc.googlegro ups.com...

>
I'm trying to run a query as part of a VBA procedure, and what I want
it to do is to grab the value for a parameter (the current month) from
another part of the procedure. I clearly am missing something,
however. Here's what I have:

Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset
Dim strCurrentMonth As String
Dim strMsg As String
Dim qryPar As QueryDef

a bunch of stuff...

Set qryPar = db.QueryDefs("qryMonthlyAccrualUpdate")
qryPar.Parameters("Month") = rs3!Month
DoCmd.OpenQuery "qryMonthlyAccrualUpdate", acViewNormal

The value of rs3!Month should be whatever month's data they have just
entered (this is a database for tracking sick/annual accrual). The
query is designed to then calculate how many hours they have accrued
for that month and add it to a table so that their current balances
can be calculated. What is happening, however, is that I'm getting an
error that the rows can't be appended because of an access key
violation - I think this is because there isn't a value for the
month. I have both "month" and "ID" set as primary keys in the table.

Any help would be greatly appreciated. Also, if this is the wrong
forum for this message, I apologize.

TIA,
Heather

[rkc]

hbean wrote:

Dim db As Database
Dim rs As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset
Dim strCurrentMonth As String
Dim strMsg As String
Dim qryPar As QueryDef

a bunch of stuff...

Set qryPar = db.QueryDefs("qryMonthlyAccrualUpdate")
qryPar.Parameters("Month") = rs3!Month
DoCmd.OpenQuery "qryMonthlyAccrualUpdate", acViewNormal

The value of rs3!Month should be whatever month's data they have just
entered (this is a database for tracking sick/annual accrual). The
query is designed to then calculate how many hours they have accrued
for that month and add it to a table so that their current balances
can be calculated. What is happening, however, is that I'm getting an
error that the rows can't be appended because of an access key
violation - I think this is because there isn't a value for the
month. I have both "month" and "ID" set as primary keys in the table.

You're using the QueryDef incorrectly if you're trying to run
a action query. Doesn't it prompt you for the parameter? That should
be a clue.

Set qryPar = db.QueryDefs("qryMonthlyAccrualUpdate")
qryPar.Parameters("Month") = rs3!Month
'----------------
qryPar.Execute "qryMonthlyAccrualUpdate"
'----------------

[hbean]

On Mar 5, 5:42 pm, rkc <[e-mail]:

>
Set qryPar = db.QueryDefs("qryMonthlyAccrualUpdate")
qryPar.Parameters("Month") = rs3!Month
'----------------
qryPar.Execute "qryMonthlyAccrualUpdate"
'----------------

Yes, it was prompting me for the parameter (month), which I didn't
want it to do. I was wanting it to grab the month from the rs3!month
value, which isn't working. I tried the above, which doesn't seem to
work either. I think I will take one of the above suggestions and
have users enter months from a form so that I know which month they're
entering based on which button they've selected, and see how far I get
with that.

Thanks for the suggestions.

Heather

[rkc]

hbean wrote:

On Mar 5, 5:42 pm, rkc <[e-mail]:

> Set qryPar = db.QueryDefs("qryMonthlyAccrualUpdate")
qryPar.Parameters("Month") = rs3!Month
'----------------
qryPar.Execute "qryMonthlyAccrualUpdate"
'----------------

Yes, it was prompting me for the parameter (month), which I didn't
want it to do. I was wanting it to grab the month from the rs3!month
value, which isn't working. I tried the above, which doesn't seem to
work either. I think I will take one of the above suggestions and
have users enter months from a form so that I know which month they're
entering based on which button they've selected, and see how far I get
with that.

You can change your method and struggle with that or just fix what
you're doing now. I would check the results returned by rs3 to make
sure it's working first. I'd also think about naming the recordset
variables something that indicated what they were supposed to be returning.

[hbean]

I know that rs3!month is giving the correct month because I'm writing
that value to another table, and it's returning the correct value. Is
that what you mean? I can change the rs labels to something different
- that's not a big deal. What else would you suggest to try to fix
this? It's really causing me a lot of grief, and I'm really not
getting anywhere with my alternatives.

TIA,
Heather

[rkc]

hbean wrote:

I know that rs3!month is giving the correct month because I'm writing
that value to another table, and it's returning the correct value. Is
that what you mean? I can change the rs labels to something different
- that's not a big deal. What else would you suggest to try to fix
this? It's really causing me a lot of grief, and I'm really not
getting anywhere with my alternatives.

If I were you the first thing I would do is open up a test database
and write some code to prove to myself that you can execute an action
query using QueryDef.Execute and a saved parameter query.

Then I would write some test code to prove to myself that my query
is actually doing what I think it is doing.

[hbean]

THANK YOU, THANK YOU, THANK YOU!

The QueryDef.Execute did the trick! Many, many thanks! I've been
stuck for a week on this.

Heather