Problem with displaying search results in subform

[ShawnCartwright]

Hi there,

Im new to this site. Im hoping you can help, Ive tried for about a week now and close to tears lol. Im trying to get the search to pull information from textboxes and populate the form with the information. Ive had success in other databases but this one does seem to want to work and ive gone to the edge of screaming. Perhaps you can help please?

Private Sub cmdSearch_Click()
On Error GoTo errr
Me.tblsubCustomerInformation.Form.RecordSource = "SELECT * FROM tblCustomerInformation " & BuildFilter
Me.tblsubCustomerInformation.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub


Private Function BuildFilter() As Variant


Dim varWhere As Variant


Dim CustomerNumber As String
Dim CustomerFirstName As String
Dim CustomerAddress1 As String
Dim CustomerPhone As String


CustomerNumber = """"
CustomerFirstName = """"
CustomerAddress1 = """"
CustomerPhone = """"


varWhere = Null


If Me.txtCustomerNumber > "" Then
varWhere = varWhere & "[CustomerNumber] like " & CustomerNumber & Me.txtCustomerNumber & CustomerNumber & " AND "
End If


If Me.txtCustomerFirstName > "" Then
varWhere = varWhere & "[CustomerFirstName] like " & CustomerFirstName & Me.txtCustomerFirstName & CustomerFirstName & " AND "
End If


If Me.txtCustomerAddress1 > "" Then
varWhere = varWhere & "[CustomerAddress1] like " & CustomerAddress1 & Me.txtCustomerAddress1 & CustomerAddress1 & " AND "
End If


If Me.txtCustomerPhone > "" Then
varWhere = varWhere & "[CustomerPhone] like " & CustomerPhone & Me.txtCustomerPhone & CustomerPhone & " AND "
End If




If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function

[pbaldy]

You haven't said what the problem is, but we can start here and find out what the function is returning:

BaldyWeb-Immediate window

[ShawnCartwright]

sorry lol. Its not populating the subform with the information I input in the textboxes. Its just going blank or bring up the first entry in the table only.

[pbaldy]

What's the result of the Debug.Print?

[ShawnCartwright]

What's the result of the Debug.Print?

Nothing. :/

[pbaldy]

Presuming you used it correctly, that would mean the function isn't returning anything. Try setting a breakpoint and following the code so you can see what the variables contain at each step of the process.

http://www.baldyweb.com/Debugging.htm

[ShawnCartwright]

Presuming you used it correctly, that would mean the function isn't returning anything. Try setting a breakpoint and following the code so you can see what the variables contain at each step of the process.

http://www.baldyweb.com/Debugging.htm

database was corrupt had to import it all into a fresh one. Thank you for your help.

[pbaldy]

Glad you got it sorted out.