How to write vba Multi field search form

alhabkk

Registered User.
Local time
Today, 05:39
Joined
Sep 9, 2013
Messages
49
I have a table name ex. equipment and many fields inside ex. date, equipment name (combo box) and others.

I need to make form for all fields and button search once I click on it will open a report depending on a fields i selected.

I think that I have to use VBA

help me
 
Paul im not that much good with codes !
I use this vba code but there is one problem, it not take the values i put it in boxs!

Private Sub Toggle3_Click()
Dim strReport As String
Dim strDateField As String
Dim lngLen As Long
Dim strWhere As String
Dim lngView As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"


strReport = "rptFLM"
strDateField = "[Date]"
lngView = acViewReport



If Not IsNull(Me.txtlocation) Then
strWhere = strWhere & "([Location] Like ""*" & Me.txtlocation & "*"") AND "
End If

If Not IsNull(Me.txtDateFrom) Then
strWhere = strWhere & "([Date] >= " & Format(Me.txtDateFrom, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtDateTo) Then
strWhere = strWhere & "([Date] < " & Format(Me.txtDateTo, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtTagNumber) Then
strWhere = strWhere & "([Tag Number] Like ""*" & Me.txtTagNumber & "*"") AND "
End If

If Not IsNull(Me.txtCreatedby) Then
strWhere = strWhere & "([Created by] Like ""*" & Me.txtCreatedby & "*"") AND "
End If

If Not IsNull(Me.txtJSA1) Then
strWhere = strWhere & "([JSA / Procedure] Like ""*" & Me.txtJSA1 & "*"") AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
End If

If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If


DoCmd.OpenReport strReport, lngView, strWhere
Exit_Handler:
Exit Sub

End Sub
 
What is the result of the Debug.Print? Try this:

DoCmd.OpenReport strReport, lngView, , strWhere
 
Replace the IsNull with =""
Code:
If Not IsNull(Me.YourControlName) Then
Code:
If Not Me.YourControlName = "" Then
It is 6 replaces.
And as Paul wrote:
DoCmd.OpenReport strReport, lngView, , strWhere
 
Well, this test would be even better since it handles both Null & ZLS:

If Len(Me.SomeControl & vbNullString) > 0 Then
 

Users who are viewing this thread

Back
Top Bottom