Hello, I would like to use a Combo or Text box to control values in my SQL Queries, but either
have not been able to figure out the format, or I am taking the wrong course?
I do program a little in VB6, but Access leaves me somewhat befuddled at times.
What I want is the Query to use the contents of a combo or textbox control, to set
the parameter for the table field in a select statement. Now I know this doesn't work,
but I will include it, in hopes that it will explain what I am trying to do.
SELECT [SORDERS].[SS], [SORDERS].[NAME], [SORDERS].[DOB], [SORDERS].[Form.cboMonth.text]
FROM SORDERS
WHERE [SORDERS].[Form.cboMonth.text] Is Not Null
ORDER BY [SORDERS].[SS];
The people I am working on this database for, have taken it upon themselves to work
all of these year in spreadsheets, and they have columns for each month (JAN,FEB,MAR,etc..)
and in each field a numeric value for the date the transaction will take place. I would
like the user to be able to select the month from a combo box, which would defined which
month the query is supposed to collect data from. The "WHERE" portion of the query to
to leave out any records which have a NULL or emtpy value in the Month column.
PLEASE NOTE** I am a beginner to MS ACCESS, and SQL. Although I have worked in VB6
I have not done much database stuff other than populating Data & FlexGrids and etc..
This is a learning process, but I have reached a wall on this problem. I have been trying
for 3 days to find an example or article that would educate me on how to handle this,
but have not yet yielded the proper "text search" in google to find anything.
Thanks for any help you can offer
Add Microsoft DAO 3.6 Library to your references. In VB Editor, click on References and scroll down to DAO 3.6 and click.
Change the name of QRY_JAN to QRY_MONTH
Add this code to the existing code in the afterUpdate event of cboMonth:
' This code will change the month referenced in the sqlstring in the query
' QRY_MONTH to what ever has been selected in cboMonth of the form
' Reporting. If the form has not been opened, the query will run with whatever
' month was previously selected.
Dim mydb As database, mysql As String, myqd As querydef
Set mydb = CurrentDb
Set myqd = mydb.QueryDefs("qry_month"
mysql = myqd.sql
mysql = Replace(mysql, _
Mid(mysql, InStr(1, mysql, "where") + 17, 3), _
Forms!reporting!cboMonth.C
myqd.sql = mysql
mydb.QueryDefs.Refresh
Set myqd = Nothing
Set mydb = Nothing
End Sub
Have a try and let me know. Remember, open the form first, select a month, and then run the query. Presto!