• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

400 POINTS: ADVANCED Search Form (Multiple Multiselect List Boxes for the same field)

Ok, another question for the great experts of Experts Exchange.

Please note that while this question has only 250 points assigned to it, there is another 50-point question here: http://www.experts-exchange.com/Databases/MS_Access/Q_20453074.html (you may want to use as a resource). THUS, THIS QUESTION IS WORTH A GOOD 400 POINTS, AKA 1600 EXPERT POINTS.

Now onto business. I have a search form. It has:
Type "lstType" - a list box. has four choices. text data type. MULTISELECT SET TO STANDARD
Public "lstPublic" - a list box. has two choices. text data type. MULTISELECT SET TO STANDARD
Name "namesearch" - a text box. text data type. note: this is the name of a college, not a person, so don't tell me to make separate last and first name fields :-)
City "citysearch" - a text box. text data type.

I have built the following code:

Private Sub cmdgetdata_Click()
Dim strFilter As String
Dim varItem As Variant
Dim varItem2 As Variant

strFilter = ""

If Not IsNull(Me!namesearch) Then strFilter = strFilter & "[namesearch] Like " & Chr(34) & "*" & Me!namesearch & "*" & Chr(34) & " AND "
If Not IsNull(Me!citysearch) Then strFilter = strFilter & "[citysearch] Like " & Chr(34) & "*" & Me!citysearch & "*" & Chr(34) & " AND "
If Me!lsttype.ItemsSelected.Count > 0 Then
  strFilter = strFilter & "("
  For Each varItem In Me!lsttype.ItemsSelected
     strFilter = strFilter & "[Type]=" & Chr(34) & Me!lsttype.ItemData(varItem) & Chr(34) & " OR "
  Next
  strFilter = Left$(strFilter, Len(strFilter) - 4) & ") AND "
End If
If Me!lstpublic.ItemsSelected.Count = 1 Then
  strFilter = strFilter & "("
  For Each varItem2 In Me!lstpublic.ItemsSelected
     strFilter = strFilter & "[Public]=" & Chr(34) & Me!lstpublic.ItemData(varItem2) & Chr(34) & " OR "
  Next
  strFilter = Left$(strFilter, Len(strFilter) - 4) & ") AND "
End If
   
If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 4)
Me.Form.Filter = strFilter
Me.Form.FilterOn = True
End Sub

Looks like it would work, right? For some reason, my form does nothing when the button is clicked.

HOWEVER! WE MUST FURTHER COMPLICATE THE PROBLEM! I also have nine list boxes:
Lst1
Lst2
Lst3
Lst4
Etc...

Now, they are ALL for state. The first seven are for each region (in accordance with the National College Board) in the United States. The eighth one is for US Territories (Guam, Puerto Rico, Federated States of Micronesia, etc) and the last one is each of the providences of Canada. You can view a screenshot of what it looks like (basically) here: http://members.cox.net/anacin/screenshot1.gif. there is also a cmdgetdata button on the form that is not shown in the screenshot. If the screenshot comes in small, then you have Windows XP and the auto-resize setting set. If you don't know how to fix it, you can hover the mouse over the bottom right corner of the image and a little picture with 4 arrows should come up. Click it and it will get bigger.

TIME FOR MORE CODE! THIS IS WHAT I HAVE WITH THE LIST BOXES!

Private Sub cmdgetdata_Click()
Dim strFilter As String
Dim varItem As Variant
Dim varItem2 As Variant
Dim lst1var As Variant
Dim lst2var As Variant
Dim lst3var As Variant
Dim lst4var As Variant
Dim lst5var As Variant
Dim lst6var As Variant
Dim lst7var As Variant
Dim lst8var As Variant
Dim lst9var As Variant

strFilter = ""

If Not IsNull(Me!namesearch) Then strFilter = strFilter & "[namesearch] Like " & Chr(34) & "*" & Me!namesearch & "*" & Chr(34) & " AND "
If Not IsNull(Me!citysearch) Then strFilter = strFilter & "[citysearch] Like " & Chr(34) & "*" & Me!citysearch & "*" & Chr(34) & " AND "
If Me!lsttype.ItemsSelected.Count > 0 Then
  strFilter = strFilter & "("
  For Each varItem In Me!lsttype.ItemsSelected
     strFilter = strFilter & "[Type]=" & Chr(34) & Me!lsttype.ItemData(varItem) & Chr(34) & " OR "
  Next
  strFilter = Left$(strFilter, Len(strFilter) - 4) & ") AND "
End If
If Me!lstpublic.ItemsSelected.Count = 1 Then
  strFilter = strFilter & "("
  For Each varItem2 In Me!lstpublic.ItemsSelected
     strFilter = strFilter & "[Public]=" & Chr(34) & Me!lstpublic.ItemData(varItem2) & Chr(34) & " OR "
  Next
  strFilter = Left$(strFilter, Len(strFilter) - 4) & ") AND "
End If
'START OF THE REGIONS AND STATE LIST
'LST1
If Me!lst1.ItemsSelected.Count > 0 Then
  strFilter = strFilter & "("
  For Each lst1var In Me!lst1.ItemsSelected
     strFilter = strFilter & "[State]=" & Chr(34) & Me!lst1.ItemData(lst1var) & Chr(34) & " OR "
  Next
  strFilter = Left$(strFilter, Len(strFilter) - 4) & ") OR "
End If
'LST2
If Me!lst2.ItemsSelected.Count > 0 Then
  strFilter = strFilter & "("
  For Each lst2var In Me!lst2.ItemsSelected
     strFilter = strFilter & "[State]=" & Chr(34) & Me!lst2.ItemData(lst2var) & Chr(34) & " OR "
  Next
  strFilter = Left$(strFilter, Len(strFilter) - 4) & ") OR "
End If
'LST3
If Me!lst3.ItemsSelected.Count > 0 Then
  strFilter = strFilter & "("
  For Each lst3var In Me!lst3.ItemsSelected
     strFilter = strFilter & "[State]=" & Chr(34) & Me!lst3.ItemData(lst3var) & Chr(34) & " OR "
  Next
  strFilter = Left$(strFilter, Len(strFilter) - 4) & ") OR "
End If
'LST4
If Me!lst4.ItemsSelected.Count > 0 Then
  strFilter = strFilter & "("
  For Each lst4var In Me!lst4.ItemsSelected
     strFilter = strFilter & "[State]=" & Chr(34) & Me!lst4.ItemData(lst4var) & Chr(34) & " OR "
  Next
  strFilter = Left$(strFilter, Len(strFilter) - 4) & ") OR "
End If
'LST5
If Me!lst5.ItemsSelected.Count > 0 Then
  strFilter = strFilter & "("
  For Each lst5var In Me!lst5.ItemsSelected
     strFilter = strFilter & "[State]=" & Chr(34) & Me!lst5.ItemData(lst5var) & Chr(34) & " OR "
  Next
  strFilter = Left$(strFilter, Len(strFilter) - 4) & ") OR "
End If
'LST6
If Me!lst6.ItemsSelected.Count > 0 Then
  strFilter = strFilter & "("
  For Each lst6var In Me!lst6.ItemsSelected
     strFilter = strFilter & "[State]=" & Chr(34) & Me!lst6.ItemData(lst6var) & Chr(34) & " OR "
  Next
  strFilter = Left$(strFilter, Len(strFilter) - 4) & ") OR "
End If
'LST7
If Me!lst7.ItemsSelected.Count > 0 Then
  strFilter = strFilter & "("
  For Each lst7var In Me!lst7.ItemsSelected
     strFilter = strFilter & "[State]=" & Chr(34) & Me!lst7.ItemData(lst7var) & Chr(34) & " OR "
  Next
  strFilter = Left$(strFilter, Len(strFilter) - 4) & ") OR "
End If
'LST8
If Me!lst8.ItemsSelected.Count > 0 Then
  strFilter = strFilter & "("
  For Each lst8var In Me!lst8.ItemsSelected
     strFilter = strFilter & "[State]=" & Chr(34) & Me!lst8.ItemData(lst8var) & Chr(34) & " OR "
  Next
  strFilter = Left$(strFilter, Len(strFilter) - 4) & ") OR "
End If
'LST9
If Me!lst9.ItemsSelected.Count > 0 Then
  strFilter = strFilter & "("
  For Each lst9var In Me!lst9.ItemsSelected
     strFilter = strFilter & "[State]=" & Chr(34) & Me!lst9.ItemData(lst9var) & Chr(34) & " OR "
  Next
  strFilter = Left$(strFilter, Len(strFilter) - 4) & ") AND "
End If
'END OF THE REGIONS AND STATE LIST
If strFilter <> "" Then
    If Right(strFilter, 4) = " OR " Then
    strFilter = Left$(strFilter, Len(strFilter) - 4)
    ElseIf Right(strFilter, 4) = "AND " Then
    strFilter = Left$(strFilter, Len(strFilter) - 5)
    Else 'for debugging purposes
    MsgBox "There is an error in trimming the string." 'for debugging purposes
End If

'NEW IF STATEMENT TRIM ABOVE. THIS IS NOT NEEDED: If strFilter <> "" Then strFilter = Left$(strFilter, Len(strFilter) - 4)
Me.Form.Filter = strFilter
Me.Form.FilterOn = True
End Sub


PLEASE HELP ME! I WILL GLADLY SEND YOU MY DATABASE VIA E-MAIL IF IT MAKES IT EASIER; THEN WE CAN POST THE FINAL CODE WHEN WE ARE DONE.

Thanks,

nex
0
nexusnation
Asked:
nexusnation
  • 22
  • 20
  • +1
1 Solution
 
JezWaltersCommented:
What a lot of code!  Are you sure you're building up your filter string correctly?

At first glance, where you're processing the list box selections, it looks like the expressions of the form:

  strFilter = Left$(strFilter, Len(strFilter) - 4) & ") AND "

should actually be:

  strFilter = Left$(strFilter, Len(strFilter) - 3) & ") AND "

As you're appending " OR" (3 characters) to each clause.

Otherwise, it would be well worthwhile breakpointing just before the end of your button click event handler and checking the value at run-time.
0
 
nexusnationAuthor Commented:
since it is so much code, i am sure there are mistakes.

and actually, OR is being appended for the list boxes because state can't have TWO values. AND is for the other fields. kinda confusing.

also,

") AND " needs 4 to be trimmed off, not 3. spaces at the end are counted (unless i use the trim function first, which may make it easier)
same with: "OR ". that is 3, not 2. again, trim COULD be used.

regarding the breakpointing, i may do that. thanks for the suggestion.

i will be eager to hear from others. i am guessing that shane is not at his computer at the moment, cause he'll pounce on this question. hehehe

thanks,

nex
0
 
CharitygCommented:
If you could send it to me in zip format, I'd love to take a look.
cgaborik@christensenfarms.com
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
TextReportCommented:
Nex, chuck me a copy of your sample to andrew@textreport.com

Cheers, Andrew
0
 
nexusnationAuthor Commented:
i'll upload it for all to download and look at. it's currently 1.5 MB. feel free to download and work on. i warn you, it is VERY messy. don't even open the main switchboard to try and do something there (may be cool to see the 'rollover' text and the cool photoshop graphic, though :-). there are buttons everywhere...

http://members.cox.net/anacin/MLC%20Guidance%20Catalog%202002.mdb

thanks,

nex
0
 
TextReportCommented:
Before getting the example to do anything I had to the following changes

1. Delete the duplicate procedure Midcontinent_East_Click in Form_Search2
2. Set a Reference to Microsoft DAO 3.6
3. Put in Option Explicit at the beginning of the form module.
4. Renamed MidcontinentalEastList to MidcontinentEastList

Now I get a SELECT Statement in my messagebox MsgBox SQLA

Cheers, Andrew
0
 
TextReportCommented:
Next Tip. Replace your SQLA lines with the following

Cheers, Andrew

SQLA = "SELECT CollegeInfo.State/Provience" & vbCrLf & _
       "      ,CollegeInfo.City" & vbCrLf & _
       "      ,CollegeInfo.Institution" & vbCrLf & _
       "      ,CollegeInfo.Type" & vbCrLf & _
       "      ,CollegeInfo.Public" & vbCrLf & _
       "FROM CollegeInfo" & vbCrLf
SQLA = SQLA & " WHERE (   CollegeInfo.State/Provience IN (" & stra & ")" & vbCrLf & _
              "        OR CollegeInfo.State/Provience IN (" & strb & ")" & vbCrLf & _
              "        OR CollegeInfo.State/Provience IN (" & strc & ")" & vbCrLf & _
              "        OR CollegeInfo.State/Provience IN (" & strd & ")" & vbCrLf & _
              "        OR CollegeInfo.State/Provience IN (" & stre & ")" & vbCrLf & _
              "        OR CollegeInfo.State/Provience IN (" & strf & ")" & vbCrLf & _
              "        OR CollegeInfo.State/Provience IN (" & strg & ")" & vbCrLf & _
              "        OR CollegeInfo.State/Provience IN (" & strh & ")" & vbCrLf & _
              "        OR CollegeInfo.State/Provience IN (" & stri & ")" & vbCrLf & _
              "       )"

If Nz(Me.citysearch, "") <> "" Then
   SQLA = SQLA & "   AND CollegeInfo.City) = " & Chr(34) & Me.citysearch & Chr(34) & vbCrLf
End If
If Nz(Me.namesearch, "") <> "" Then
   SQLA = SQLA & "   AND CollegeInfo.State) = " & Chr(34) & Me.namesearch & Chr(34) & vbCrLf
End If
0
 
TextReportCommented:
OK now I have re-written using an array, reduces the amount of repetitive code.

Cheers, Andrew

Dim SQLA As String
Dim x As Variant

Dim strSTATE As String
Dim cntSTATE As Long
Dim strWHERE As String

ReDim arycontrolnames(8) As String
arycontrolnames(0) = "WesternList"
arycontrolnames(1) = "SouthwesternList"
arycontrolnames(2) = "MidwestList"
arycontrolnames(3) = "MidcontinentEastList"
arycontrolnames(4) = "SouthernList"
arycontrolnames(5) = "MidAtlanticList"
arycontrolnames(6) = "NortheastList"
arycontrolnames(7) = "TerritoriesList"
arycontrolnames(8) = "CanadaList"

For cntSTATE = LBound(arycontrolnames) To UBound(arycontrolnames)
    For Each x In Me(arycontrolnames(cntSTATE)).ItemsSelected
      strSTATE = strSTATE & ", " & Chr(34) & Me(arycontrolnames(cntSTATE)).ItemData(x) & Chr(34) ' surround with quotes
    Next
Next cntSTATE

If strSTATE <> "" Then
   strWHERE = "  AND CollegeInfo.State/Provience IN (" & Mid(strSTATE, 3) & ")"
End If

If Nz(Me.citysearch, "") <> "" Then
   strWHERE = strWHERE & "   AND CollegeInfo.City) = " & Chr(34) & Me.citysearch & Chr(34) & vbCrLf
End If
If Nz(Me.namesearch, "") <> "" Then
   strWHERE = strWHERE & "   AND CollegeInfo.State) = " & Chr(34) & Me.namesearch & Chr(34) & vbCrLf
End If

If strWHERE <> "" Then strWHERE = "WHERE " & Mid(LTrim(strWHERE), 5)

SQLA = "SELECT CollegeInfo.State/Provience" & vbCrLf & _
       "      ,CollegeInfo.City" & vbCrLf & _
       "      ,CollegeInfo.Institution" & vbCrLf & _
       "      ,CollegeInfo.Type" & vbCrLf & _
       "      ,CollegeInfo.Public" & vbCrLf & _
       "FROM CollegeInfo" & vbCrLf & _
       strWHERE

Debug.Print SQLA
MsgBox SQLA, vbInformation, "AJ" ' for debugging only
0
 
TextReportCommented:
If your listboxes were called List1, List2 etc then you wouldnt need the array and you could replace the state building part to

For cntSTATE = 1 to 9
    For Each x In Me("List" & cntSTATE).ItemsSelected
      strSTATE = strSTATE & ", " & Chr(34) & Me("List" & cntSTATE).ItemData(x) & Chr(34) ' surround with quotes
    Next
Next cntSTATE

Cheers, Andrew
0
 
nexusnationAuthor Commented:
woah! pause!

you confused me with my own program. how did you find my code with sqla, sqlb, etc. and with those names on the list boxes. don't tell me i posted an old version. (me checking). GAHH! i posted the wrong version. trust me though, the old version is better, and the code is similar. the form you will want to look at is "CollegeSearch".

http://members.cox.net/anacin/MLC%20Guidance%20Catalogs%202002.mdb

sorry for the inconvience, Andrew.

sincerely,

andrew (same name :-)
0
 
nexusnationAuthor Commented:
ok, i have the following code. however, only the SELECT and FROM clauses are shown. the WHERE clause (with the strWHERE) is nowhere to be found. if i select even one thing in any list box, the msgbox doesn't even appear (to the contrary, the msgbox shows if data is entered in the name or city text boxes). to add to the problem, the code has nothing for the lstpublic or lsttype boxes. regarding those:
lstpublic: i want to use the loop that involves seeing if anything is selected and how many are (.itemsselected.Count) and ONLY add it to the WHERE clause if 1 (not 0 or 2) things are selected. it is a 2-choice multiselect list box. trust me, i don't want a check box here (i just make the program. it ain't my idea :-)
for lsttype: 4 choices, loop through, etc. like any other loop for a multiselect list box.

one last thing, for the namesearch and citysearch boxes, i need them to use:
Like "*" & Me.namesearch & "*"
i know how to do that, but i'd rather not mess up the code.

thanks,

andrew



Private Sub cmdgetdata_Click()
Dim SQLA As String
Dim x As Variant

Dim strSTATE As String
Dim cntSTATE As Long
Dim strWHERE As String

For cntSTATE = 1 To 9
   For Each x In Me("lst" & cntSTATE).ItemsSelected
     strSTATE = strSTATE & ", " & Chr(34) & Me("List" & cntSTATE).ItemData(x) & Chr(34)
   Next
Next cntSTATE

If strSTATE <> "" Then
  strWHERE = "  AND CollegeInfo.State/Provience IN (" & Mid(strSTATE, 3) & ")"
End If

If Nz(Me.citysearch, "") <> "" Then
  strWHERE = strWHERE & "   AND CollegeInfo.City) = " & Chr(34) & Me.citysearch & Chr(34) & vbCrLf
End If
If Nz(Me.namesearch, "") <> "" Then
  strWHERE = strWHERE & "   AND CollegeInfo.State) = " & Chr(34) & Me.namesearch & Chr(34) & vbCrLf
End If

If strWHERE <> "" Then strWHERE = "WHERE " & Mid(LTrim(strWHERE), 5)

SQLA = "SELECT CollegeInfo.State/Provience" & vbCrLf & _
      "      ,CollegeInfo.City" & vbCrLf & _
      "      ,CollegeInfo.Institution" & vbCrLf & _
      "      ,CollegeInfo.Type" & vbCrLf & _
      "      ,CollegeInfo.Public" & vbCrLf & _
      "FROM CollegeInfo" & vbCrLf & _
      strWHERE

Debug.Print SQLA
MsgBox SQLA, vbInformation, "AJ" ' for debugging only
End Sub
0
 
nexusnationAuthor Commented:
note the names of the listboxes (in the new version) are lst1, lst2, lst3, lst4, etc.

thanks,

andrew
0
 
TextReportCommented:
Andrew, first thing is to repeat what I said in my posting 04/01/2003 11:27AM PST that Option Explicit should be in the Declarations section of EVERY module, this will assist in eliminating many many errors caused by typo's.

Near the end of the function you have a missing End If

If strFilter <> "" Then
    If Right(strFilter, 4) = " OR " Then
    strFilter = Left$(strFilter, Len(strFilter) - 4)
    ElseIf Right(strFilter, 4) = "AND " Then
    strFilter = Left$(strFilter, Len(strFilter) - 5)
    Else 'forH debugging purposes
    MsgBox "There is an error in trimming the string." 'for debugging purposes
End If

if you indent your code then this would have been obvious

If strFilter <> "" Then
   If Right(strFilter, 4) = " OR " Then
      strFilter = Left$(strFilter, Len(strFilter) - 4)
   ElseIf Right(strFilter, 4) = "AND " Then
      strFilter = Left$(strFilter, Len(strFilter) - 5)
   Else 'forH debugging purposes
      MsgBox "There is an error in trimming the string." 'for debugging purposes
   End If ' This is the one I have Added
End If

Cheers, Andrew
0
 
TextReportCommented:
Andrew, I have rewritten the code as in the first time. This should make it much easier to suss.

I will now take a look at what you have just posted.

Cheers, Andrew

Private Sub cmdgetdata_Click()
Dim strFilter As String
Dim varItem As Variant
Dim varItem2 As Variant
Dim lst1var As Variant
Dim cntState As Long
Dim strIN As String
strFilter = ""

If Not IsNull(Me!namesearch) Then strFilter = strFilter & " AND [namesearch] Like " & Chr(34) & "*" & Me!namesearch & "*" & Chr(34)
If Not IsNull(Me!citysearch) Then strFilter = strFilter & " AND [citysearch] Like " & Chr(34) & "*" & Me!citysearch & "*" & Chr(34)

strIN = ""
If Me!lsttype.ItemsSelected.Count > 0 Then
   For Each varItem In Me!lsttype.ItemsSelected
       strIN = strIN & ", " & Chr(34) & Me!lsttype.ItemData(varItem) & Chr(34)
   Next
End If
If strIN <> "" Then
   strIN = Mid(strIN, 3)
   strFilter = strFilter & " AND [Type] IN (" & strIN & ")"
End If

strIN = ""
If Me!lstpublic.ItemsSelected.Count = 1 Then
   For Each varItem2 In Me!lstpublic.ItemsSelected
      strIN = strIN & ", " & Chr(34) & Me!lstpublic.ItemData(varItem2) & Chr(34)
   Next
End If
If strIN <> "" Then
   strIN = Mid(strIN, 3)
   strFilter = strFilter & " AND [Public] IN (" & strIN & ")"
End If

strIN = ""
For cntState = 1 To 9
    If Me("lst" & cntState).ItemsSelected.Count > 0 Then
       For Each lst1var In Me("lst" & cntState).ItemsSelected
           strIN = strIN & ", " & Chr(34) & Me("lst" & cntState).ItemData(lst1var) & Chr(34)
       Next
    End If
Next cntState

If strIN <> "" Then
   strIN = Mid(strIN, 3)
   strFilter = strFilter & " AND [State/Provience] IN (" & strIN & ")"
End If

If strFilter <> "" Then
   strFilter = Mid(strFilter, 6)
   Me.Form.Filter = strFilter
   Me.Form.FilterOn = True
Else
   Me.Form.FilterOn = False
End If
Debug.Print strFilter
End Sub

0
 
TextReportCommented:
If you are only going to use a single selection then can you not turn off the multiple select option?

Cheers, Andrew
0
 
TextReportCommented:
Andrew, just a final thought for the night as I am based in the UK and its fast approaching bedtime. I will be here for possibly another hour and will check again in around 8 hours time.

If you get the chance please try my code posted 04/01/2003 01:41PM PST and let me know how it is for what you are after.

Finally, You have a setting in the database that refuses to stop at break points and breaking your errors, any idea what you have done to stop this?

Cheers, Andrew
0
 
nexusnationAuthor Commented:
cause then you can't deselect it (without code). by using multiselect with the loop and of =0:

if 0 are selected: no filter
if 1 is selected: filters the one chosen
if 2 are selected: no filter

it can be taken two different ways. they may select both of them to choose both or they may select neither of them because they don't want it filtered.

regarding option explicit and indentation: i gotta work on that. i'll start doing that every time :-)
what exactly does Option Explicit do?

thanks, andrew
0
 
nexusnationAuthor Commented:
>>>>Finally, You have a setting in the database that refuses to stop at break points and breaking your errors, any idea what you have done to stop this?

i may know a decent amount of VBA, but i don't even use breakpoints (not yet at least. not to sure what they do :-)


ah, i think i found it (even though i am not entirely sure as to what i am looking for). in the VBA Editor, Tools > Options, General Tab. on the right.

thanks, andrew
0
 
nexusnationAuthor Commented:
i tryed the new code on 4/01/03 1:41 PM. parameter boxes are coming up for "namesearch" and "citysearch"
0
 
TextReportCommented:
NameSearch should be Institution and citysearch should be City so

If Not IsNull(Me!namesearch) Then strFilter = strFilter & " AND [namesearch] Like " & Chr(34) & "*" & Me!namesearch & "*" & Chr(34)
If Not IsNull(Me!citysearch) Then strFilter = strFilter & " AND [citysearch] Like " & Chr(34) & "*" & Me!citysearch & "*" & Chr(34)

becomes

If Not IsNull(Me!namesearch) Then strFilter = strFilter & " AND [Institution] Like " & Chr(34) & "*" & Me!namesearch & "*" & Chr(34)
If Not IsNull(Me!citysearch) Then strFilter = strFilter & " AND [city] Like " & Chr(34) & "*" & Me!citysearch & "*" & Chr(34)


Cheers, Andrew
0
 
TextReportCommented:
Breakpoints are "Markers" in your code that will allow your code to sit and wait until you tell it to continue. You set breakpoints by clicking in the lefthandside or pressing F9 or Debug Menu, Toggle BreakPoint.
0
 
nexusnationAuthor Commented:
ah, gotcha on those breakpoints. and i knew why the parameter boxes were coming up. just telling you that you need to change the code you have on your end :-) i ain't that stupid with VBA ;-)

Eager to hear from you,

Andrew
0
 
TextReportCommented:
I have been out for the evening, watching football (soccer if your States side).

So what is outstanding on this now?

Cheers, Andrew
0
 
nexusnationAuthor Commented:
read the start of my post, 4.1.03 1:30 pm PST...

"ok, i have the following code. however, only the SELECT and FROM clauses are shown. the WHERE clause (with the strWHERE) is nowhere to be found. if i select even one thing in any list box, the msgbox doesn't even appear (to the contrary, the msgbox shows if data is entered in the name or city text boxes). to add to the problem, the code has nothing for the lstpublic or lsttype boxes."

p.s. i am in the EST (GMT -0500). be on at 5a PST tomorrow (should be late morning/early afternoon for you, eh?

thanks for your efforts,

andrew
0
 
TextReportCommented:
Have you tried the code I posted 04/01/2003 01:41PM PST as this one is working on my copy of the database except the namesearch you identified.

As well as the problems in getting the syntax correct there is a fundamental problem that we have not identified with your database. When these errors occour we should be able to use a BreakPoint or the Stop Command or the error should highlight where the error is, this is not happening.

I will look further into this but it won't be for another 6 hours.

Cheers, Andrew
0
 
TextReportCommented:
In the meantime can you check out http://support.microsoft.com/default.aspx?scid=kb;en-us;296848 and see if your Special Keys setting is causing the break problem.

Cheers, Andrew
0
 
TextReportCommented:
Just tested it on your database and if you select the option it will allow you to debug your code, using breakpoints, whatches and stepping through the code with F8 and Shift-F8.

Cheers, Andrew
0
 
nexusnationAuthor Commented:
thanks for all of your work. if my database continues to become a problem, you can export all objects to a new blank database if you wish.

sorry for the invonvience but i will be leaving on a trip in like two hours and i will be returning on Sunday (notice in my profile). when i come back, i will test the code and award the points.

thnaks for your efforts,

Andrew
0
 
TextReportCommented:
No problem and have a nice trip. Cheers, Andrew
0
 
nexusnationAuthor Commented:
i'm back...

regarding the special keys:
that is what is causing the problem, as i turned that setting off. i guess that you can just use the "Stop" keyword instead. to make it easier, you can turn the setting back on and use the breakpoints if you wish.

regarding the code:
the code you claim that works on 04/01/2003 01:41PM PST does not work on my copy. any idea why? i have a reference set to DAO and it seems like it would work.

thanks,

Andrew
0
 
TextReportCommented:
I have downloaded the database again and on the OnClick of the GetData button I have entered the code below which is what I posted above but I have also fixed the Institution and City problem.

I can now select states and or enter institution or city and it filters the records

Cheers, Andrew

Private Sub cmdgetdata_Click()
Dim strFilter As String
Dim varItem As Variant
Dim varItem2 As Variant
Dim lst1var As Variant
Dim cntState As Long
Dim strIN As String
strFilter = ""

If Not IsNull(Me!namesearch) Then strFilter = strFilter & " AND [institution] Like " & Chr(34) & "*" & Me!namesearch & "*" & Chr(34)
If Not IsNull(Me!citysearch) Then strFilter = strFilter & " AND [city] Like " & Chr(34) & "*" & Me!citysearch & "*" & Chr(34)

strIN = ""
If Me!lsttype.ItemsSelected.Count > 0 Then
  For Each varItem In Me!lsttype.ItemsSelected
      strIN = strIN & ", " & Chr(34) & Me!lsttype.ItemData(varItem) & Chr(34)
  Next
End If
If strIN <> "" Then
  strIN = Mid(strIN, 3)
  strFilter = strFilter & " AND [Type] IN (" & strIN & ")"
End If

strIN = ""
If Me!lstpublic.ItemsSelected.Count = 1 Then
  For Each varItem2 In Me!lstpublic.ItemsSelected
     strIN = strIN & ", " & Chr(34) & Me!lstpublic.ItemData(varItem2) & Chr(34)
  Next
End If
If strIN <> "" Then
  strIN = Mid(strIN, 3)
  strFilter = strFilter & " AND [Public] IN (" & strIN & ")"
End If

strIN = ""
For cntState = 1 To 9
   If Me("lst" & cntState).ItemsSelected.Count > 0 Then
      For Each lst1var In Me("lst" & cntState).ItemsSelected
          strIN = strIN & ", " & Chr(34) & Me("lst" & cntState).ItemData(lst1var) & Chr(34)
      Next
   End If
Next cntState

If strIN <> "" Then
  strIN = Mid(strIN, 3)
  strFilter = strFilter & " AND [State/Provience] IN (" & strIN & ")"
End If

If strFilter <> "" Then
  strFilter = Mid(strFilter, 6)
  Me.Form.Filter = strFilter
  Me.Form.FilterOn = True
Else
  Me.Form.FilterOn = False
End If
Debug.Print strFilter
End Sub
0
 
nexusnationAuthor Commented:
works now. thanks, andrew! there is a reason why you are a Top 15 in this area.

post a comment in that 50 pt question and i will award the points there to you as well.

thanks for your hard work,

andrew
0
 
nexusnationAuthor Commented:
why are my comments not showing? i just posted 1 or 2 comments and even after reloading, they aren't here (and i am using the "Reload this Question" link),

anyway, i was wondering if you could just...
1) explain to be what the Debug.Print does and if it is still needed or not
2) i am wondering what is the easiest way to make the filter apply automatically. i would think that putting the code into a function and running the function on the text boxes' change event and the list boxes' click event.

thanks,

andrew
0
 
TextReportCommented:
No problem!

1) Debug.Print puts the results in the immediate Window CTRL-G displays the immediate window in 2000 it is part of the VBA windows. If your code is in Break Mode then you can check the values of your variables in the immediate window by doing ?varName

2) Change the Code from a Sub to a Function then you can call the function directly by changing the AfterUpdate event of all relevant controls to =FunctionName() rather than using Event Procedures for them all.

With regards to your posting check out the community support area and possibly post a zero points question to get some feedback.

Cheers, Andrew
0
 
nexusnationAuthor Commented:
andrew,

debug.print...
what is it in Access 2002? i even turned on Access special keys, but CTRL-G doesn't work. do i need to set a breakpoint or something? and is the debug.print optional? it isn't even needed anymore, right?

automatically...
thanks, that's what i figured.

CS post...
not going to bother the mods right now, unless it happens again.

thanks,

andrew
0
 
TextReportCommented:
Yes for CTRL-G you need Use special Keys, alternatively go into the design of a module and select View Menu, Immediate Window watch out though it is normally visible at the bottom of your module window.

Cheers and Good Luck Andrew
0
 
nexusnationAuthor Commented:
problem:

the code is returning:

[institution] Like "**" AND [city] Like "**"[institution] Like "**" AND [city] Like "**"

or

[institution] Like "**" AND [city] Like "**"

both are followed by the other filter parts, if selections are made in the list boxes.

did i do something? Andrew

CODE:
***********************
Function Searching()
Dim strFilter As String
Dim varItem As Variant
Dim varItem2 As Variant
Dim lst1var As Variant
Dim cntState As Long
Dim strIN As String
strFilter = ""

If Not IsNull(Me!namesearch) Then strFilter = strFilter & " AND [institution] Like " & Chr(34) & "*" & Me!namesearch & "*" & Chr(34)
If Not IsNull(Me!citysearch) Then strFilter = strFilter & " AND [city] Like " & Chr(34) & "*" & Me!citysearch & "*" & Chr(34)

strIN = ""
If Me!lsttype.ItemsSelected.Count > 0 Then
 For Each varItem In Me!lsttype.ItemsSelected
     strIN = strIN & ", " & Chr(34) & Me!lsttype.ItemData(varItem) & Chr(34)
 Next
End If
If strIN <> "" Then
 strIN = Mid(strIN, 3)
 strFilter = strFilter & " AND [Type] IN (" & strIN & ")"
End If

strIN = ""
If Me!lstpublic.ItemsSelected.Count = 1 Then
 For Each varItem2 In Me!lstpublic.ItemsSelected
    strIN = strIN & ", " & Chr(34) & Me!lstpublic.ItemData(varItem2) & Chr(34)
 Next
End If
If strIN <> "" Then
 strIN = Mid(strIN, 3)
 strFilter = strFilter & " AND [Public] IN (" & strIN & ")"
End If

strIN = ""
For cntState = 1 To 9
  If Me("lst" & cntState).ItemsSelected.Count > 0 Then
     For Each lst1var In Me("lst" & cntState).ItemsSelected
         strIN = strIN & ", " & Chr(34) & Me("lst" & cntState).ItemData(lst1var) & Chr(34)
     Next
  End If
Next cntState

If strIN <> "" Then
 strIN = Mid(strIN, 3)
 strFilter = strFilter & " AND [State/Provience] IN (" & strIN & ")"
End If

If strFilter <> "" Then
    strFilter = Mid(strFilter, 6)
    Me.Form.Filter = strFilter
    Me.Form.FilterOn = True
Else
    Me.Form.FilterOn = False
End If
Debug.Print strFilter
End Function
0
 
TextReportCommented:
I suspect that the textbox is not null for some reason so change the lines

If Not IsNull(Me!namesearch) Then strFilter = strFilter & " AND [institution] Like " & Chr(34) & "*" & Me!namesearch & "*" & Chr(34)
If Not IsNull(Me!citysearch) Then strFilter = strFilter & " AND [city] Like " & Chr(34) & "*" & Me!citysearch & "*" & Chr(34)

to

If nz(Me!namesearch, "") <> "" Then strFilter = strFilter & " AND [institution] Like " & Chr(34) & "*" & Me!namesearch & "*" & Chr(34)
If Nz(Me!citysearch, "") <> "" Then strFilter = strFilter & " AND [city] Like " & Chr(34) & "*" & Me!citysearch & "*" & Chr(34)

Cheers, Andrew
0
 
nexusnationAuthor Commented:
that's what i figured, but i was unsure exactly what to do about it. i actually did this but it did not work:

If Not IsNull(Me!namesearch) Or Not Me.namesearch = "" Then strFilter = strFilter & " AND [institution] Like " & Chr(34) & "*" & Me!namesearch & "*" & Chr(34)
If Not IsNull(Me!citysearch) Or Not Me.citysearch = "" Then strFilter = strFilter & " AND [city] Like " & Chr(34) & "*" & Me!citysearch & "*" & Chr(34)


thanks again,

Andrew
0
 
TextReportCommented:
So is it working now?
0
 
nexusnationAuthor Commented:
yup. it is 100%. thanks a lot. if i have any problem with the code in the near future, i'll post here.

thanks,

Andrew
0
 
nexusnationAuthor Commented:
hmmm. no longer 100%. when i exit the text boxes (namesearch and citysearch controls) they seem to replace their text with spaces, but only sometimes. it seems to happen the most when there is no match in the search, but i have tried hundreds of times and yet i cannot locate a pattern at all.

i need an opinion here. perhaps use code to set strings to the values of the control and then set the values back again when done?

Andrew
0
 
TextReportCommented:
Do you want to send me a small example, there is nothing in the code that should be changing the values in the controls.

Cheers, Andrew
0
 
nexusnationAuthor Commented:
i'll drop you my DB to your email and you can experiment with it and see if you can generate the error like me.

i'll send it later today,

Andrew
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 22
  • 20
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now