Jon,
I would suggest the following:
1) Create a search form containing (unbound) text boxes
that directly correspond to the fields that you wish to
search. For instance, if you want to search on a
combination of 4 different fields, then your search form
should contain 4 different text boxes where the user would
enter the search criteria.
2) Enter any descriptive text in the label for each text
box. This way, the users know what type of information to
enter.
3) Place two command buttons on the form.
a) Label the 1st button as "Display" (or "Search")
and assign the name as "cmdDisplay".
b) Label the 2nd button as "Close" and assign the
name as "cmdClose".
Put the below code in the VBA module of your search form.
=======================================================
Option Compare Database
Option Explicit
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close acForm, Me.Name
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
If Err.Number <> 2501 Then
MsgBox "Module: " & vbTab & vbTab & Me.Name &
vbCrLf _
& "Procedure #: " & vbTab & "1" & vbCrLf _
& "Error #: " & vbTab & vbTab & Err.Number &
vbCrLf _
& "Description: " & vbTab & Err.Description
Else
Resume Exit_cmdClose_Click
End If
End Sub
Private Sub cmdDisplay_Click()
On Error GoTo Err_cmdDisplay_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim ctl As Control
stDocName = "YourMaintenanceForm" 'This is the main
form you use to view/maintain the data.
stLinkCriteria = ""
'Loop through each text box control on the form to get
its value.
'IMPORTANT: This sub requires that each control used
in the search functionality
'be assigned the SAME NAME(!!!) as the field in the
table.
'This is because the below BuildCriteria function
constructs the string using the control
'name as part of the string that that will be used in
the where clause (stLinkCriteria)
'in the Open Form statement.
For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acTextBox
.SetFocus
'Field must have focus to obtain .Text
value.
If stLinkCriteria = "" And Not IsNull
(ctl.Value) Then
stLinkCriteria = BuildCriteria
(.Name, vbString, .Text)
ElseIf Not IsNull(ctl.Value) Then
stLinkCriteria = stLinkCriteria
& " And " & BuildCriteria(.Name,
vbString, .Text)
End If
End Select
End With
Next ctl
DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria
DoCmd.Close acForm, Me.Name
Exit_cmdDisplay_Click:
Exit Sub
Err_cmdDisplay_Click:
If Err.Number <> 2501 Then
MsgBox "Module: " & vbTab & vbTab & Me.Name &
vbCrLf _
& "Procedure #: " & vbTab & "2" & vbCrLf _
& "Error #: " & vbTab & vbTab & Err.Number &
vbCrLf _
& "Description: " & vbTab & Err.Description
Else
Resume Exit_cmdDisplay_Click
End If
End Sub
=======================================================
-----Original Message-----
hi,
i am making a database in which i want to provide a search feature. the main
form which has a record source of the search query, is accessed through a
search form. the search form contains a textbox and combo- box. the combo-box
contains a list of fields. i wish for the user to be able to select the
search field from the combo and enter the search string into the text. i
thought it might be able to be done through a query such as:
SELECT *
FROM cdd
WHERE Forms![select-show]!cmb_search Like "*" &
Forms![select-show]!txt_search & "*";
with no results being shown, when the Forms![select-show]! cmb_search is
replaced with a field such as 'title' it works fine
can anybody tell me if this is able to be done through a query and if not
any suggestions on how to do it otherwise, any help appreciated.
Thanks
.
