Running a Query with Different Values Problem

[pinkpanther6666]

i have written a query where i have put the [ ] in.
When i run a report based on the query runs it asks for me for a value and i input a value and the query runs correctly
Written a macro which saves this report to my hard drive
However
How can i automate the above but i need run the report multiple times with different values


Can anyone please point me in the right direction


Many Thanks


Steve
Using Access 2016

 

[Micron]

You say you have "written" a macro. If it's a macro I'd say phrase that as "I created a macro" only because some people refer to vba procedures as macros, which they are not. Procedures are written, macros are not, so I'm not sure which it is that you're working with.
If it's a macro (which I don't use; I only use vba code) I'd say you need to open the report and specify the name of a filter query for it, then run the file create steps.
You need to close the report before you open it again, using a different filter query (Filter Name) for the next save, or else it may get saved again as the first version. You've not said enough about your process to know if this is feasible; i.e., whether this is supposed to open, close, save and reopen automatically or what. I assume there is a reason why you're not just supplying the criteria 3 times.

 

[xenou]

Another trick would be to edit the sql directly with your code, then run the report, then put the sql back (if you want to keep that version that you have now).

 

[FDibbins]

Procedures are written, macros are not

Not sure what you mean by that, but macros are also written/composed/typed. Yes, you can record them, but that gives very limited capability, for more functionality, you need to type the code.

 

[Micron]

I mean in Access, one uses the macro builder to create a macro. Sure there is usually some typing involved but it's only for supplying parameters. A macro converted to code doesn't qualify as a written procedure as far as I'm concerned, meaning it's a procedure properly written in VBA and not an Access conversion, which are very easy to spot. If you call this a macro

Private Sub System_KeyDown(KeyCode As Integer, Shift As Integer)
Dim strText As String

If KeyCode = 114 Then
    strText = Me.ActiveControl.SelText
'MsgBox strText
    Me.Filter = "[System] LIKE '" & strText & "*'"
    Me.FilterOn = True
End If
End Sub

I say you are not correct - it is a procedure. You can disagree with me, but I'm too set in my ways to change my opinion!

 

[FDibbins]

Your comment was that you cannot write macros - I was just pointing out that you can

 

[pinkpanther6666]

Many many thanks for all the replys but I've tried n tried but sorted it out myself

I've just run the macro
(Non vba) with criteria and all works fine now

Many thanks to all who replied


Steve

 

[Micron]

Sorry pink, don't mean to hijack your thread (glad you got it solved).

I was just pointing out that you can

Can you explain how? In all my years of Access and Excel programming, I've always considered a procedure is something one writes (OK, types) and a macro is something you create with a bunch of mouse click selections and maybe a few parameters typed in for good measure.

 

[xenou]

Well, for what it's worth, in Access (unlike in Excel!) there are things called macros that are not the same as vba procedures and functions you write yourself. This is a cause of much confusion in a forum like this where there are many Excel vba programmers. MSAccess macros also have features that vba procedures and functions don't have -- mainly, a different security context. You create macros in MSAccess as Micron described - with mouseclicks and property sheets. You write vba procedures and functions the way we are used to - by using the visual basic editor.

 

[pinkpanther6666]

All,

Many Thanks to all who replied and this is how i sorted it ... hopefully i can explain it properly.

In my Report that's based on a query i had "[ Insert ID ]" which pops up a message box and i entered a condition value i wanted the query to extract on and that all worked
However i have to run this multiple time with conditional values so i took the step to automate it

So i deleted the [ Insert ID ] from my query and I created a macro (not VBA)


1.In the creation of this there is an command "Add New Actions and i have selected "Open a Report".
2.So i selected my report but there is also a option to put a "where Condition " and i have put [Main]![Name]=184.
3.I have also added another command to Email the Report.
4.I have also added another command to Close the Report.
5.I have Copy and Paste Steps 2 and 3 many times (In step 2 Changing the value in [Main]![Name] to another value and in step 3 changing email address).i i
6.I can Add/Delete as and when its required
6.The final command is to Close the Report.

The macro is saved, so all i have to do now is to execute the macro and it does it all automatically.


I hope this explains it ........... it might sound a bit basic but it works


On to my next problem .........having a Dlookup in a form which it pulls data from a query based on values in the form and query !!!!!!!!


Many Thanks Again


Steve