Displaying Results from a Query using a Form

Hello,

I am in need of some serious help with my Access 2000 database.

I have created a form with two combo boxes: stops_combo and stoptime_combo. When a bus stop is selected from stops_combo, the stoptime_combo box is populated with the stop times for the stop selected.
I've got that down, and it seems to work fine.

Now, I want to create a new table out of these selections. Ideally, I want to be able to click on a command button and up will pop a new table displaying the stop and stop times associated with that stop.

I am assuming that I need to somehow link up an SQL statement to a DoCmd in VBA, but I am not sure how to do it.

I am very new to VBA...I feel like I know just enough that I am stuck in a rut and trying to figure this out is driving me crazy! :-)

Thanks,

Kindra

Hello, Kindra!

Are you able to post some of your coding for a closer look?

I have some ideas, perhaps other experts here, I am trying not to spin my wheels trying to give you the good word. Post a portion of the code, at least what is not working or what you hope to achieve, we'll have a look:-)

Just like you, I am new to VBA, still I assume it is pretty simple perhaps your code can help shed some light...

In a bit!

Okay, the only code I have thus far is in SQL and AfterUpdate code in VBA for the first combo box.

Here is what the VBA code looks like for the first combo box, stops_combo:

Expand|Select|Wrap|Line Numbers

1. Private Sub stops_combo_AfterUpdate()
2. Me.stoptime_combo.Requery
3. End Sub

And the SQL Statement in the Rowsource for the stops_combo box is simply:

Expand|Select|Wrap|Line Numbers

1. SELECT stopquery.STOP
2. FROM stopquery;
3.  

For the second combo box stoptime_combo I have a the following SQL in the Rowsource:

Expand|Select|Wrap|Line Numbers

1. SELECT DISTINCT [Rte1stoptimes].[STRTIME] FROM Rte1stoptimes WHERE ((([Rte1stoptimes].[STOP])=[Forms]![RouteInfo]![stops_combo])) ORDER BY [Rte1stoptimes].[STRTIME] DESC; 

Again, after the selections have been made from stops_combo and stoptimes_combo, I want to be able to click a command button that generates a new table showing the search results from the selections made. Thanks in advance for the help!

--Kindra

I would create and save a simple "query1" doesn't matter whats in it.
then add this code to your second combo box event:

CurrentDb.QueryDefs("Query1").SQL = "SELECT DISTINCT [Rte1stoptimes].[STRTIME] FROM Rte1stoptimes WHERE ((([Rte1stoptimes].[STOP])=[Forms]![RouteInfo]![stops_combo])) ORDER BY [Rte1stoptimes].[STRTIME] DESC; "

then add this to you command button click event:
DoCmd.OpenQuery "Query1", acViewNormal

There are other ways to do this but creating a saved query is the best if you later decide you want a report and another form to use the same data.

I would create and save a simple "query1" doesn't matter whats in it.
then add this code to your second combo box event:

CurrentDb.QueryDefs("Query1").SQL = "SELECT DISTINCT [Rte1stoptimes].[STRTIME] FROM Rte1stoptimes WHERE ((([Rte1stoptimes].[STOP])=[Forms]![RouteInfo]![stops_combo])) ORDER BY [Rte1stoptimes].[STRTIME] DESC; "

then add this to you command button click event:
DoCmd.OpenQuery "Query1", acViewNormal

There are other ways to do this but creating a saved query is the best if you later decide you want a report and another form to use the same data.