By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,213 Members | 1,296 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,213 IT Pros & Developers. It's quick & easy.

VBA and Filter Question

P: 35
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

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
Jan 15 '08 #1
Share this Question
Share on Google+
3 Replies

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

Jan 16 '08 #2

Expert 100+
P: 1,445

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

Any way try this :
Expand|Select|Wrap|Line Numbers
  1. SortFor = SortFor & "CompCodes Like " & Chr(34) & "*" & Me![lstSortFor].ItemData(F) & "*" & Chr(34)
Check you want to search for "List" or "ItemData"....?

Jan 16 '08 #3

P: 35
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*
Jan 21 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.