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
5 2092
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!
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: -
Private Sub stops_combo_AfterUpdate()
-
Me.stoptime_combo.Requery
-
End Sub
And the SQL Statement in the Rowsource for the stops_combo box is simply: -
SELECT stopquery.STOP
-
FROM stopquery;
-
For the second combo box stoptime_combo I have a the following SQL in the Rowsource: -
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
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: -
Private Sub stops_combo_AfterUpdate()
-
Me.stoptime_combo.Requery
-
End Sub
And the SQL Statement in the Rowsource for the stops_combo box is simply: -
SELECT stopquery.STOP
-
FROM stopquery;
-
For the second combo box stoptime_combo I have a the following SQL in the Rowsource: -
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.
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!
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!
Sign in to post your reply or Sign up for a free account.
Similar topics
by: jaysonsch |
last post by:
Hello!
I am having some problems with a database query that I am trying to do.
I am trying to develop a way to search a database for an entry and
then edit the existing values. Upon submit, the...
|
by: Matt K. |
last post by:
Hi there,
I have a form in an Access project that contains a subform which
displays the results of a query of the style "select * from
where = #a certain date#". In the main part of the form...
|
by: Greg Bale |
last post by:
Hi.
(this is s repeat post from MS community newsgroup)
I am building a form for people to use to analyse the data
in my Access2000 database. The SQL is built up in VBA
depending on control...
|
by: Stuart Clark |
last post by:
Hiya
I'm learning ASP using Access and Dreamweaver. I've just started
simple and I've tried to make the db show the results of just two
tables without doing anything clever! I have the following...
|
by: Joe |
last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource
(using Oracle ODBC drivers). After linking the tables in Access, I inspect
the data contained in the linked tables. For...
|
by: NasirMunir |
last post by:
I have created a table in access (copied from excel). Then I created a form which contains a text field and a list box. The text field is actually a look-up field, where a user can enter a searchable...
|
by: dba |
last post by:
Have been displaying data from database using html for some time but
just recently trying to display data back to "form". Can't find answer.
<form method="post" action="<?php echo $PHP_SELF;?>">...
|
by: Nightcrawler |
last post by:
I have a website that does the following:
1. it accepts a keyword through a textbox in the UI
2. once the submit button is clicked it goes out and spiders a few
websites using the keyword...
|
by: ejamnadas |
last post by:
I have a form which combo boxes (say cmb1 and cmb2). The form also has a subform with a listbox whose rowsource is a query based on the values selected in the comboboxes.
In the After Update event...
|
by: Del |
last post by:
Hello and thanks for any and all assistance!
I have a database that is used by several users on several different
machines.
The backend database is housed on a file server.
Each user has a...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
| |