Access 2000: how to create a query based on a form parameter -- Please HELP!!

sheree

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.

Nov 13 '05 #1

hamking

Seems like you should create a table for different type of "interests"

Create a subform [b] of the query you want, and place it on main form
[A]. For subform [b], Record Source click the query builder and drag
down the columns you want for your query. For column "interest" the
criteria should be [Forms]![A]![interestselectedfrombox]

For your comboBox, [interestselectedfrombox], make it unbound, the
AfterUpdate event should have the following:
[Forms]![A]![interestselectedfrombox].Requery
sa***@case.edu (sheree) wrote in message news:<f8*************************@posting.google.c om>...

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.

Nov 13 '05 #2

Pieter Linden

sa***@case.edu (sheree) wrote in message news:<f8*************************@posting.google.c om>...

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.

I'll hazard a guess. If you mean you want to show the id, name, and
"interest selected" from a table, then it's:

SELECT id, name, interest
FROM mytable
WHERE interest = Forms![MyOpenForm]![cboInterest];

where cboInterest is the name of the combobox control on your form...
or is that not what you meant?

Nov 13 '05 #3

sheree

The values in the combobox are actually FIELD NAMES -- not data for a
given field. So, in your code example, I really would want something
like:
SELECT id, name, Forms![MyOpenForm]![cboInterest]
FROM mytable;
But I can't seem to get this to work. Do you think it's not possible
to use a parameter for one of the select field names?

Thanks for your help!

pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...

sa***@case.edu (sheree) wrote in message news:<f8*************************@posting.google.c om>...

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.

I'll hazard a guess. If you mean you want to show the id, name, and
"interest selected" from a table, then it's:

SELECT id, name, interest
FROM mytable
WHERE interest = Forms![MyOpenForm]![cboInterest];

where cboInterest is the name of the combobox control on your form...
or is that not what you meant?

Nov 13 '05 #4

Malcolm Cook

it is possible

in the comboboxes after_update handler you have to build the query as a
string and assigne it to the form's recordsource, like this

assuming that cboInterest is a control on the form MyOpenForm, you would
write:

Private Sub cboInterest_AfterUpdate()
with me
.form.RecordSource = "SELECT id, name, " & .cboInterest.value & "
FROM mytable"
end with
End Sub

you might alternatively consider keeping the form's record source constant
and instead hide columns (if you are displaying results in datasheet mode)
that you don't want to show, something like this:

Private Sub cboInterest_AfterUpdate()
dim ctl as variant
with me
for each ctl in array(.ctl1, .ctl1, .ctl3 ....) 'list all your
interest controls here
ctl.controlhidden = ctl.name = .cboInterest.value
next
end with
End Sub
However, I agree with the other posters that your model is probably wrong
and that you really want to normalize the interests into another table.

Regards,

--
Malcolm Cook - me*@stowers-institute.org
Database Applications Manager - Bioinformatics
Stowers Institute for Medical Research - Kansas City, MO USA

"sheree" <sa***@case.edu> wrote in message
news:f8*************************@posting.google.co m...

The values in the combobox are actually FIELD NAMES -- not data for a
given field. So, in your code example, I really would want something
like:
SELECT id, name, Forms![MyOpenForm]![cboInterest]
FROM mytable;
But I can't seem to get this to work. Do you think it's not possible
to use a parameter for one of the select field names?

Thanks for your help!

pi********@hotmail.com (Pieter Linden) wrote in message

news:<bf**************************@posting.google. com>...

sa***@case.edu (sheree) wrote in message news:<f8*************************@posting.google.c om>...

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.

I'll hazard a guess. If you mean you want to show the id, name, and
"interest selected" from a table, then it's:

SELECT id, name, interest
FROM mytable
WHERE interest = Forms![MyOpenForm]![cboInterest];

where cboInterest is the name of the combobox control on your form...
or is that not what you meant?

Nov 13 '05 #5

Don Leverton

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.

Nov 13 '05 #6

sheree hemphill

Thanks to everyone for all your suggestions! You've been extremely
helpful. It's true that my table design is a bad one & I'm now in the
middle of going back through and normalizing things (what I should have
done way back when, but was in a rush). This problem I'm having now
just highlights how important it is to normalize. Thanks again.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 13 '05 #7