Karen-
The best way to do this sort of "filter by form" is to build a form with unbound controls (and no Record Source) and then write code to evaluate what the user typed and build a filter. You will still need to open a form, but if you make the default view of the form Datasheet, it'll look just like the results of a query - or what you call "worksheet" view.
Do you want the user to specify that a search value entered for Activity should also look in OrgName, or do you want to do that by default? Do you want the user to specify AND or OR?
Let's say you have an unbound form with controls called txtActivity, txtOrg, txtAddress, and txtEventDate. Put a command button on the form and put something like this in the Click event:
Private Sub cmdSearch_Click()
Dim strFilter As String
' See if Activity entered
If Len(Me.txtActivity & "") > 0 Then
' Build a filter on Activity Name
strFilter = "([Activity Name] LIKE '*" & Me.txtActivity & "*')"
End If
' See if Org entered
If Len(Me.txtOrg & "") > 0 Then
' Add or build a filter on OrgName
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "([OrgName] LIKE '*" & Me.txtOrg & "*')"
End If
' Check Address
If Len(Me.txtAddress & "") > 0 Then
' Add or build a filter on Address
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "([Address] LIKE '*" & Me.txtAddress & "*')"
End If
' Check Event Date
If Len(Me.txtEventDate & "") > 0 Then
' Add or build a filter on Address
If Len(strFilter) > 0 Then strFilter = strFilter & " AND "
strFilter = strFilter & "([EventDate] = #" & Me.txtEventDate & "#)"
End If
' If no filter built,
If Len(Me.strFilter) = 0 Then
MsgBox "You must enter at least one filter value."
Exit Sub
End If
' Open the Datasheet form
DoCmd.OpenForm "Datasheet Form", View:=acFormDS, _
WhereCondition:=strFilter
' Close me
DoCmd.Close acForm, Me.Name
End Sub
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
I am trying to create a search form. I have 4 criteria that the user can search on: Activity Name, Org Name, Address & Event Date. I would like to have Access search by any or all of those items, based on what the user types in. For example, the user types in Ward in the OrgName and 4251 in the Address. I would like the results to include any records that Ward and 4251 in the record as well as any that have only Ward or 4251. To throw another request at you. Can I also program the search to search multiple fields for the same keyword? For example, the user types in Ward in the Activity Name search field and records with Ward in the Activity Name or Org Name shows up as the results?
I know that I can do a search by form. If I do that though, the results show up in the form view. I would like the results to display in the worksheet view.
I have no idea even where to begin.
Thanks.
Karen
Tidak ada komentar:
Posting Komentar