Hi Sheree,
Try the code below...
My (pre-existing form) contains 3 listboxes and a command button, based (for
the most part) on the instructions in this KB article:
http://support.microsoft.com/default...b;en-us;124344
lstObjectTypes
lstObjectNames
lstFieldNames
cmdOpenQuery
This approach is optional ...
probably all that you really need is "lstFieldNames", which is a
multi-select listbox containing the table's field names,
and the command button named "cmdOpenQuery".
The " lstFieldNames_AfterUpdate()" event builds a query named "qryTemp",
and "cmdOpenQuery_Click()" opens the query in datasheet view.
*******************************************
Private Sub lstFieldNames_AfterUpdate()
'Requires: IsTableQuery() function
'http://support.microsoft.com/default.aspx?scid=kb;en-us;113549
'Optional: How to Fill a List Box with Database Object Names
'http://support.microsoft.com/default.aspx?scid=kb;en-us;124344
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim qdfTemp As QueryDef
Dim ctl As Control
Set ctl = Me.lstFieldNames
Dim MyTable As String
MyTable = Me.lstObjectNames
'This is the optional listbox containing table names.
'You could just use your table name directly
Dim varItm As Variant
Dim strSelected As String
Dim Msg As String
Dim MySQL As String
Dim CR As String
CR = vbCrLf
If IsTableQuery("", "qryTemp") Then
MyDB.QueryDefs.Delete ("qryTemp")
End If
For Each varItm In ctl.ItemsSelected
If Len(strSelected) > 0 Then
strSelected = strSelected & ", " & ctl.ItemData(varItm)
Else
strSelected = ctl.ItemData(varItm)
End If
Next varItm
MySQL = ""
MySQL = MySQL & "SELECT "
MySQL = MySQL & strSelected
MySQL = MySQL & " FROM "
MySQL = MySQL & MyTable
MySQL = MySQL & "; "
Set qdfTemp = MyDB.CreateQueryDef("qryTemp", MySQL)
Set ctl = Nothing
Set MyDB = Nothing
Set qdfTemp = Nothing
End Sub
Private Sub cmdOpenQuery_Click()
DoCmd.OpenQuery ("qryTemp")
End Sub
*******************************************
HTH,
Don
========================================
"sheree" <sa***@case.edu> wrote in message
news:f8*************************@posting.google.co m...
I would like to create a query where one of the columns of the queries
comes from a combo list box on a form.
For example, if my table has the following fields:
id
name
interest1
interest2
interest3
my combo box would be a dropdown containing these choices:
interest1
interest2
interest3
when the user selects one these in the combo box, then the following
query executes:
select id, name, [interestselectedfrombox]
from mytable
where blah = blah
ex: they select interest3 from the combo box, then the query that
runs is:
select id, name, interest3
from mytable
where blah = blah
i've tried variants on this, but none of them have worked so far. i
need some help!!!!! if anyone could show me some specific syntax
examples, that would be great.