Same answer

[Emmanuel]

Dear Friends,

I have a question in regards this code which is a macro converted to VBA. Two of this Queries have a criteria which ask the user between what date and what other date is going to retreve the info. Sales and also credits. The thing is that although they are two diferent queries the answer t both should be the same. For example if the answer to the first query is from 03/01/2003 to 03/31/2003 it should be the same to the next question. Is there a command that I can use to tell the system to use the same answer to the first question on the second one? Attached an example of the VBA code. Any suggestions will be very well received.


Private Sub Command74_Click()


DoCmd.DeleteObject acTable, "NET SALES BY ITEM"
DoCmd.DeleteObject acTable, "PESO EN RETAIL"
DoCmd.DeleteObject acTable, "Tbl-Item_class"
DoCmd.DeleteObject acTable, "U Of M Conv"
DoCmd.DeleteObject acTable, "U Of M Conv DISPLAYS"
DoCmd.DeleteObject acTable, "U Of M Conv RETAIL"
DoCmd.DeleteObject acTable, "Final Sales"
DoCmd.DeleteObject acTable, "Final Returns"
DoCmd.DeleteObject acTable, "NET SALES BY ITEM DETAIL"
DoCmd.OpenQuery "A-Convertion Credits For Displays", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "A-Convertion Credits For Displays"
DoCmd.OpenQuery "B-UOM IN RETAILS", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "B-UOM IN RETAILS"
DoCmd.OpenQuery "B2-UOM IN RETAIL FOR DISPLAYS", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "B2-UOM IN RETAIL FOR DISPLAYS"
DoCmd.OpenQuery "C-Create Item Class", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "C-Create Item Class"
DoCmd.OpenQuery "D-Create pesos", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "D-Create pesos"
DoCmd.OpenQuery "D2-UPDATE 89948 & 89949", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "D2-UPDATE 89948 & 89949"
DoCmd.CopyObject "", "NET SALES BY ITEM", acTable, "SAMPLE NET SALES BY ITEM"
DoCmd.CopyObject "", "NET SALES BY ITEM DETAIL", acTable, "SAMPLE NET SALES BY ITEM DETAIL"
DoCmd.OpenQuery "K-Total Sales", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "K-Total Sales"
DoCmd.OpenQuery "I-TOTAL RETURNS", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "I-TOTAL RETURNS"
DoCmd.OpenQuery "L-APPEND SALES TO NET TBL", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "L-APPEND SALES TO NET TBL"
DoCmd.OpenQuery "M-APPEND CREDITS TO NET TBL", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "M-APPEND CREDITS TO NET TBL"
DoCmd.OpenQuery "Update Date & Time", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "Update Date & Time"
DoCmd.OpenQuery "APPEND YTD SALES", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "APPEND YTD SALES"
DoCmd.OpenQuery "P-APPEND SALES DETAIL TO NET TBL2", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "P-APPEND SALES DETAIL TO NET TBL2"
DoCmd.OpenQuery "Q-APPEND CREDITS DETAIL TO NET TBL2", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "Q-APPEND CREDITS DETAIL TO NET TBL2"
DoCmd.OpenQuery "APPEND DETAIL TO HISTORY", acViewDesign, acReadOnly
DoCmd.RunCommand acCmdRun
DoCmd.Close acQuery, "APPEND DETAIL TO HISTORY"


End Sub

DoCmd.OpenQuery "K-Total Sales", acViewDesign, acReadOnly DoCmd.OpenQuery "I-TOTAL RETURNS", acViewDesign, acReadOnly

 

[Mile-O]

You'll find it easier to accomplish if you allow the user to set these dates on a form rather than type them in as the query runs.

That way you can reference the controls on the form that hold the dates and will run the query without asking for dates at all.

 

[Emmanuel]

RE:HELP

Thanks for your help I will do as you suggest.

But question.... Can this reference to the form be done on the criteria?

Emmanuel

 

[Mile-O]

Yes,

e.g. [Forms]![frmYourForm]![ctlYourControl] is a valid criteria

 

[jfgambit]

Yes....

Set the criteria of the field to:

Between [Forms]![frmNameofForm]![1stFieldonForm] And [Forms]![frmNameofForm]![2ndFieldonForm]

As long as the form is open at the time of running you will get your date ranges.

HTH

 

[Emmanuel]

RE: hELP

Thanks for your help....


Emmanuel


P.D. There is only one true power....... Knowledge and sharing it is the only way to greatness.....