Newbie: Running filter as function 1

[waldo7474]

Hi All!!

Hopefully you can help me. I am trying to run a search based on certain criteria. I would like the user to be able to fill in as much (or as little) criteria into different fields as they desire. I have five such fields that the user can put their criteria into. I want my database to look at the criteria, skip any null values that might be associated with the search and then spit out values that meet all the criteria. I have tried to do this a couple of ways, but now I am trying something new. Right now when I hit my Search button, nothing happens. I am hoping you can tell me what I am doing wrong. I need the search to run when the button is pressed, and then I need the filter to also work. Can you check my code to see if it should work, and then let me know if I am missing something. I am pretty new to this whole VBA thing.

Here is the code:

Private Sub SearchBtn_Click()
    
    Me.Filter = Lookup
    
    Me.FilterOn = True
    
End Sub

Function Lookup() As String

    multVar = 0     'This variable is to let the function know if there's more than one criteria
    
    If Not IsNull(PartNumberLookup) Then
        FilterString = "[PartNumber] Like '*" & PartNumberLookup & "*'"
        multVar = multVar + 1
    End If
    
    If Not IsNull(DescriptionLookUp) Then
        If multVar = 0 Then
            FilterString = "[Description] Like '*" & DescriptionLookUp & "*'"
        Else
            FilterString = FilterStrong & "AND [Description] Like '*" & DescriptionLookUp & "*'"
        End If
        multVar = multVar + 1
    End If
    
    If Not IsNull(FirstUsedOnLookup) Then
        If multVar = 0 Then
            FilterString = "[FirstUsedOn] Like '*" & FirstUsedOnLookup & "*'"
        Else
            FilterString = FilterStrong & "AND [FirstUsedOn] Like '*" & FirstUsedOnLookup & "*'"
        End If
        multVar = multVar + 1
    End If
    
    If Not IsNull(OldPartNumberLookup) Then
        If multVar = 0 Then
            FilterString = "[OldPartNumber] Like '*" & OldPartNumberLookup & "*'"
        Else
            FilterString = FilterStrong & "AND [OldPartNumber] Like '*" & OldPartNumberLookup & "*'"
        End If
        multVar = multVar + 1
    End If
    
    If Not IsNull(WrongPartNumberLookup) Then
        If multVar = 0 Then
            FilterString = "[WrongPartNumber] Like '*" & WrongPartNumberLookup & "*'"
        Else
            FilterString = FilterStrong & "AND [WrongPartNumber] Like '*" & WrongPartNumberLookup & "*'"
        End If
        multVar = multVar + 1
    End If
    
End Function

Any help is greatly appreciated. I am about to pull my hair out with this. Hopefully what I am trying to do makes sense.

Thanks in advance!!

 

[ajdesalvo]

One thing I noticed is

FilterString = FilterStrong &

should be:

FilterString = FilterString & Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[waldo7474]

Thanks for the help Anthony, and the quick response.

I changed all the FilterStrongs to FilterStrings and nothing changed. I still hit the "Search Button" and nothing happens. It acts like the button doesn't do anything. Am I missing something? If you have any more suggestions I would love to hear them.

Thanks again for the help in advance.

 

[ajdesalvo]

Instead for using the filter, let's try setting the form's recordsource.

Take the Lookup function code and place it in the search button's On Click event:

multVar = 0 'This variable is to let the function know if there's more than one criteria

If Not IsNull(Me!PartNumberLookup) Then
FilterString = "[PartNumber] Like '*" & Me!PartNumberLookup & "*'"
multVar = multVar + 1
End If

If Not IsNull(Me!DescriptionLookUp) Then
If multVar = 0 Then
FilterString = "[Description] Like '*" & Me!DescriptionLookUp & "*'"
Else
FilterString = FilterString & "AND [Description] Like '*" & Me!DescriptionLookUp & "*'"
End If
multVar = multVar + 1
End If

If Not IsNull(Me!FirstUsedOnLookup) Then
If multVar = 0 Then
FilterString = "[FirstUsedOn] Like '*" & Me!FirstUsedOnLookup & "*'"
Else
FilterString = FilterString & "AND [FirstUsedOn] Like '*" & Me!FirstUsedOnLookup & "*'"
End If
multVar = multVar + 1
End If

If Not IsNull(Me!OldPartNumberLookup) Then
If multVar = 0 Then
FilterString = "[OldPartNumber] Like '*" & Me!OldPartNumberLookup & "*'"
Else
FilterString = FilterString & "AND [OldPartNumber] Like '*" & Me!OldPartNumberLookup & "*'"
End If
multVar = multVar + 1
End If

If Not IsNull(Me!WrongPartNumberLookup) Then
If multVar = 0 Then
FilterString = "[WrongPartNumber] Like '*" & Me!WrongPartNumberLookup & "*'"
Else
FilterString = FilterString & "AND [WrongPartNumber] Like '*" & Me!WrongPartNumberLookup & "*'"
End If
multVar = multVar + 1
End If

'Set the form's recordsource
Forms![Search Formname].RecordSource = "Select * FROM [Form Recordsource] WHERE " & FilterString

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[waldo7474]

I'm sorry Anthony. I really don't know how to put that function in to the OnClick event. Whenever I try to get around having Private Sub routine, then it tells me that my OpenForm event is screwed up. I am not sure why this happens. Right now, I have in the OnClick event the filter and the filter turned on. Then the theory is that the filter calls out the function.

If you could help me out with this it would be greatly appreciated.

Thanks
Wally

In the code above, I tried to show the subroutine and the function. This is the order in which it appears in the module. There are some other subroutines above it, but nothing below it. Thanks again for all your help!

 

[vbajock]

Just a tip -
Turn on Variable Declaration or those variable misspellings will drive you nuts - in the code editor, click Tools, Options and make sure Require Variable Declaration is turned on, or put Option Explicit as the first line in your code modules. Then use the DIM statement to declare the variables you are going to use and give them the correct data type. Otherwise debugging your code will be no fun.

 

[ajdesalvo]

vbajock is correct on turning on the variable declaration function.

Remove from Private Sub SearchBtn_Click:

Me.Filter = Lookup
Me.FilterOn = True

You will then need to dimension the variables used in the code:

Private Sub SearchBtn_Click()

Dim MultVar as Integer
Dim FilterString as String

Then copy the code I sent from above.

Then end the sub routine:

End Sub

Remove the Lookup Function all together.

Select Debug, then compile to see if there any extra end subs, etc.

So what you are doing is taking the filter stuff out of the On Click you had originally, then adding the Lookup funstion code in the On Click event. Then the Lookup function can be removed.

Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[waldo7474]

That fixed it!!!!

Anthony you are THE MAN!!

Let me ask you one more question, if I may. Once I put in my search criteria, and hit the search button. All of the header controls (the lookups) are all locked until I get to the end of the recordset (a new record). I have locked all the controls in the form in their properties. Do you have any idea why the other controls are locked? If there is nothing else I can do, I could add a button labeled "New Search" and just have that button go to a new record, but this doesn't seem right. Any suggestions!!!

Thanks for all the previous help. You definitely get a STAR!!!
Wally