?
Solved

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

Posted on 2003-04-01
44
Medium Priority
?
300 Views
Last Modified: 2008-02-01
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
Comment
Question by:nexusnation
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 22
  • 20
  • +1
44 Comments
 
LVL 17

Expert Comment

by:JezWalters
ID: 8245900
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8246680
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
 
LVL 2

Expert Comment

by:Charityg
ID: 8246723
If you could send it to me in zip format, I'd love to take a look.
cgaborik@christensenfarms.com
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 28

Expert Comment

by:TextReport
ID: 8247254
Nex, chuck me a copy of your sample to andrew@textreport.com

Cheers, Andrew
0
 
LVL 12

Author Comment

by:nexusnation
ID: 8247666
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8248049
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8248118
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8248274
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8248277
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8248423
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8248907
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8248917
note the names of the listboxes (in the new version) are lst1, lst2, lst3, lst4, etc.

thanks,

andrew
0
 
LVL 28

Expert Comment

by:TextReport
ID: 8248957
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8248969
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8248994
If you are only going to use a single selection then can you not turn off the multiple select option?

Cheers, Andrew
0
 
LVL 28

Expert Comment

by:TextReport
ID: 8249059
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8249086
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8249115
>>>>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
 
LVL 12

Author Comment

by:nexusnation
ID: 8249189
i tryed the new code on 4/01/03 1:41 PM. parameter boxes are coming up for "namesearch" and "citysearch"
0
 
LVL 28

Expert Comment

by:TextReport
ID: 8249303
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8249330
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8255172
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8257015
I have been out for the evening, watching football (soccer if your States side).

So what is outstanding on this now?

Cheers, Andrew
0
 
LVL 12

Author Comment

by:nexusnation
ID: 8258713
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8260192
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8260207
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8262563
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8262787
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8263431
No problem and have a nice trip. Cheers, Andrew
0
 
LVL 12

Author Comment

by:nexusnation
ID: 8293630
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
 
LVL 28

Accepted Solution

by:
TextReport earned 1400 total points
ID: 8294226
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8294276
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8294418
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8294630
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8294704
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8294738
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8296195
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8297351
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8298811
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8300718
So is it working now?
0
 
LVL 12

Author Comment

by:nexusnation
ID: 8300800
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8424989
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
 
LVL 28

Expert Comment

by:TextReport
ID: 8429329
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
 
LVL 12

Author Comment

by:nexusnation
ID: 8431319
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question