Problem Opening Queries in Access 2007

[kmartinenko]

Hello all,

My system was recently "upgraded" to MS Office 2007 and now I am having a really frustrating time trying to get my simple command button to open a query.

This is the same database I posted a couple of questions about back in January. The VBA code for the two combo boxes and the subsequent command button is as follows:

 Private Sub route_combo_AfterUpdate()
Me.timepoint_combo.Requery
End Sub

Private Sub Search_Click()
DoCmd.OpenQuery , "Query1", acViewNormal

End Sub

Private Sub timepoint_combo_AfterUpdate()
CurrentDb.QueryDefs("Query1").SQL = "SELECT * FROM [RideData] WHERE ((([RideData].[ROUTE])=[Forms]![Form1]![route_combo]))AND((([RideData].[TRIP])=[Forms]![Form1]![timepoint_combo] ORDER BY [RideData].[ROUTE] ASC;"
End Sub

Two main issues is that Access 2007 won't let me create an empty "Query1" without asking for at least one destination field. I was given the solution on this forum to use the above CurrentDb.Query Defs function in conjunction with creating a Query1 query without anything in it. Nice simple work around, but its not working now! And, I think this is why its not "behaving" properly, because I could do this in Access 2000, and it worked fine in 2003, so do I need to use a different syntax for 2007?

Thanks,

[kmartinenko]

 Private Sub route_combo_AfterUpdate()
Me.timepoint_combo.Requery
End Sub

Private Sub Search_Click()
DoCmd.OpenQuery , "Query1", acViewNormal

End Sub

Private Sub timepoint_combo_AfterUpdate()
CurrentDb.QueryDefs("Query1").SQL = "SELECT * FROM [RideData] WHERE ((([RideData].[ROUTE])=[Forms]![Form1]![route_combo]))AND((([RideData].[TRIP])=[Forms]![Form1]![timepoint_combo] ORDER BY [RideData].[ROUTE] ASC;"
End Sub

Two main issues is that Access 2007 won't let me create an empty "Query1" without asking for at least one destination field. I was given the solution on this forum to use the above CurrentDb.Query Defs function in conjunction with creating a Query1 query without anything in it. Nice simple work around, but its not working now! And, I think this is why its not "behaving" properly, because I could do this in Access 2000, and it worked fine in 2003, so do I need to use a different syntax for 2007?

Thanks,

I figured it out. If anyone ever comes across this problem, you may find it useful to know that in Access 2007, they don't require extraneous code in VBA. Instead, you can simply insert your WHERE SQL statement within a "Query1", so I created a Query1, and the SQL statement is as follows:

SELECT RideData.ROUTE, RideData.DIR, RideData.TRIP, RideData.TIMEPOINT, RideData.SCHEDULED_TIME, RideData.[ON], RideData.[OFF], RideData.SCHEDULE_DEVIATION, RideData.SCHEDULED_RUNTIME, RideData.ACTUAL_RUNTIME, RideData.DELTA_RUNTIME
FROM RideData
WHERE (((RideData.ROUTE)=Forms!Form1!route_combo) And ((RideData.TRIP)=Forms!Form1!timepoint_combo));

Then, simply create a command button using the wizard, select "Run Query" and associate the command button with the query you created.

--Kindra