PatrickJohnson
Registered User.
- Local time
- Today, 08:43
- Joined
- Jan 26, 2007
- Messages
- 68
i have the following code which will basically has the user enter a start and end date, then runs a query using the start date as a criteria, then adds 1 to the start date and does it again, etc., until the end date is reached.
Function Float_Macro()
On Error GoTo Float_Macro_Err
Dim dateStartDate As Date
Dim dateEndDate As Date
DoCmd.SetWarnings False 'Turn off warnings
DoCmd.OpenQuery "GET Float Check Delete", acViewNormal, acEdit 'Clear Floats Table
dateStartDate = InputBox("Enter Sample Start Date", "Start Date", Date) 'User Input Box: Start Date
dateEndDate = InputBox("Enter Sample End Date", "End Date", dateStartDate + 365) 'User Input Box: End Date
Do While dateStartDate < dateEndDate 'Sets cutoff point for loop
SendKeys dateStartDate, False 'Puts user entered data for Start Date into Query Input Box
SendKeys "{ENTER}", False 'Chooses "OK" on Query Input Box
DoCmd.OpenQuery "GET Float Check Append", acViewNormal, acEdit 'adds data to list
dateStartDate = dateStartDate + 1 'Sets next sample date
Loop
DoCmd.OpenQuery "GET Float Time Card Entries Table", acViewNormal, acEdit 'Averages Float Data
Float_Macro_Exit:
Exit Function
Float_Macro_Err:
MsgBox Error$
Resume Float_Macro_Exit
End Function
The problem is the only way I've been able to make it work is the above, where a dialog pops up initally where the user enters start date, then another where they enter end date, then each time the query is run which has the report date as a user entered field, and i use the very inefficient SendKeys to populate the field when prompted and continue on.
My desired end result would be to have the user enter a start and end date, then run the query for every date including and between, appending each query result set to the previous in a table, then display the final table.
How can i make it step through and run the query using the input box date as a starting point but then not popping up the dialog box anymore?
Function Float_Macro()
On Error GoTo Float_Macro_Err
Dim dateStartDate As Date
Dim dateEndDate As Date
DoCmd.SetWarnings False 'Turn off warnings
DoCmd.OpenQuery "GET Float Check Delete", acViewNormal, acEdit 'Clear Floats Table
dateStartDate = InputBox("Enter Sample Start Date", "Start Date", Date) 'User Input Box: Start Date
dateEndDate = InputBox("Enter Sample End Date", "End Date", dateStartDate + 365) 'User Input Box: End Date
Do While dateStartDate < dateEndDate 'Sets cutoff point for loop
SendKeys dateStartDate, False 'Puts user entered data for Start Date into Query Input Box
SendKeys "{ENTER}", False 'Chooses "OK" on Query Input Box
DoCmd.OpenQuery "GET Float Check Append", acViewNormal, acEdit 'adds data to list
dateStartDate = dateStartDate + 1 'Sets next sample date
Loop
DoCmd.OpenQuery "GET Float Time Card Entries Table", acViewNormal, acEdit 'Averages Float Data
Float_Macro_Exit:
Exit Function
Float_Macro_Err:
MsgBox Error$
Resume Float_Macro_Exit
End Function
The problem is the only way I've been able to make it work is the above, where a dialog pops up initally where the user enters start date, then another where they enter end date, then each time the query is run which has the report date as a user entered field, and i use the very inefficient SendKeys to populate the field when prompted and continue on.
My desired end result would be to have the user enter a start and end date, then run the query for every date including and between, appending each query result set to the previous in a table, then display the final table.
How can i make it step through and run the query using the input box date as a starting point but then not popping up the dialog box anymore?