By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

list box as query criteria

P: n/a
Friends,

I have a form named Welcome on which there are various pages. On one
page, simply named PAGE1, I have added a list box named LSTINITIALS,
which stores the initials of my database users. This list box is also
used to supply criteria to a query named WELCOMELOOKUP.

On the same form I have added another list box named LSTSSN which has
the query named WELCOMELOOKUP as row source.

This query has 5 field, one of which is named INITIAL and has the
following criteria: FORMS!WELCOME!LSTINITIALS. The query source is a
table named CASES.

What I would like is that my LSTSSN would return all records according
to the selected record in the list box LSTINITIALS.

Can anyone give me a better solution?

Thanks.
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
jp***@tin.it (Paolo) wrote in message news:<9f*************************@posting.google.c om>...
Friends,

I have a form named Welcome on which there are various pages. On one
page, simply named PAGE1, I have added a list box named LSTINITIALS,
which stores the initials of my database users. This list box is also
used to supply criteria to a query named WELCOMELOOKUP.

On the same form I have added another list box named LSTSSN which has
the query named WELCOMELOOKUP as row source.

This query has 5 field, one of which is named INITIAL and has the
following criteria: FORMS!WELCOME!LSTINITIALS. The query source is a
table named CASES.

What I would like is that my LSTSSN would return all records according
to the selected record in the list box LSTINITIALS.

Can anyone give me a better solution?

Thanks.


if you're only using a single record in your listbox, then use a
combobox instead, and create a subform with the related records. When
you run the combobox wizard, select "go to record..." and that should
do it.
Nov 13 '05 #2

P: n/a
RE/
This query has 5 field, one of which is named INITIAL and has the
following criteria: FORMS!WELCOME!LSTINITIALS. The query source is a
table named CASES


I've had trouble with getting queries to look into the contents of list and
combo boxes. If you approach doesn't work, try adding a AfterUpdate event to
the list that populates an invisible text field with what you want the query to
select on and aiming the query at that text field instead of the list box.
--
PeteCresswell
Nov 13 '05 #3

P: n/a
Paolo,
a few questions first... can the user select multiple options in the
listbox? If not, save yourself considerable headache and just use a
combobox. Otherwise, if you *really* want to use the listbox, there's
code here for what you want:

http://www.mvps.org/access/forms/frm0007.htm

you'd just set the rowsource and then requery the listbox...

the stuff on comboboxes might help too...

http://www.mvps.org/access/forms/frm0028.htm
Nov 13 '05 #4

P: n/a
pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...
Paolo,
a few questions first... can the user select multiple options in the
listbox? If not, save yourself considerable headache and just use a
combobox. Otherwise, if you *really* want to use the listbox, there's
code here for what you want:

http://www.mvps.org/access/forms/frm0007.htm


Dev's idea of creating SQL from a multiselect listbox is a good one:

'******************** Code Start ************************
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))
'******************** Code end ************************

Instead of using the code above, I would use something like:

'******************** Code Start ************************
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim intI As Integer

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees"
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
intI = 0
For Each varItem In ctl.ItemsSelected
intI = intI + 1
If intI = 1 Then
strSQL = strSQL & " WHERE [EmpID] = " & ctl.ItemData(varItem)
Else
strSQL = strSQL & " OR [EmpID]= " & ctl.ItemData(varItem)
End If
Next varItem
'******************** Code end ************************

so that the case where no items are selected creates a valid SQL
string. In a more generalized version that includes other criteria
just tack on the " OR ... " parts for each selected item in the
listbox onto the WHERE part.

James A. Fortune
Nov 13 '05 #5

P: n/a
ja******@oakland.edu (James Fortune) wrote in message news:<a6*************************@posting.google.c om>...
pi********@hotmail.com (Pieter Linden) wrote in message news:<bf**************************@posting.google. com>...
Paolo,
a few questions first... can the user select multiple options in the
listbox? If not, save yourself considerable headache and just use a
combobox. Otherwise, if you *really* want to use the listbox, there's
code here for what you want:

http://www.mvps.org/access/forms/frm0007.htm


Dev's idea of creating SQL from a multiselect listbox is a good one:

'******************** Code Start ************************
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees where [EmpID]="
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
strSQL = strSQL & ctl.ItemData(varItem) & " OR [EmpID]="
Next varItem

'Trim the end of strSQL
strSQL=left$(strSQL,len(strSQL)-12))
'******************** Code end ************************

Instead of using the code above, I would use something like:

'******************** Code Start ************************
Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim intI As Integer

Set frm = Form!frmMyForm
Set ctl = frm!lbMultiSelectListbox
strSQL = "Select * from Employees"
'Assuming long [EmpID] is the bound field in lb
'enumerate selected items and
'concatenate to strSQL
intI = 0
For Each varItem In ctl.ItemsSelected
intI = intI + 1
If intI = 1 Then
strSQL = strSQL & " WHERE [EmpID] = " & ctl.ItemData(varItem)
Else
strSQL = strSQL & " OR [EmpID]= " & ctl.ItemData(varItem)
End If
Next varItem
'******************** Code end ************************

so that the case where no items are selected creates a valid SQL
string. In a more generalized version that includes other criteria
just tack on the " OR ... " parts for each selected item in the
listbox onto the WHERE part.

James A. Fortune

Cool! I like the alternative way to figuring out the OR stuff... I
always found that a pain when executing the SQL... it would always
flake out because I trimmed too much.
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.