Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2009
    Posts
    4

    Unanswered: Search form with results in subform--need help with code!

    Hello!!!!

    I am trying to create a search form in Access. I have the following “unbound” fields:


    Project Number as a text box
    ARRA Number as a text box
    State Number as a text box
    County as a multi-select box with 96 possible selections
    Region as a multi-select box with 4 possible selections
    Project Type as a multi-select box with 24 possible selections


    Everything is set to text. I’ve seen this work with bound and unbound fields. Since this is unbound, I’ve told Access where I want to pull from. You’ll see that in the code as well.



    Here’s where the code is breaking. I am receiving the following error: Run time error 5, Invalid Procedure call or argument.



    The following is the procedure with the error in RED-------

    Private Sub Command24_Click()
    Const CInvalidDateError As String = "You have entered an invalid date."
    Dim strFilter As String
    Dim varItem As Variant

    strFilter = ""

    'If ARRA #
    If Not IsNull(Me!ARRA) Then
    strFilter = strFilter & "qryMetrics.[Project Number] Like " & Chr(34) & "*" & Me!ARRA & "*" & Chr(34) & " AND "
    End If

    'If FMIS #
    If Not IsNull(Me!FMIS) Then
    strFilter = strFilter & "qryMetrics.[FMIS#] Like" & Chr(34) & "*" & Me!FMIS & "*" & Chr(34) & "AND "
    End If

    'If State #
    If Not IsNull(Me!State) Then
    strFilter = strFilter & "qryMetrics.[State#] Like" & Chr(34) & "*" & Me!State & "*" & Chr(34) & "AND"
    End If

    'If Date From
    If IsDate(Me!DateFrom) Then
    strWhere = strWhere & "AND" & "qryMetrics.[InspectionDate] >= " & GetDateFilter(Me!DateFrom)
    ElseIf Nz(Me!DateFrom) <> "" Then
    strError = CInvalidDateError
    End If

    'If Date To
    If IsDate(Me!DateTo) Then
    strWhere = strWhere & "AND" & "qryMetrics.[InspectionDate] >= " & GetDateFilter(Me!DateTo)
    ElseIf Nz(Me!DateTo) <> "" Then
    strError = CInvalidDateError
    End If

    'If County
    If Me!County.ItemsSelected.Count > 0 Then
    strFilter = strFilter & "("
    For Each varItem In Me!County.ItemsSelected
    strFilter = strFilter & "qryMetrics.[County Name]=" & Chr(34) & Me!County.ItemData(varItem) & Chr(34) & " OR "
    Next
    strFilter = Left$(strFilter, Len(strFilter) - 97) & ") And "
    End If

    'If Region
    If Me!Region.ItemsSelected.Count > 0 Then
    strFilter = strFilter & "("
    For Each varItem In Me!Region.ItemsSelected
    strFilter = strFilter & "qryMetrics.[Region]=" & Chr(34) & Me!Region.ItemData(varItem) & Chr(34) & " OR "
    Next
    strFilter = Left$(strFilter, Len(strFilter) - 5) & ") AND "
    End If

    'If Project Purpose
    If Me!ProjectPurpose.ItemsSelected.Count > 0 Then
    strFilter = strFilter & "("
    For Each varItem In Me!ProjectPurpose.ItemsSelected
    strFilter = strFilter & "qryMetrics.[Project Purpose]=" & Chr(34) & Me!ProjectPurpose.ItemData(varItem) & Chr(34) & " OR "
    Next
    strFilter = Left$(strFilter, Len(strFilter) - 25) & ") AND "
    End If

    'If Status
    If Me!Status.ItemsSelected.Count > 0 Then
    strFilter = strFilter & "("
    For Each varItem In Me!Status.ItemsSelected
    strFilter = strFilter & "qryMetrics.[Status]=" & Chr(34) & Me!Status.ItemData(varItem) & Chr(34) & " OR "
    Next
    strFilter = Left$(strFilter, Len(strFilter) - 4) & ") AND "
    End If

    'If Employees
    If Me!Employees.ItemsSelected.Count > 0 Then
    strFilter = strFilter & "("
    For Each varItem In Me!Employees.ItemsSelected
    strFilter = strFilter & "qryMetrics.[EmployeeID]=" & Chr(34) & Me!Employees.ItemData(varItem) & Chr(34) & " OR "
    Next
    strFilter = Left$(strFilter, Len(strFilter) - 30) & ") AND "
    End If

    If strFilter <> "" Then
    strFilter = Left$(strFilter, Len(strFilter) - 10)
    End If

    If strFilter = "" Then
    Me!sfrmSearch.Form.FilterOn = False
    Else
    Me!sfrmSearch.Form.Filter = strFilter
    Me!sfrmSearch.Form.FilterOn = True
    End If

    End Sub

    __________________________________________________ ______________________

    Function GetDateFilter(dtDate As Date) As String
    ' Date filters must be in MM/DD/YYYY format
    GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY hh:mm:ss AM/PM") & "#"
    End Function

    Any help on this would be fantastic. Thank you in advance!!!!

    GSimmons
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is that all the error says? What is the length of strFilter at the time this line executes?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What is that line intended to do? Subtracting 97 seems arbitrary, and likely to result in a negative number. Also, I would build an IN clause, as it will end up being much shorter. This isn't exactly the same as what you're doing, but demonstrates building an IN clause instead of repeated OR's:

    Multi-Select Listbox
    Paul

  4. #4
    Join Date
    Dec 2009
    Posts
    4
    Quote Originally Posted by Teddy View Post
    Is that all the error says? What is the length of strFilter at the time this line executes?
    Teddy,

    Yes, that is all the error states. How can I determine the length of strFilter at the time the line executes? I know that doesn't exactly help you, but I honestly don't know how to determine that..

    Thanks Teddy for the response!

    Greg

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Sorry Teddy, your post wasn't there when I started. I've demonstrated my slow typing skills apparently.
    Paul

  6. #6
    Join Date
    Dec 2009
    Posts
    4
    Quote Originally Posted by pbaldy View Post
    What is that line intended to do? Subtracting 97 seems arbitrary, and likely to result in a negative number. Also, I would build an IN clause, as it will end up being much shorter. This isn't exactly the same as what you're doing, but demonstrates building an IN clause instead of repeated OR's:

    Multi-Select Listbox
    Hey Paul,

    From what I gather, this particular expression checks the multiselect list box. The user might have chosen more than one item. This loops through the items in the list box to see which items have been selected if any. That was my first impression as well, the 97 being arbitrary that is....thanks Paul.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm well aware of what the loop does, just not that line with the 97. If I'm a betting man, I'm thinking you want this:

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

    But I would still switch to the IN method. I've heard of people running into string length limits, and that looks like a prime candidate.
    Paul

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by gsimmons77 View Post
    Teddy,

    Yes, that is all the error states. How can I determine the length of strFilter at the time the line executes? I know that doesn't exactly help you, but I honestly don't know how to determine that..

    Thanks Teddy for the response!

    Greg
    Check the help documentation IN THE CODE EDITOR for "breakpoints" and "intermediate window". It will make your life much, much easier.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Dec 2009
    Posts
    4
    Quote Originally Posted by pbaldy View Post
    I'm well aware of what the loop does, just not that line with the 97. If I'm a betting man, I'm thinking you want this:

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

    But I would still switch to the IN method. I've heard of people running into string length limits, and that looks like a prime candidate.
    Paul,

    It seems switching to that has worked, but I run into another problem: I get an "invalid" argument statement that traces back to--

    ELSE
    Me!sfrmSearch.Form.Filter = strFilter

    at the bottom of the code. "SfrmSearch" is the subform I've created that gives me the results in datasheet view. Can that be remedied? For my own edification, could you provide more details concerning the IN method? If that would take too long, perhaps you could point me in the right direction. As always, you've been most helpful and thank you for your time!

    Greg

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I note that you have similar issues down below the one we talked about, like:

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

    The way to check it would be to set a breakpoint as Teddy recommended, and type

    ?strFilter

    in the immediate window, so you can see if the value of the string is what you think it is. There is also info on that here:

    Debugging

    As to using IN, I already gave you sample code building it. Basically you'd be building this:

    FieldName IN(1,2,3)

    instead of

    FieldName = 1 OR FieldName = 2 OR FieldName = 3

    As you can see, the more values that are selected the greater the savings in string length.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •