How to create an advanced search form for Access 2010

[jappianing]

Dear All,
I would be very grateful if anyone could be of a help to me.
I have designed an advanced search form which will be used to search for all our equipment in the Access 2010 database.
The form is based on a query called SearchEquipmentForm. The query itself was run using a UNION query called TESTTYPEOFEQUIPMENTBYBUILDING. The form which is called Search has a subform called SearchEquipmentForm subform.
The subform has the following fields:
Nomenclature
Mfr (manufacture)
Mfr part no
Model no
Bldg (this is building number)
Floor
Room
Group
Keyword
Type
Year ( the original field name was purchdate)

I have the following on the main search form:
Nomenclature
Mfr
Mfr part no
Modelno
Bldg
Floor
Room
Group
Keyword
Type
DateFrom
DateTo

Search, clear, close (command buttons on the main search form)
I want the user to be able to search using nomenclature, building, mfr, mfrpartno, modelno, keyword, group, type , DateFrom and DateTo
I used the following code (which I got from one of the World Access Forum postings on how to create advanced search form in MS Access 2007 and 2010).
Private Sub cmdClear_Click()
Me.SearchEquipmentForm_subform.Form.RecordSource = "SELECT * FROM searchequipmentform "
Me.SearchEquipmentForm_subform.Requery

txtnomenclature = ""
txtkeyword = ""
txtmodelno = ""
txtmfr = ""
txtmfrpartno = ""
txtbuilding = ""
txtfloor = ""
txtroom = ""
txtgroup = ""
txttype = ""
DateFrom = ""
dateTo = ""



End Sub



Private Sub cmdclose_Click()
DoCmd.Close
End Sub

Private Sub CmdSearch_Click()

On Error GoTo errr
Me.SearchEquipmentForm_subform.Form.RecordSource = "SELECT * FROM searchequipmentform " & BuildFilter
Me.SearchEquipmentForm_subform.Requery
Exit Sub
errr:

End Sub


Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"


Const conJetDate = "\#dd\|mm|/yyyy\#"
varWhere = Null

If Me.txtbuilding > "" Then
varWhere = varWhere & "[building] like " & Me.txtbuilding & " AND "
End If

If Me.txtnomenclature > "" Then
varWhere = varWhere & "[nomenclature] like " & tmp & search.txtnomenclature & tmp & " AND "
End If


If Me.txtbuilding > "" Then
varWhere = varWhere & "[building] like " & Me.txtbuilding & " AND "
End If


If Me.txtfloor > "" Then
varWhere = varWhere & "[floor] like " & Me.txtfloor & " AND "
End If

If Me.txtroom > "" Then
varWhere = varWhere & "[room] like " & Me.txtroom & " AND "
End If

If Me.txtgroup > "" Then
varWhere = varWhere & "[group] like " & tmp & search.txtgroup & tmp & " AND "
End If

If Me.txtkeyword > "" Then
varWhere = varWhere & "[keyword] like " & tmp & search.txtkeyword & tmp & " AND "
End If

If Me.txttype > "" Then
varWhere = varWhere & "[type] like " & tmp & search.txttype & tmp & " AND "
End If

If Me.txtdatefrom > "" Then
varWhere = varWhere & "([year] >=" & Format(search.txtdatefrom, conJetDate) & ") AND "
End If

If Me.txtdateto > "" Then
varWhere = varWhere & "([year] <= " & Format(search.txtdateto, conJetDate) & ") 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



But when the user clicks on the search button nothing happens.
The clear button works, but when the user clicks on it, it will ask the user to enter a value for Mfr part no, Model no and purchdate, before clearing the form.
The close button works perfectly.
I look forward to hearing from you all.

 

[John Big Booty]

In future when you post code please use the Code Tags, it will make you post a whole lot easier to read.

 

[jappianing]

Thanks John

 

[Alansidman]

I'm confused by your clear button code. Why have the requery cmd before you clear the text boxes. Why run the query before clearing? And you probably need a requery in your actual search code.

Anyway, look at this 10 minute video on a simple way to run a multi-search form on a query. It is a little simpler than yours and easy to set up and should give you the results you are looking for.

http://www.datapigtechnologies.com/flashfiles/searchform.html

Alan

 

[jappianing]

Dear All,
I would be very grateful if any of you could help.
I have designed an advanced search form which will be used to search for all our equipment in the Access 2010 database.
The form is based on a query called SearchEquipmentForm. The query itself was run using a UNION query called TESTTYPEOFEQUIPMENTBYBUILDING. The form which is called Search has a subform called SearchEquipmentForm subform.
The subform has the following fields:
Nomenclature
Mfr (manufacture)
Mfr part no
Model no
Bldg (this is building number)
Floor
Room
Group
Keyword
Type
Year ( the original field name was purchdate)

I have the following on the main search form:
Nomenclature
Mfr
Mfr part no
Modelno
Bldg
Floor
Room
Group
Keyword
Type
DateFrom
DateTo

I have search, clear and close buttons on the main search form.
I want the user to be able to search using nomenclature, building, mfr, mfrpartno, modelno, keyword, group, type , DateFrom and DateTo
I used the code below (which I got from one of the World Access Forum postings on how to create advanced search form in MS Access 2007 and 2010).
But when the user clicks on the search button nothing happens.
The clear button works, but whenever the user clicks on it, it will ask the user to enter a value for Mfr part no, Model no and purchdate, before clearing the form. The close button works perfectly.
Any form of assistance would be very much appreciated!



[Private Sub cmdClear_Click()
Me.SearchEquipmentForm_subform.Form.RecordSource = "SELECT * FROM searchequipmentform "
Me.SearchEquipmentForm_subform.Requery

txtnomenclature = ""
txtkeyword = ""
txtmodelno = ""
txtmfr = ""
txtmfrpartno = ""
txtbuilding = ""
txtfloor = ""
txtroom = ""
txtgroup = ""
txttype = ""
DateFrom = ""
dateTo = ""



End Sub



Private Sub cmdclose_Click()
DoCmd.Close
End Sub

Private Sub CmdSearch_Click()

On Error GoTo errr
Me.SearchEquipmentForm_subform.Form.RecordSource = "SELECT * FROM searchequipmentform " & BuildFilter
Me.SearchEquipmentForm_subform.Requery
Exit Sub
errr:

End Sub


Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"


Const conJetDate = "\#dd\|mm|/yyyy\#"
varWhere = Null

If Me.txtbuilding > "" Then
varWhere = varWhere & "[building] like " & Me.txtbuilding & " AND "
End If

If Me.txtnomenclature > "" Then
varWhere = varWhere & "[nomenclature] like " & tmp & search.txtnomenclature & tmp & " AND "
End If


If Me.txtbuilding > "" Then
varWhere = varWhere & "[building] like " & Me.txtbuilding & " AND "
End If


If Me.txtfloor > "" Then
varWhere = varWhere & "[floor] like " & Me.txtfloor & " AND "
End If

If Me.txtroom > "" Then
varWhere = varWhere & "[room] like " & Me.txtroom & " AND "
End If

If Me.txtgroup > "" Then
varWhere = varWhere & "[group] like " & tmp & search.txtgroup & tmp & " AND "
End If

If Me.txtkeyword > "" Then
varWhere = varWhere & "[keyword] like " & tmp & search.txtkeyword & tmp & " AND "
End If

If Me.txttype > "" Then
varWhere = varWhere & "[type] like " & tmp & search.txttype & tmp & " AND "
End If

If Me.txtdatefrom > "" Then
varWhere = varWhere & "([year] >=" & Format(search.txtdatefrom, conJetDate) & ") AND "
End If

If Me.txtdateto > "" Then
varWhere = varWhere & "([year] <= " & Format(search.txtdateto, conJetDate) & ") 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

][/CODE]

 

[jappianing]

Dear Alan,
Your video really happened me. Everything is working fine for me. My only concern now is how to incldue a date field on the form. On the original table there is a field called purchdate, but on the search form I want the user to be able to search by DateFrom and DateTo.
Your thoughts on this would be very much appreciated.

 

[Pat Hartman]

Add the controls for the fromDate and thruDate to the form. Make sure the Format property for each control is set to a date format. You might want to pre-populate these for the user in the FORM's Load Event
Me.txtFromDate = Date() -30
Me.txtThruDate = Date()
This will give you the last thirty days.
If you are using A2007 or newer, Access will automatically give you a popup calendar control because you set the control format to a date data type so it knows you probably want a calendar. It gives you an option to not display a calendar if that is your preference. (Note - if you use a mask - Access will NOT give you a calendar popup)
Then your query criteria would need to change to include -
AND purchdate Between Forms!yourform!txtFromDate AND Forms!yourform!txtThruDate

 

[jappianing]

Hello Pat,
Thanks so much for your guidance.