Donna Tunstall
Registered User.
- Local time
- Today, 23:01
- Joined
- Aug 16, 2001
- Messages
- 17
Hi All. I have a form which has a couple of multi-select list boxes. With the help of someone who knows far more than me, I was able to get the following code to work.
What I am trying to do now is to use the Screen.Active object command to be able to distinguish from which list box the user has made a selection and fire that criteria over to the appropriate query. I would use an if statement at the Set QueryDefs depending on the name of the ActiveControl.
*********** start of working code ****************************************************************************
Dim Q As QueryDef
Dim db As Database
Dim Criteria As Variant
Dim ctl As Control
Dim Itm As Variant
Set ctl = Me![SelectCategory]
For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = "'" & ctl.ItemData(Itm) & "'"
Else
Criteria = Criteria & ",'" & ctl.ItemData(Itm) & "'"
End If
Next Itm
If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the list box!", 0, "No Selection Made")
Exit Sub
End If
Set db = CurrentDb()
Set Q = db.QueryDefs("qryCategorySelection")
Q.SQL = "Select * From [qryCatalog] Where [CategoryName] In(" & Criteria & ");"
Q.Close
*********** end of working code *****************************************************************************
This does not work, however :
Set ctl = Screen.ActiveControl
For Each Itm In ctl.ItemsSelected
I also tried
For Each Itm in Screen.ActiveControl.ItemsSelected
It works fine when referencing one specific control, but goes wobbly when I try to expand on that and I get the error message Number 2455 , You entered an expression that has an invalid reference to the property ITEMSSELECTED. The property may not exist or may not apply to the object you specified
It goes all goofy at the ItemsSelected point.
What is odd though, is that when I type it in the drop down box offers the selection of ItemsSelected after ActiveControl. However, when I type a full stop after ItemsSelected the choices Count or Item pop up??? Tried adding .Item at the end of it, but this time the error message was Argument Not Optional, so I assume I screwed that bit up.
Please - I cannot believe that I have to set up separate code for each List Box on my form.
Thanks in advance for any help!! Cheers,
Donna
What I am trying to do now is to use the Screen.Active object command to be able to distinguish from which list box the user has made a selection and fire that criteria over to the appropriate query. I would use an if statement at the Set QueryDefs depending on the name of the ActiveControl.
*********** start of working code ****************************************************************************
Dim Q As QueryDef
Dim db As Database
Dim Criteria As Variant
Dim ctl As Control
Dim Itm As Variant
Set ctl = Me![SelectCategory]
For Each Itm In ctl.ItemsSelected
If Len(Criteria) = 0 Then
Criteria = "'" & ctl.ItemData(Itm) & "'"
Else
Criteria = Criteria & ",'" & ctl.ItemData(Itm) & "'"
End If
Next Itm
If Len(Criteria) = 0 Then
Itm = MsgBox("You must select one or more items in the list box!", 0, "No Selection Made")
Exit Sub
End If
Set db = CurrentDb()
Set Q = db.QueryDefs("qryCategorySelection")
Q.SQL = "Select * From [qryCatalog] Where [CategoryName] In(" & Criteria & ");"
Q.Close
*********** end of working code *****************************************************************************
This does not work, however :
Set ctl = Screen.ActiveControl
For Each Itm In ctl.ItemsSelected
I also tried
For Each Itm in Screen.ActiveControl.ItemsSelected
It works fine when referencing one specific control, but goes wobbly when I try to expand on that and I get the error message Number 2455 , You entered an expression that has an invalid reference to the property ITEMSSELECTED. The property may not exist or may not apply to the object you specified
It goes all goofy at the ItemsSelected point.
What is odd though, is that when I type it in the drop down box offers the selection of ItemsSelected after ActiveControl. However, when I type a full stop after ItemsSelected the choices Count or Item pop up??? Tried adding .Item at the end of it, but this time the error message was Argument Not Optional, so I assume I screwed that bit up.
Please - I cannot believe that I have to set up separate code for each List Box on my form.
Thanks in advance for any help!! Cheers,
Donna
