VBA and Filter Question

[Soulspike]

Form name to filter = frmSortFor
Filter based on list box from frmTest= lstSortFor
Form containing list box = frmTest
Field (CompCodes) data format = "PM SM TS EW WA"

I have a database that I want to filter a form (frmSortFor) useing the field (CompCodes). I need to find the records that match any of the 2 letter codes that I select on the list box. When i use the code listed below i get an runtime error :

"Microsoft Access can't Find the field 'FrmSortFor' referred to in your expression.".

I am pretty sure it is the way I have my filter syntax is set up but have not been able to find a good example as to what it should look like. Currently the filter data that is generated by the code and is stored in the variable SortFor looks like this:

""Forms!FrmSortFor!CompCodes= Like "*5M*" or Forms!FrmSortFor!CompCodes= Like "*HW*" or Forms!FrmSortFor!CompCodes= Like "*QM*""

Form name to filter = frmSortFor
Filter based on list box name = lstSortFor
Form containing list box = frmTest
Field (CompCodes) data format = "PM SM TS EW WA"


Current Software
- Access 97
- Win XP

Database
Format of Data in [CompCodes] field "PM SM TS EW WA"


Private Sub cmdPullFilteredRecords_Click()

Dim SortFor As String
Dim F As Variant

' Build Criteria string from selected Items in list box

SortFor = ""

For Each F In Me![lstSortFor].ItemsSelected

If SortFor <> "" Then
SortFor = SortFor & " or "
End If
SortFor = SortFor & "Forms!FrmSortFor!CompCodes=" & " Like " & Chr(34) & "*" & Me![lstSortFor].ItemData(F) & "*" & Chr(34)
Next F

' Open and Filter a form based on [CompCodes] field in frmSort for

DoCmd.OpenForm "frmSortFor", acFormDS
Form!FrmSortFor.Filter = SortFor
Form!FrmSortFor.FilterOn = True

End Sub


Any help would be greatly appretiated. Thank you

[Soulspike]

What is the format that a filter statement should be written in? I can't seem to find it anywhere.

Thanks

[QVeen72]

Hi,

You cannot write in Form Control names in double codes ..

Any way try this :

SortFor = SortFor & "CompCodes Like " & Chr(34) & "*" & Me![lstSortFor].ItemData(F) & "*" & Chr(34)
 

Check you want to search for "List" or "ItemData"....?

Regards
Veena

[Soulspike]

Well the items i am searching for are in 3 multi select list boxes. List box 1 is to find all records containing the codes selected. Then List box 2 takes those records and sorts out specific codes and list box 3 finds specific codes within those records. I have not worked with VBA much and what I know is self taught so I have quite a few holes in my knowlege. I think useing a recordset would do it but have not found any real good information on the internet to explain how to use that.


so Like *ListBox1*, Not Like *ListBox2* and Like *ListBox3*