By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,365 Members | 3,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,365 IT Pros & Developers. It's quick & easy.

incorporating a search into a form

P: n/a
Is it possible to incorporate a search into a form using a text field
for the search word(s) and a command button to start the search,
returning results that don't exactly match.. For example searching for
Aluminium could return all records that have aluminium in a field. The
reason this needs to be done in a form as opposed to just using
Access' find facility is that it will be used by people that aren't
used to computers and especially access so an idiot-proof approach is
needed.
thanks
Sam
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Email me if this seems to difficult to master.

Create a table named Elements with 3 columns in it
called:Element_ID(the Primary Key-an AutoNumber), STONE(text) and
METAL(text). Populate the rows with various kinds of data such as
marble,quartz,limestone, etc and aluminum,iron,steel,etc...
Write a query called Qry_criteria with qry_Search as the source and
displaying the field:Element_ID.
Write a query using this table called qry_Search.Include fields
Element_ID, STONE and METAL.
Write a query called Qry_results with Qry Search linked to
qry_Criteria by the Element_ID.Display fields for: Element_ID,Metal
and Stone.
Write a report called rpt_Results with Qry_Results as the source.
Display text boxes for: STONE and Metal.
Create a form with a 2 text boxes on it. Go to Design View-Properties
of the text box. Write STONE in the Name....line in one text box,
Write METAL in the Name....line.

Add a command button to the form at the bottom. Call it cmdExecute
Report. Add this code to the On CLick event.

Private Sub cmd_ExecuteReport_Click()
On Error GoTo Err_cmd_ExecuteReport_Click

Dim MyDB As Database, MyQuery As QueryDef

Set MyDB = DBEngine.Workspaces(0).Databases(0)

MyDB.QueryDefs.Delete "qry_Criteria"
pwhere = 0
Set MyQuery = MyDB.CreateQueryDef("qry_Criteria")

ST1 = " SELECT [Qry_Search].[Element_ID] FROM [Qry_Search] GROUP BY
[Qry_Search].[Element_ID]"

If (Stone <> "") Then
If pwhere = 0 Then
ST2 = " HAVING (First(Qry_Search.[Stone]) Like '*" + Stone +
"*')"
Else
ST2 = " AND (First(Qry_Search.[Stone]) Like '*" + Stone +
"*')"
End If
pwhere = 1
Else
ST2 = ""
End If

If (Metal <> "") Then
If pwhere = 0 Then
ST3 = " HAVING (First(Qry_Search.[Metal]) Like '*" + Metal +
"*')"
Else
ST3 = " AND (First(Qry_Search.[Metal]) Like '*" + Metal +
"*')"
End If
pwhere = 1
Else
ST3 = ""
End If

Stall = ST1 + ST2 + ST3 + ";"
MyQuery.SQL = Stall
DoCmd.OpenReport "rpt_Results", acPreview, "", ""
DoCmd.ApplyFilter "qry_Criteria", ""

Exit_cmd_ExecuteReport_Click:
Exit Sub

Err_cmd_ExecuteReport_Click:
Resume Next

End Sub

Add a command button to the form at the bottom. Call it cmdClear. Add
this code to the On CLick event. This will empty the boxes and clear
the code for a new request.
Private Sub cmd_Clear_Click()
On Error GoTo Err_cmd_Clear_Click

Dim MyDB As Database

Set MyDB = DBEngine.Workspaces(0).Databases(0)

MyDB.QueryDefs.Delete "qry_Criteria"
Metal = ""
Stone = ""

Err_cmd_Clear_Click:
Resume Next

End Sub
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.