472,362 Members | 2,011 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,362 software developers and data experts.

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

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
6 2647
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
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
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
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Jim G | last post by:
I have two tables: TestA and TestB. Both tables have 3 fields: ID, Name, and RunDate. I need to create a query which will join the two tables first on Name but then I need to match up the...
0
by: s_gregory | last post by:
The mdb is considerable size 70 +- mb. A complex union query was working well, but when an additional union select... was added into the query, selecting identical fields from a different source,...
0
by: Jonathan Fisher | last post by:
Whatıs the appropriate way to create records in related, subsidiary tables when a record is created in a main table? Iım using Access 2000, and I have a main table called tblProject, and a few...
0
by: Josh C. | last post by:
Hello everyone. I'm a bit of an Access newbie, so please bear with me. Please go to http://www.dumoti.com/access/ to view the database - 536kb. I'll go straight into examples: In the form...
1
by: Tiga | last post by:
Surely this was possible in Access 97. Has it gone or am I just not seeing the wood from the trees. Any help Grateful. Regards
6
by: bobdydd | last post by:
Access 2000 Windows XP Hi Everybody I have got a weird one here. I have a large form frmTransactions based on a single table tblTransactions. There are a lot of fields on the form but...
0
by: breakerman | last post by:
I have 4 inch high sprocket feed forms, for which I've defined a custom form in the Windows XP Print Server. My OKI Microline 320 has this fancy forms tear-off mode that only activates when it...
7
by: J-P-W | last post by:
Hi, in the past I've spent ages writing VB routines to get around this problem, is there a simple way? I have three tables for a membership system: tblMembership - MembershipNumber; Names etc...
13
by: Ulv | last post by:
I have a table (TblItems) with fields Lenght, Width and Height as decimalfields. I also have the fields Desc, a text field. In a form I have created this line of code after updating: Desc =...
5
by: Ferasse | last post by:
Hi, I'm an occasional Ms-Access developer, so there is still a lot of stuff that I don't get... Right now, I'm working on a database that stores contractual information. One of the form that...
2
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
1
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.