We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

ACCESS Queries from a form

taverny
taverny asked
on
Medium Priority
302 Views
Last Modified: 2012-05-11
Hi Experts,
I have about 10 queries that run everyday. In those queries everyday we change one field ( the date fields) then run it . I was able to create a form that calls the query and place the proper date into the query.
But my issue is how can I pass the same date to multiple queries from one macro.
I am sorry I forgot to mention that I would like to have a macro because the output is going to an excell spreadshit. or actually I am open to any suggestions as long as I can click one button put the date click ok , and then have the output in excell.
Thanks in Advance.



Comment
Watch Question

Commented:
This is all very doable, but it's a little tough without seeing the code.  If it isn't sensitive information, or you can create a quick example for me, post it I can set it up for you.

What I have in mind is the QueryDef object:

http://msdn.microsoft.com/en-us/library/bb177500%28v=office.12%29.aspx

Basically your button click will fire off a bit of code that will take the date entered and update each of the 10 querydefs.  With that, your queries will be updated, then you can move the data to excel.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you both for your answer.
mmr159, my query is a very basic query . it basically goes into a table and looks for all my jobs that are under wanrranty with some special criteria and a data range.
attached is the code. if you look in the code I have already linked the query to prompt me for the date range in a form with the same syntax suggested by Rachel.
Rachel , I did try your suggestion ( adding the 2 query under the button) and it did run correctly .
but how can have it to output them as an excel directly from the button. instead of the screen .

I have a similar macro that run each of the query and output them as an excel , is there a way to prompt me once and when I click ok to have the excel file created?
thanks again.
SELECT [JOB FOLDER].[JOB #], [JOB FOLDER].[REPAIR FACILITY], [JOB FOLDER].[DATE RECEIVED], [JOB FOLDER].[APPROVED DATE], [JOB FOLDER].[INVOICE DATE], [JOB FOLDER].[REPAIR CODE]
FROM [JOB FOLDER]
WHERE ((([JOB FOLDER].[JOB #]) Not Like "e-ds*") AND (([JOB FOLDER].[REPAIR FACILITY]) Like "padm" Or ([JOB FOLDER].[REPAIR FACILITY])="pbg" Or ([JOB FOLDER].[REPAIR FACILITY])="psg" Or ([JOB FOLDER].[REPAIR FACILITY])="ptg" Or ([JOB FOLDER].[REPAIR FACILITY])="pdah" Or ([JOB FOLDER].[REPAIR FACILITY])="pjp") AND (([JOB FOLDER].[DATE RECEIVED]) Is Not Null) AND (([JOB FOLDER].[APPROVED DATE]) Is Not Null) AND (([JOB FOLDER].[INVOICE DATE]) Between [Forms]![BCFormDavid]![Start Date] And [Forms]![BCFormDavid]![End Date]) AND (([JOB FOLDER].[REPAIR CODE]) Like "warranty*" Or ([JOB FOLDER].[REPAIR CODE])="courtesy"))
ORDER BY [JOB FOLDER].[REPAIR CODE];

Open in new window

Author

Commented:
well, I got some update , I found a post online that had a similar question. so it seems to be working , but I just wanted to check with you if I am omitting anything . and also I just thought about it , is it possible to have all the output of my query going to the same Excell spreadshieet but each one on a separate spreadsheet.
here is the code behind my buitton in the form.
Thanks


Private Sub OKButt_Click()
On Error GoTo Err_OKButt_Click

    Dim stDocName As String

    stDocName = "bcIncentElecInHouseWarranty"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "bcIncentElecInHouseWarranty", "c:\test.xls"
   
    stDocName = "bcIncentElecMotorWarranty"
    'DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "bcIncentElecMotorWarranty", "c:\test1.xls"
   
Exit_OKButt_Click:
    Exit Sub

Err_OKButt_Click:
    MsgBox Err.DESCRIPTION
    Resume Exit_OKButt_Click
   
End Sub
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Taverny, with all due respect, I'm a little unclear why I received an "assist" and mmr159 received the answer credit in this case, since I answered your posted question and he answered a followup additional request you had. Could you explain?

Thanks.
-Rachel

Author

Commented:
Hi Rachel,
Sorry I just saw that the system put "assisted" on your answer , your answer is supposed to be the main one and the one from mmr159 I guess should be assisted since he told me how to write on different sheet.
I never pay attention in the past that one is the main and the other one is assisted. Do I have a way to change it now?sorry it was not meant to be.
Thanks, Taverny - mistakes happen! I investigated and discovered we can use the "Request Attention" button so a moderator can resolve this, which I've now done, so hopefully a super-user can sort it out for us.

-Rachel

Author

Commented:
Perfect , if a moderator contact me I will make sure that the changes are correct.
Sorry again, and thanks for your help
David
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.