Search form with multiple controls

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am making a search form, frmSearchIt. I have 11 unbound text controls on
the form each of which I named based on the field they are searching in their
respective tables:
txtCaseNum, txtFirstName, txtLastName, txtCompany, txtOfficer, txtTracerNum,
txtTIN, txtOpenDateFrom, txtOpenDateTo, txtCloseDateFrom, txtCloseDateTo

Three buttons are on the form, btnBack, btnClear, btnSearch.

What I want is when the user hits btnSearch the form takes the information
entered into the controls, if any, finds records matching and in the form
footer should display only 5 fields of those matching records along with a
way to click on a matching record and open another form, frmCase, which
displays just that record.

Just in case it makes a difference:
-All ‘Date’ fields in the tables are Date/Time data types, while fiends such
as txtCaseNum, txtTracerNum, and txtTIN, are text data types in the table.
-CaseNum is the primary key for the main table and secondary key for most of
the other tables.
-The main form, frmCase, has a bunch of subforms in it that are created at
the same time frmCase is created using field CaseNum. I havn’t figured out
how to delete them all at once but that is another issue.
-All the controls used on the search form, frmSearchIt, come from different
tables, but there are 9 tables so I will leave them out unless they are asked
for.
 
What is your question?
Its best to ask a specific question than to describe an entire application
and expect someone to write it for you.
To perform your search, attach a click event to the search button. In the
procedure form an SQL string based on the contents of the search controls and
create a recordset based on the results. You then populate whatever control
is to hold the search results based on the recordset returned.

-Dorian
 
This is doable, but will take some work. What you need to do is build an SQL
string based on whether a value is entered in any of the fields, then use
that string as the recordset for your search. Here is some "air" code that
might give you an idea:

Dim dbf as Database
Dim rst as Recordset
Dim strSQL as String
Dim strWhere as String

strSQL = "SELECT fld1, fld2, fld3, fld4, fld5 FROM MyTable WHERE "
If Not IsNull(Me.txtCaseNumber) Then
strWhere = "CaseNumber = '" & Me.txtCaseNumber & "'"
End If
If Not IsNull(Me.txtFirstName) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "FirstName = '" & Me.txtFirstName & "'"
End If
.........
If Not IsNull(Me.txtCloseDateTo) Then
If Len(strWhere) > 0 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "CloseDateTo = #" & Me.txtCloseDateTo & "#"
End If

strSQL = strSQL & strWhere & ";"
set dbf = CurrentDb
set rst = dbf.OpenRecordset(strSQL)
 
Actually my problem is I am not sure where to start.
But after reading your reply and trying to look at similar forms I see that
the button can only create a SQL string. My knowledge of SQL statements is
novice at best, but as far as I can understand the code would go something
like this:
How do I take the string in the below code and turn it into an SQL statement?

Private Sub btnSearch_Click()
'Create string to become the SQL query
Dim strSQLSearch As string

If Not IsNull(Me.txtCaseNum) Then
'I am not sure the syntax to assign the value in txtCaseNum
'to the string but here goes
strSQLSearch= strSQLSearch "AND" Me.txtCaseNum
End If
End Sub
 
In answer to your follow up question, yes, you will need to use SQL. There
is really no other way to accomplish this. You can get a head start by
creating a query with the query builder that included all the tables, fields,
joins, etc, but without any WHERE condition. This will create the most
difficult part for you. Now, to get it into your program. To do this, you
will have to read the query's sql and use it in your program. Here is the
way I do that:
1. Create the query. Save and Close it.
2. Open your VBA editor where you will want to put the sql.
3. In the immediate window:

Debug.Print CurrentDB.QueryDefs("QueryNameHere").SQL
The sql created by the query builder will display in the immediate window.

4. Copy the displayed string
5. Paste it into your code. It will likely be very long and need to be cut
up into smaller pieces so it will fit on a screen. Put it in the example I
gave you for strSQL = "......"
6. Do the strWhere part as in my example.
 
This has been tremendously helpful, thank you. However I am not sure what
"Dim dbf as Database" does? When I click the button it gives me a compile
error saying, "User-defined type not defined" and highlights that line.
Thank you so far I am new to this so I am not sure what information to give.
 
Database is an object in the DAO object model. If you're running into the
error you're getting, that implies you don't have a reference set to DAO (by
default, Access 2000 and 2002 don't set that reference).

While you're in the VB Editor, select Tools | References from the toolbar.
Scroll through the list of available references until you find the Microsoft
DAO 3.6 Object Library. Select it, and back out of the dialog.

Change the declaration from Dim rst as Recordset to Dim rst as DAO.Recordset
(since there's also a Recordset object in the ADO model)
 
*Angelic music plays in the background* None of us in the office could see
why this was not working. THANK YOU ALL so much for your help! Just a few
more bugs to work out, think I can handle those and this DB can finally go
into production.

Thank you again
 
Back
Top