Creating a search to populate a subset of records

[GlenS]

So here's the deal. I've been racking my brain for two days and haven't figured out the issue, so I'm hoping that the collective brain on this forum can come up with a solution.

I have a form that when initially displayed shows all client records in my database with the ability to add new ones through a "Add New" command button. I have another command button called "Search" that displays a second form containing a dozen or so fields to search and filter down on.

When the Search button is pressed, I display search form and enter data in some of the fields for searching. I then click ok and the main form should now display a subset of the records based on the data I entered on the search form.

My dilemma is that the WhereCondition of the docmd.OpenForm doesn't work. The doccmd works to open the form when I don't include the WhereCondition, but the moment I put the WhereCondition in, I get an unknown runtime error (3000).

Here's the code. I've stripped out most of the search fields to target just the one until I can get it working, then I'll add the others back in.

Private Sub cmdSearch_Click()
Dim varWhere As Variant

' Initialize the search string to Null
varWhere = Null

If Not IsNothing(Me.srchLastName) Then
varWhere = "([p_last_nam] LIKE '" & Me.srchLastName & "*')"
varWhere = (varWhere + " OR ") & "([s_last_nam] LIKE '" & Me.srchLastName & "*')"
End If

' Check to see that we built a filter
If IsNothing(varWhere) Then
MsgBox "You must enter at least one search criteria.", vbInformation, gstrAppTitle
Exit Sub
End If

' Open Clients filtered
' Note: if form already open, this just applies the filter
DoCmd.OpenForm "frmClients", acNormal, , WhereCondition:=varWhere , acFormPropertySettings, acWindowNormal
' Done
DoCmd.Close acForm, Me.Name

End Sub

I'm sure it's something simple, but...

Glen.

 

[jdraw]

Try replacing the IsNothing with IsNull.

 

[GlenS]

The IsNothing is a custom program that accounts for a number of different entries that a user can make that would be invalid for the field.

The issue is in the docmd.

DoCmd.OpenForm "frmClients", acNormal, , WhereCondition:=varWhere , acFormPropertySettings, acWindowNormal

When I include the WhereCondition parameter is when it fails. Here is a sample of what is included in the varWhere variable that is built within the If-Then test.

"([p_last_nam] LIKE 'Smith*') OR ([s_last_nam] LIKE 'Smith*')"

 

[jdraw]

didn't realize you had a routine called IsNothing.


see this for OPenForm where condition sample

 

[GlenS]

Thanks jdraw. Sorry I wasn't clearer in the OP. Based on your link, I redefined varWhere as a string and change the command as follows, but still get the runtime 3000 error on the docmd line.

DoCmd.OpenForm "frmClients", acNormal, , varWhere

 

[GlenS]

Any help on this is greatly appreciated....Anyone???