I have created a multi-Search Form to search through an Excel Document I've imported to Access, but after attempting 1 search when running the form, the SubForm doesn't reset back to listing all of the items in the excel document when I attempt to click the Clear or Search button.
Here is the code I have:
Private Sub cmdClear_Click()
Me.WireDataFinalSubForm.Form.RecordSource = "SELECT * FROM WireDataFinal "
Me.WireDataFinalSubForm.Requery
txtVolts = ""
txtTemp = ""
txtWL = ""
txtDiameter = ""
txtGauge = ""
txtOpen = ""
txtJacket = ""
txtCond = ""
txtStrip = ""
txtVolts.SetFocus
End Sub
Private Function BuildSearch() As Variant
Dim varZero As Variant
Dim strZero As String
strZero = """"
varZero = Null
If Me.txtVolts > "" Then
varZero = varZero & "[Volts] >= " & Me.txtVolts & " AND "
End If
If Me.txtTemp > "" Then
varZero = varZero & "[Temp] >= " & Me.txtTemp & " AND "
End If
If Me.txtWL > "" Then
varZero = varZero & "[Weight (lb/in)] like " & Me.txtWL & " AND "
End If
If Me.txtDiameter > "" Then
varZero = varZero & "[Diameter (in)] like " & Me.txtDiameter & " AND "
End If
If Me.txtGauge > "" Then
varZero = varZero & "[Gauge (AWG)] like " & Me.txtGauge & " AND "
End If
If Me.txtOpen > "" Then
varZero = varZero & "[Open/Protected] like " & Me.txtOpen & " AND "
End If
If Me.txtJacket > "" Then
varZero = varZero & "[Outter Jacket] like " & Me.txtJacket & " AND "
End If
If Me.txtCond > "" Then
varZero = varZero & "[Conductor Type] like " & Me.txtCond & " AND "
End If
If Me.txtStrip > "" Then
varZero = varZero & "[Stripper Tool] like " & Me.txtStrip & " AND "
End If
If IsNull(varZero) Then
varZero = ""
Else
varZero = "WHERE " & varZero
If Right(varZero, 5) = " AND " Then
varZero = Left(varZero, Len(varZero) - 5)
End If
End If
BuildSearch = varZero
End Function
Private Sub cmdClose_Click()
DoCmd.Close
End Sub
Private Sub cmdSearch_Click()
On Error GoTo errr
Me.WireDataFinalSubForm.Form.RecordSource = "SELECT * FROM WireDataFinal " & BuildSearch
Me.WireDataFinalSubForm.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub
I am assuming it has something to do with my IsNull function, but I haven't been able to figure the right way to go about fixing it.
Any advice / help would be much appreciated.