I need guidance on creating a professional looking form w/ Search

[Guest]

I need guidance on creating a professional looking form with advanced search
capabilities.

I'm basically querying off of one table. This table is pretty wide. I'd
like a form that allows users to 1)Search for a particular record (or group
of records) in the table, 2) have the search results show up in a subform on
that same form, 3) be able to edit them, and 4) this is related to 1 but I'd
like the search criteria for the query to come from a a couple of combo boxes
and a text box, any of which can be empty.

So for example, if a user wanted to see all records, they could leave all
search criteria blank and the results of the query would populate a subform
(?). Or if they do select search criteria, all query results would show up
in a subform. When these results show up, I would then like them to be able
to select a record that takes them to a different form where they can then
edit the record.

This may be easy if someone has a similar form as the one I'm describing as
an example for me to go off of.

Thanks in advance for your assistance.

 

[Guest]

Since I don't know your table or the search rules, I can only give you a
general approach, but it is one I have used.
First, create a query that returns all the rows in your table. Dont use
SELECT *, you want to name each column.
You will want 2 copies of this query. One with the prefix qsel
(qselMyTableName) and another with the prefix zsqsel (zaqselMyTableName).
You can use any prefix you like, but you need the two. The zs in my naming
convention means it is a template. It will be used to make the query you
will actually use.

Use the qsel version as the record source for your form

Now, in your form, once the user has made the selection criteria, you will
have to create a Where condition based on his input. Once you have done
that, you retrieve the SQL from the template query (zsqsel), add the Where
condition to it, and write it out to the production query(qsel) and requery
your form.

Here is an example of how to retrieve the template SQL and write it out to
the production SQL:

Dim strFind As String 'The Query Value to Look for
Dim strReplace As String 'The Query Value to Replace what we found
Dim strSQL As String 'The Query's SQL to update
Dim dbf As Database 'Database Object Reference
Dim qdfs As QueryDefs 'Query Collection Definition Object Reference
Dim qdf As QueryDef 'Query Definition Object Reference to Fix
Dim qdfTmp As QueryDef 'To get the template SQL to start with

Set dbf = CurrentDb
Set qdfs = dbf.QueryDefs

Set qdfTmp = qdfs("zsqselPActualLJG7")
Set qdf = qdfs("PActualLJG7")
'Get the template SQL
strSQL = qdfTmp.Sql
'Make the Changes
strFind = "Mon2"
strReplace = strCurrMonth & "2"
strSQL = Replace(strSQL, strFind, strReplace)
'Saves the SQL
qdf.Sql = strSQL
Set qdf = Nothing
Set qdfTmp = Nothing

 

[Immanuel Sibero]

Hi SChase,
Head on over to the Access Web:

http://www.mvps.org/access/forms/frm0045.htm


HTH,
Immanuel

 

[Albert D.Kallal]

If you are going to code a form to search, then you can try the following:

Build a unbound form. Place a text box near the top. Lets call the control
txtLastName. Turn just about everything off for this form (record selector,
navigation buttons etc). This form is NOT attached to a table, nor any data
(we call this a un-bound form).

In addition to the above, you an build nice continues form that displays the
columns of data in a nice list. you can then put this form as a sub-form
into the above form.

Then, in the txtLastName (our text box to type in the search)
after update event you simply stuff the results
into that sub-form.


dim strSql as string


strSql = "select * from tblCustomer where LastName like '" & me.txtLastName
& "*'"


me.MySubFormname.Form.RecordSource = strSql.

There is very litlte code thus to do this....

You can see some screen shots here that uses the above idea:

http://www.members.shaw.ca/AlbertKallal/Search/index.html