list box as query criteria

[Paolo]

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.

[Pieter Linden]

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.

[(Pete Cresswell)]

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

[Pieter Linden]

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

[James Fortune]

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

[Pieter Linden]

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.