Access 2007

[stewartie]

Hi,

I'm new to the group and would appreciate some help with a macro problem.

I have a database with a macro that runs a number (40+) of select queries.
It uses the following format:

Function RunQueries()
On Error GoTo RunQueries_Err

DoCmd.OpenQuery "QueryName", acViewNormal, acReadOnly
DoCmd.Close acQuery, "QueryName"

DoCmd.OpenQuery "QueryName_1", acViewNormal, acReadOnly
DoCmd.Close acQuery, "QueryName_1"

RunQueries_Exit:
Exit Function

RunQueries_Err:
MsgBox Error$
Resume RunQueries_Exit

End Function

I wish to copy the macro to run the same queries with one criteria changed, e.
g.

DoCmd.OpenQuery "QueryName", acViewNormal, acReadOnly
DoCmd.Close acQuery, "QueryName"

FOR/WHERE VARIABLE_1 = 2

Unfortunately I don't have the script-writing skills and I would be grateful
if anyone could assist.

Thanks in advance.

stewartie

 

[bhicks11 via AccessMonster.com]

Hi Stewartie,

There is always more than one way to skin a cat - though I don't know what
your variable is about - you could enclose each DoCmd in a simple if
statement, like this:

If variable_1 = 2 Then
DoCmd . . .
End If

Bonnie
http://www.dataplus-svc.com

 

[stewartie]

Bonnie,
Thanks for coming back so promptly. The variable is a simple a coding one
and in the original queries is set to "1". I need to run all the queries
again with it set to 2,3,4...16 and I'm hoping to do that in the macro script
instead of having (say) 16 versions of each query. I thought I could do
that in the macro (i.e have 16 macros) and that was what prompted the thread.

Sorry if I didn't make myself clear in the original post. If this isn't any
clearer just say and I will re-state.

Regards,

stewartie

Hi Stewartie,

There is always more than one way to skin a cat - though I don't know what
your variable is about - you could enclose each DoCmd in a simple if
statement, like this:

If variable_1 = 2 Then
DoCmd . . .
End If

Bonnie
http://www.dataplus-svc.com

Hi,

[quoted text clipped - 35 lines]

stewartie

 

[stewartie]

Bonnie,

Please disregard my last piece of gibberish - no sooner had I posted it than
I realised that your suggestion would probably do what I want. A bit of a
'senior moment'. I will give it a try shortly and let you know how it turns
out.

Again - apologies.

Regards,

stewartie.

Bonnie,
Thanks for coming back so promptly. The variable is a simple a coding one
and in the original queries is set to "1". I need to run all the queries
again with it set to 2,3,4...16 and I'm hoping to do that in the macro script
instead of having (say) 16 versions of each query. I thought I could do
that in the macro (i.e have 16 macros) and that was what prompted the thread.

Sorry if I didn't make myself clear in the original post. If this isn't any
clearer just say and I will re-state.

Regards,

stewartie

Hi Stewartie,

[quoted text clipped - 14 lines]

 

[bhicks11 via AccessMonster.com]

Don't worry Stewartie - I have those 'senior moments' all the time.

I didn't really know the details of what you are doing but if it works -
great. If not you can post again.

Bonnie

Bonnie,

Please disregard my last piece of gibberish - no sooner had I posted it than
I realised that your suggestion would probably do what I want. A bit of a
'senior moment'. I will give it a try shortly and let you know how it turns
out.

Again - apologies.

Regards,

stewartie.

Bonnie,
Thanks for coming back so promptly. The variable is a simple a coding one

[quoted text clipped - 15 lines]

 

[tina]

my first question would be, why are you opening and immediately closing
SELECT queries?

hth

 

[Steve Schapel]

Stewartie,

I agree with Tina. What you are doing here appears to make no sense.
Can you say what you are trying to achieve via this routine?

As an aside, I should also point out that what you have here is not a
macro, it is a VBA procedure.

 

[tina]

well, i picked up the first obvious point, but fell right over the second
one! owww! <gets up, rubbing.....bump on head>