need help with macro run in MS Access

I have three queries and one module. I need to run in sequence:
Query 1
Module : function name let say CreateList
Query2
Query3

openquery
??? run module----------> runcode(CreateList)?
openquery
openquery

I am trying to create a macro that will run a module
What should i do? Please need your help
Roman FAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Create a routine like:

Sub RunMyQueries()

Dim i
openquery1
i =   runcode(CreateList)
openquery2
openquery3

End sub

Now, in your macro include RunMyQueries()
PatHartmanCommented:
Macros can only run Functions.  They cannot run Procedures.  Nothing ever runs Modules.
Eric ShermanAccountant/DeveloperCommented:
You can use the RunCode action to call a Visual Basic for Applications (VBA) Function procedure.

Action:
RunCode  

Argument:
Createlist()

ET
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Roman FAuthor Commented:
Thank you all for your respond

PatHartman:  thank you, i do know that, what is the solution
etsherman: thank you, i assume that, that is why i put in the description
eghtebas: thank you, did not get that... need more to know
Eric ShermanAccountant/DeveloperCommented:
<<<<<etsherman: thank you, i assume that, that is why i put in the description>>>>>

I am not sure what you are asking ... Why not just run the 3 queries inside your VBA Function???
You can then run CreateList() from a Macro or using the Call CreateList in VBA code.

Function CreateList()
DoCmd.OpenQuery "Query1"

******************************
Your VBA Code for CreateList Here
******************************

DoCmd.OpenQuery "Query2"
DoCmd.OpenQuery "Query3"

End Function
Roman FAuthor Commented:
ok,
my Module name is RunHEER3%
sub name is CreateList
my Macros name is 01_HEER Run

what should i put in
******************************
Your VBA Code for CreateList Here
******************************
PatHartmanCommented:
As I said earlier - A sub is not a function.  macro
Eric ShermanAccountant/DeveloperCommented:
When you examine your objects ... do they look like what's in the attached picture???

Sample Module/Macro

ET
Roman FAuthor Commented:
Option Compare Database

Sub CreateRandomHEERList()
    Dim rsUniqueNames As Recordset
    Set rsUniqueNames = DBEngine(0)(0).OpenRecordset("Select Distinct [Processor Long Name] From tblHEER_QC_Selection")
    Do Until rsUniqueNames.EOF
        DBEngine(0)(0).Execute "Insert Into Top3PctHEER (ID, [Activity ID Key], [Processor Long Name], [Date Closed], Picked, DatePicked,DateLoaded,DateQCCompleted) " & _
            "SELECT TOP 3 PERCENT ID, [Activity ID Key], [Processor Long Name], [Date Closed], Picked, DatePicked, DateLoaded,DateQCCompleted " & _
            "FROM [tblHEER_QC_Selection] " & _
            "WHERE [tblHEER_QC_Selection].Picked =False And [tblHEER_QC_Selection].[Processor Long Name]='" & rsUniqueNames![Processor Long Name] & "' " & _
            "ORDER BY RandomNumber(ID);"
       
        Debug.Print "Rows Inserted for "; rsUniqueNames![Processor Long Name] & ": " & DBEngine(0)(0).RecordsAffected
        rsUniqueNames.MoveNext
    Loop
End Sub

Public Function RandomNumber(parmDummy) As String
  ' Generate and return a random number.
    RandomNumber = Mid(CreateObject("scriptlet.typelib").Guid, 2, 36)
End Function


Function CreateList()
DoCmd.OpenQuery "Query1"

'
what should be in here???
'

DoCmd.OpenQuery "Query2"
DoCmd.OpenQuery "Query3"

End Function
Eric ShermanAccountant/DeveloperCommented:
Try this ...  In order to run VBA code (in a Macro) it needs to be a Function not a Sub as Pat and myself keep pointing out.

Public Function CreateRandomHEERList()

'************************
DoCmd.OpenQuery "Query1"

     Dim rsUniqueNames As Recordset
     Set rsUniqueNames = DBEngine(0)(0).OpenRecordset("Select Distinct [Processor Long Name] From tblHEER_QC_Selection")
     Do Until rsUniqueNames.EOF
         DBEngine(0)(0).Execute "Insert Into Top3PctHEER (ID, [Activity ID Key], [Processor Long Name], [Date Closed], Picked, DatePicked,DateLoaded,DateQCCompleted) " & _
             "SELECT TOP 3 PERCENT ID, [Activity ID Key], [Processor Long Name], [Date Closed], Picked, DatePicked, DateLoaded,DateQCCompleted " & _
             "FROM [tblHEER_QC_Selection] " & _
             "WHERE [tblHEER_QC_Selection].Picked =False And [tblHEER_QC_Selection].[Processor Long Name]='" & rsUniqueNames![Processor Long Name] & "' " & _
             "ORDER BY RandomNumber(ID);"
         
         Debug.Print "Rows Inserted for "; rsUniqueNames![Processor Long Name] & ": " & DBEngine(0)(0).RecordsAffected
         rsUniqueNames.MoveNext
     Loop


'**************************
DoCmd.OpenQuery "Query2"


'***************************
DoCmd.OpenQuery "Query3"

End Function



Then use the RunCode Action in your Macro:

Action:
RunCode  

Argument:
CreateRandomHEERList()

ET
Roman FAuthor Commented:
Option Compare Database

Public Function CreateRandomHEERList()
   
    DoCmd.OpenQuery qryUppendNew
   
    Dim rsUniqueNames As Recordset
    Set rsUniqueNames = DBEngine(0)(0).OpenRecordset("Select Distinct [Processor Long Name] From tblHEER_QC_Selection")
    Do Until rsUniqueNames.EOF
        DBEngine(0)(0).Execute "Insert Into Top3PctHEER (ID, [Activity ID Key], [Processor Long Name], [Date Closed], Picked, DatePicked,DateLoaded,DateQCCompleted) " & _
            "SELECT TOP 3 PERCENT ID, [Activity ID Key], [Processor Long Name], [Date Closed], Picked, DatePicked, DateLoaded,DateQCCompleted " & _
            "FROM [tblHEER_QC_Selection] " & _
            "WHERE [tblHEER_QC_Selection].Picked =False And [tblHEER_QC_Selection].[Processor Long Name]='" & rsUniqueNames![Processor Long Name] & "' " & _
            "ORDER BY RandomNumber(ID);"
       
        Debug.Print "Rows Inserted for "; rsUniqueNames![Processor Long Name] & ": " & DBEngine(0)(0).RecordsAffected
        rsUniqueNames.MoveNext
    Loop
    DoCmd.OpenQuery qryUpdateTop3HEER
    DoCmd.OpenQuery qryUpdateTOP3HEER_History
   
   
End Function

Public Function RandomNumber(parmDummy) As String
  ' Generate and return a random number.
    RandomNumber = Mid(CreateObject("scriptlet.typelib").Guid, 2, 36)
End Function










no idea what is going on, it stops at ss.jpg
PatHartmanCommented:
Looks like a phantom break point.  Just press the right triangle to continue.
Roman FAuthor Commented:
did not get that, sorry
Eric ShermanAccountant/DeveloperCommented:
The query name is Text and should be enclosed in quotes as shown below ... My fault ... I left those out.

DoCmd.OpenQuery "qryUppendNew"

 DoCmd.OpenQuery "qryUpdateTop3HEER"
 DoCmd.OpenQuery "qryUpdateTOP3HEER_History"


ET
Roman FAuthor Commented:
great, works great
One little thing, how to get rid of those confirmation windows,
where should i put
Setwarning (No)
PatHartmanCommented:
Add the following two lines at the beginning of the procedure - before the existing code.
    DoCmd.SetWarnings False
    DoCmd.Hourglass True

Open in new window

Then as the last two lines - after the existing code.
    DoCmd.SetWarnings True
    DoCmd.Hourglass False

Open in new window


I set the hourglass on when the warnings are off so that I have a visual clue that warnings are off.  I cannot over emphasize how dangerous it is to leave warnings off when you are developing.  If you accidentally close a form you just spent 4 hours modifying, your changes will be silently discarded if warnings are off.
Eric ShermanAccountant/DeveloperCommented:
Pat answered that one for you ... Glad it is working.

ET
Roman FAuthor Commented:
I would to thank everybody for the help, it is hard..
Eric ShermanAccountant/DeveloperCommented:
Ok, are you going to award points and close the question???

ET

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roman FAuthor Commented:
Thank you very much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.