Using combo box(s) to create a query



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;


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
'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
'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

Exit Sub

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
End Sub

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

MakeQuery strSQL, "YourQueryName"

Warm Regards,
remote programming and training

free video tutorials

Access Basics
free 100-page book that covers essentials in Access

:) have an awesome day :)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question