• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

ACCESS Queries from a form

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.



0
taverny
Asked:
taverny
  • 4
  • 4
  • 2
3 Solutions
 
mmr159Commented:
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.
0
 
RemRemRemCommented:
Instead of filling in the date via macro, set the date value (or it's criteria, as the case may be) equal to the date field on the form you've created. The syntax would be something like:

MyDate: Forms!frmName!DateFieldName

if in a field return value. Or, for a criteria:

=[Forms]![frmName]![DateFieldName] in the criteria field.

In some cases, it works more smoothly to turn the value into a function and make that function the value instead. In this case, open up a module and create:


Public Function fnMyDate() As Date
    fnMyDate = Forms!frmName!DateFieldName
End Function

...then in the queries fields or criteria values, you would make it:

MyDate: fnMyDate()
or
=fnMyDate() in the criteria

To get all of the queries to run, you can put a button on your form and in its OnClick event put:
Private Sub (ButtonName)_Click()
    DoCmd.OpenQuery "qryNameOne"
    DoCmd.OpenQuery "qryNameTwo"
    DoCmd.OpenQuery "qryNameThree"
...etc
End Sub

Obviously, replace the "qryNameOne" and so on with your actual query names.

-Rachel
0
 
tavernyAuthor 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

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tavernyAuthor 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
0
 
mmr159Commented:
You can specify the sheet by using the Range parameter:

Range = "sheet_name!"
0
 
RemRemRemCommented:
Taverny, you got the right code - the string of code you need is:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "bcIncentElecMotorWarranty", "c:\test1.xls"
   

Although you may want to use a later version than acSpreadsheetTypeExcel97 and this assumes your query is called "bcIncentElecMotorWarranty" and you're dropping it to a file called "c:\test1.xls"

-Rachel
0
 
RemRemRemCommented:
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
0
 
tavernyAuthor 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.
0
 
RemRemRemCommented:
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

0
 
tavernyAuthor Commented:
Perfect , if a moderator contact me I will make sure that the changes are correct.
Sorry again, and thanks for your help
David
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now