By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Displaying Results from a Query using a Form

kmartinenko
P: 12
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
Jan 10 '08 #1
Share this Question
Share on Google+
5 Replies


Dököll
Expert 100+
P: 2,364
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!
Jan 11 '08 #2

kmartinenko
P: 12
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
Jan 11 '08 #3

P: 45
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.
Jan 11 '08 #4

kmartinenko
P: 12
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.
Thank you for this explanation! This is getting *close* to what I am after, except that I want all fields to be displayed from the Rte1stoptimes table for both the stop and stop time selected from the combo boxes...but I think I can tailor the code above to meet those specs. It was the CurrentDb.QueryDefs("Query1").SQL statement that I was looking for...

Many thanks!
Jan 11 '08 #5

kmartinenko
P: 12
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.
Update:

SUCCESS!!! I modified the above CurrentDb.QueryDefs code to return all records matching the combo box selections and it worked perfectly. Thanks again for all of your help! This made my Friday a lot happier!
Jan 11 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.