MultiSelect lists to form Query

[abroniewski]

Hi!

I am creating a database and I have gotten stuck on the searching functionality. I am creating a form with several list boxes. The user chooses catagorie(s) to search through from these multiselect listboxes.

The catagories come from a table named Classifications where each colomn is a different catagory (listbox). I want the selected items to be saved to a table (named tblSearchFields, which has the same headings as Classifications), which will then be used to form the criteria of a query.

The error I am currently receiving is "3021: No current record".

The code I have so far:
Private Sub lstProjectPhase_Click()

Dim var As Variant
Dim lst As ListBox
Dim db As Database
Dim rst As Recordset

Set db = CurrentDb()
Set rst = CurrentDb.OpenRecordset("tblSearchFields", dbOpenDynaset)
Set lst = lstProjectPhase

rst.MoveFirst *****This is where the highlighted error occurs
For Each var In lst.ItemsSelected
rst![Project Phases] = lst.ItemData(var)
Next var
rst.Close

Set rst = Nothing
Set db = Nothing

End Sub

Thanks for your help!
Adam

 

[abroniewski]

I've been playing around with the code, and moved some code into the Form_Open subroutine. I get no error when I open the form, but the new error I get when clicking is "Variable not defined".

Private Sub Form_Open(Cancel As Integer
Dim lst As ListBox
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = CurrentDb.OpenRecordset("tblSearchFields", dbOpenDynaset)
Set lst = lstProjectPhase
End Sub

Private Sub lstProjectPhase_Click() *** HERE IS THE HIGHLIGHTED ERROR
Dim var As Variant
rst.MoveFirst
For Each var In lst.ItemsSelected
rst![Project Phases] = lst.ItemData(var)
Next var
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

 

[abroniewski]

I looked at some other code to try, and now I am having an error grabbing the data from the item selected...

The output of the debug.Print statement is:
0
1
1
2
2
3

Private Sub cmdOK_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim var As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("Query-Catagories")
For Each var In Me!lstProjectPhase.ItemsSelected
Debug.Print var
Debug.Print Me!lstProjectPhase.ItemData(var)
strCriteria = strCriteria & ",'" & Me!lstProjectPhase.ItemData(var) & "'"
Next var

strCriteria = Right(strCriteria, Len(strCriteria) - 1)
strSQL = "SELECT * FROM [Table-CommitmentsMaster] " & _
"WHERE [Table-CommitmentsMaster]![Project Phase] IN(" & strCriteria & ");"
qdf.SQL = strSQL
DoCmd.OpenQuery "Query-Catagories"
Set db = Nothing
Set qdf = Nothing
End Sub

 

[abroniewski]

Looks like the ItemData is returning the IDKey (1,2,3) associated with the catagory, rahter than the catagory itself. How can I rectify this?

 

[abroniewski]

I had to call the item from position 1 (ie column 2) from the list. There is a hidden column in my listbox as the IDKey. Here is the modified loop:

For Each var In Me!lstProjectPhase.ItemsSelected
strCriteria = strCriteria & ",'" & lstProjectPhase.Column(1, var) & "'"
Next var