Code for initialising a NEW SEARCH on a search form

[vasnas]

hello,
im new to Access (got the 2007 version) and have very very limited knowledge of coding, macros, SQL etc...

I have created a Search Form based on a query, which has a lot of unbound text boxes, ticks etc for various data types (numbers, text, dates, Y/N etc). Also there is table at the bottom for listing the results after the SEARCH is done.

I just dont know how to write the code for a button that does a NEW SEARCH ie. clears all fields from any data/ticks/numbers etc and clears the results shown on the table. so that the user can search again from scratch.
Is there a universal way of doing this with some kind of code that works in many different cases, ie. adaptable to my case?

Here is a little bit of the (normal) SEARCH button's code in case you need it...

Private Sub Command4_Click()
Dim sql As String

sql = "SELECT Company.CID, CompanyName, Area, Geography, DID, DepartmentName FROM QRY_Departments WHERE 1=1 "
If Me.txt_CompanyName.Value <> "" Then
sql = sql & " AND CompanyName LIKE '" & Me.txt_CompanyName.Value & "*'"
End If
If Me.txt_Area.Value <> "" Then
sql = sql & " AND Area LIKE '" & Me.txt_Area.Value & "*'"
End If
If Me.cmb_Geo.Value <> "" Then
sql = sql & " AND Geography LIKE '" & Me.cmb_Geo.Value & "'"

etc.........

Me.lst_Results.RowSource = sql
Me.lst_Results.Requery

......................................
Many thanks!!! Vas

 

[danvlas]

On Error Resume Next
For Each ctr in Me.Controls
ctr= Null
Next
CurrentProject.Connection.Execute "Delete * From ResultsTable"
Me.ResultsSubform.Requery



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[vasnas]

Ok got it, thank you Daniel Vlas!!