Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to write a process to run several prewritten Queries in a row without any human interaction. I tried to write a macro, but the after saving it it still prompted for the date range in the query. The people who came before me, created a switchboard form with the correct date ranges on it. Is there a way to pull the dates with VBA and run the query without any user interaction. I am new to VBA. and have tried everything i know and have found in the on. I asked the question on stack exchange and they had some sql i did not understand and did not work. I keeping error messages when i run the code from below. Any help would be great i am at a standstill. I am using access 2010 Thanks for your time.

Sub ModQueryDefs_OpenPramaeters()

Dim db As DAO.Database
Dim qdef As DAO.QueryDef
Dim rst As DAO.Recordset
Set db = CurrentDb

DoCmd.OpenForm "frmSwitchboard"
[Forms!] [frmSwitchboard]![txtDateStart] = 11 / 5 / 2012
[Forms!] [frmSwitchboard]![txtDateEnd] = 11 / 11 / 2012

Set qdef = db.QueryDefs("query")
Dim param As DAO.Parameter
For Each param In qdef.Parameters
Debug.Print param.Name; param.Value
param.Value = Eval(param.Name)

Next
Set rst = qdef.Openrecordset()
Do While Not rst.EOF
Debug.Print rst!Company
rst.MoveNext
End Sub
share|improve this question
1  
add a # on each side of the dates above and remove the spaces like #11/20/2012#. –  Max Vernon Nov 20 '12 at 18:30
    
and add rst.Close on a new line just before End Sub –  Max Vernon Nov 20 '12 at 18:32
    
Surely this is Stackoverflow not Database Administrators? –  Remou Nov 20 '12 at 19:06
    
You should post the sql for [NWF CC DETAIL WEEKLY] –  Remou Nov 20 '12 at 19:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.