Database Search Facility

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

Guest

Hello,

I hope this is the right forum for this question, if not please suggest
where I should place it. I've just worked through the Step-by-step Microsoft
Access 2002 guide and feel comfortable creating simple queries, forms,
reports and tables.

But the main activity I want to do on an existing database is to create a
flexible search facility into which a user can search on:-

- partial reference numbers
- multiple keywords which might be found in text
- records which were added in certain date ranges

I don't really know how to start doing this. I can create a query which does
this but I want other users to use a friendly form instead and I'm not sure
how to make the form allow the search methods I've mentioned.

I'd appreciate any advice or even better a reference form and query I could
look at. I've programmed in VBA for Excel before but never for Access.

Hope this all makes sense. Thanks for any suggestions.

Cheers,

Alasdair
 
Dear Alasdair.

I am having the exact same problem in Access. I'm currently building a
bibliographic system for our company. This system requires several
user-friendly "criteria" forms. A static query is not enough.

What I specifically want to do is construct a main form with a subform. The
upper part of the main form will have criteria fields and a search button.
When the user presses the search button, all the criteria entered are used to
query the data. The results of the search will appear as a list of records in
the subform. The subform will appear as a datasheet. I can build the main and
subforms quite easily. Where I stumble is in setting up the criteria fields
in the main form and linking them to a query.

If anyone can help us both, we would be grateful.

Dirk Schmid M.Sc.
Data Analyst / Environmental Microbiologist
North/South Consultants Inc.
Winnipeg, Canada
 
Alasdair said:
I hope this is the right forum for this question, if not please suggest
where I should place it. I've just worked through the Step-by-step Microsoft
Access 2002 guide and feel comfortable creating simple queries, forms,
reports and tables.

But the main activity I want to do on an existing database is to create a
flexible search facility into which a user can search on:-

- partial reference numbers
- multiple keywords which might be found in text
- records which were added in certain date ranges

I don't really know how to start doing this. I can create a query which does
this but I want other users to use a friendly form instead and I'm not sure
how to make the form allow the search methods I've mentioned.

I'd appreciate any advice or even better a reference form and query I could
look at. I've programmed in VBA for Excel before but never for Access.
Click to expand...


I'm sure you can find various examples/discussions on this
topic by searching the Google newsgroup archives.

Typically (whatever that means), your search form would use
a command button to open the filtered data form. The
button's click event procedure would then check the search
form's "criteria" controls and construct the needed where
clause. Here's some example air code:

Dim strWhere As String

If Not IsNull(Me.txtb1) Then 'exact match in textfield
strWhere = strWhere & " AND textfield = """ _
& Me.txtb1 & """"
End If
If Not IsNull(Me.txtb2) Then 'exact match in numberfield
strWhere = strWhere & " AND numberfield = " & Me.txtb2
End If
If Not IsNull(Me.txtb3) Then 'range match in datefield
strWhere = strWhere & " AND datefield = " _
& Format(Me.txtb3, "\#m\/m\/d\/yyyy\#)
End If
If Not IsNull(Me.txtb4) Then 'partial match in textfield2
strWhere = strWhere & " AND textfield2 Like ""*" _
& Me.txtb4 & "*"""
End If
strWhere =Mid(strWhere, 6) 'drop entra AND

At this point you can use OpenForm (or OpenReport) method's
WhereCondition argument to open the data form (or a report)
filtered by the constucted where clause:
DoCmd.OpenForm "thedataform", , , strWhere

Alternatively, you can set the RecordSource property of any
form that is already open, even this same form:

Const strSQL As String = "SELECT textfield, numberfield, " _
& "datefield, textfield2 FROM thetable "
If Len(strWhere) > 0 Then strWhere = "WHERE " & strWhere
Forms!someform.RecordSource = strSQL & strWhere

If the form to be filtered is a subform on the search form,
then the last line would be:

Me.subform.Form.RecordSource = strSQL & strWhere
 
Both of you need to do the same thing. Look up QueryDef in the Help file.
First you need to build a criteria form where users can set the criteria to
return the records they want. Second, you need to programatically build a
querydef based on the criteria the user chooses in the query form. A
querydef is the SQL string of a stored query. Finally, you set the
recordsource of your form to the stored query. If you need help with this, I
can help you for a very reasonable fee. Email me at my email address below.
 
PC Datasheet,

Thanks for the advice and the offer of help. I'll look up Querydef and try
working it through on my own to begin with.

Cheers,

Alasdair
 
Marshall,

Thanks for the example code, I'm going to try and adapt it. The comments are
very helpful.

Cheers,

Alasdair
 
Duane,

Wow, the reporting applet is impressive. Thanks for sharing it and also for
keeping the code open so I can try and learn something.

Cheers,

Alasdair
 
Dear Mr. Schmid and Mr. Alasdair,

I encountered your posts while researching a solution to my database
problem. I have had great results from the recommended Ad-Hoc Report! I was
wondering if you had made any modifiactions to this form. Specifically, I am
curioud to know if it can be altered to search for the requested data in
multiple fields. In the bibligraphical case, if the reader knows only one
name of the author, can he type in the known name and the database searched
in both the first name and last name fields? In my case, I have an
engineering drawing that illustrates construction at 3 different pump
stations. I would like the users to be able to enter a station name, and the
database will search the fields called "StationName1", "StationName2", and
"StationName3" and report all records with the requested name in any of those
3 fields!
Any ideas? Thank you all for your help! I am particualy pleased with the
Ad-Hoc Report.mdb!
 
Back
Top