Using combo box(s) to create a query

[Repent]

I have a two part question as follows:

1st part:
I am wanting to create a query that is driven by the user selecting a
value from a combo box on a form.

the combo box on the form is called "TypeofIssueID" and it's control
source is "TypeofIssueID" in a table called"tblEngineering".

it would be nice if when the user selects the value he wants to query
from the combo box the query is automatically generated at that point.
the user wouldn't have to select a "do this now" button.

2nd part:
the second part to this would be adding more than one combo box that a
user would use to select a query. How do I add more than one combo
box to generate the query? The query, like above, would run after the
user selected the last value in the last combo box.

How can I do this?

thanks in advance to all;

chris

 

[Crystal (strive4peace)]

Hi Chris,

if you are using the control to select something, the
ControlSource should probably be blank so it does not change
data in your table.

1. You can use the AfterUpdate event to create/change the
query and open it -- or you can show data on the form or
subform and have the AfterUpdate event apply a filter

2. Again, use the AfterUpdate event of each control. I like
to allow the user to pick nothing so you may want to do
whatever (either build query or filter form/subform)

Here is code if you want to change or create a query:


'~~~~~~~~~~~~~~~~~~~~~ MakeQuery
Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 3-30-08
'crystal
'strive4peace2009 at yahoo dot com

On Error GoTo Proc_Err

debug.print pSql

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
'if query is open, close it
on error resume next
DoCmd.Close acQuery, qName, acSaveNo
On Error GoTo Proc_Err
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

Resume Proc_Exit

'if you want to single-step code to find error,
CTRL-Break at MsgBox
'then set this to be the next statement
Resume
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

to use the MakeQuery procedure, put this in your code:

MakeQuery strSQL, "YourQueryName"


Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
have an awesome day
*