Why my multiselect listbox doesn't pass the value to my Select query? 3

[jane30]

Hi, I have a subform containing 3 controls: StartDate, EndDate (both are bound text box), and HospCode (unbound multiselect listbox). All three of them are parameter values for my Select query. The first two controls work perfectly. But the last control sort of give me hard time. It doesn't pass the value to my query at all 'cause I always get nothing. The expression is like this: [Forms]![SwbSubMenu]![FrmSelPer].[Form]![List65]

What I try to do is to allow users to choose more than one hospital. I used to use a text box defaulted to "AA,BB,CC,DD,EE,FF" The query returned me nothing. But it worked if only one hospCode, e.g. "AA". Any suggestion how to make this work? Use multiselect listbox or textbox?

Thanks a bunch in advance

 

[JerryDennison]

You'll need to send the fieldname with each criteria. Use the following syntax:

Private Sub List0_AfterUpdate()
Dim varItem As Variant
Dim txtTemp As String
Dim txtCriteria As String

txtCriteria = "[CriteriaFieldName] = "
txtTemp = txtCriteria

For Each varItem In Me.List0.ItemsSelected
txtTemp = txtTemp & Me.List0.ItemData(varItem) & " Or " & txtCriteria
Next

txtTemp = Left(txtTemp, Len(txtTemp) - (Len(txtCriteria) + 4))

Me.HiddenTextboxName = txtTemp
End Sub

Then pass the textbox by itself to the form or report:

...
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FormName"

stLinkCriteria = Me![HiddenTextBoxName]
DoCmd.OpenForm stDocName, , , stLinkCriteria
...
"Advice is a dangerous gift, even from the wise to the wise, for all course may run ill." J.R.R. Tolkien

 

[idd]

I stumbled upon the answer with help from all of you,

here goes.

I have the multi-select list box with no code attached to it.

I have a module with a function called list. I have a button on my listbox form which calls the list function.

The list function has the following code

beginning of code ----------------------------------


Function list()


Dim frm As Form, ctl As Control
Dim varItem As Variant
Dim str As String
Set frm = Forms!Frm_Courselist
Set ctl = frm!listy
str = "[Courseno] in ("
'enumerate selected items and
'concatenate to strSQL
For Each varItem In ctl.ItemsSelected
str = str & ctl.ItemData(varItem) & ", "
Next varItem

'Trim the end of strSQL
str = Left$(str, Len(str) - 2)
str = str & &quot"
DoCmd.OpenForm "myform", acNormal, , str
End Function


End of Code----------------------


Now when my form opens it has the string STR as the where criteria. The string STR opens the form searching for the selected items within a IN clause.

I hope this makes sense.

Idd