Access Runtime (Filter By Form)

[Guest]

I'm testing my Access 2003 DB in runtime mode, I'm recieving an error on a
macro that filters to a record on a form.
I''ve also tried converting it to to a VB Code Module, I still recieve an
error.

Here's the generated VB Module code it generated

Function Find_Applicant()
On Error GoTo Find_Applicant_Err

DoCmd.Echo False, "Please Stand By"
DoCmd.Hourglass True
DoCmd.RunCommand acCmdFilterByForm
DoCmd.RunCommand acCmdClearGrid


Find_Applicant_Exit:
Exit Function

Find_Applicant_Err:
MsgBox Error$
Resume Find_Applicant_Exit

End Function

Here are the error messages I receive,

It brings up a little box that says the (runcommand action was cancelled) I
click OK and it brings up another small box that says (OK) so i click Ok
and it brings up another little box that says (Resume with out error) I click
OK and it goes back to the same blank little box again, it does this forever
from (Resume with out error) to Blank Box with OK.


Thanks--Any help will be greatly appreciated.

 

[Allen Browne]

Filter By Form is not available in the runtime.

You will need to code another way to let the user filter the form.

 

[Guest]

Do you have any suggestions on how I can do this? or links to places for
similiar questions,
Thanks ,

 

[Allen Browne]

In any form, there are usually only a few fields that the user is likely to
filter by. Some of these could be a range (such as a month or quarter.) What
I do is to place unbound controls of a different color in the Form Header
section, so the user can enter any combination of controls, and then click
the command button to filter them.

The button's Click event procedure builds a string from the non-null boxes,
and applies it to the Filter of the form. The filter string looks exactly
like the WHERE clause of a query, so you can mock up a query with any old
values and then switch it to SQL View (View menu) to see an example of what
you need to create.

The example below is structured so that it is very easy to add as many
filter controls as you need, tacking the AND onto the end of each one, and
removing the trailing AND at the end. It illustrates how to use the
delimiters for text and date fields, and to ensure the date is interpreted
correctly regardless of the user's regional settings.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first

If Not IsNull(Me.cboFilterCompanyID) Then 'Number field example.
strWhere = strWhere & "([CompanyID] = " & Me.cboFilterCompanyID & ")
AND "
End If

If Not IsNull(Me.txtFilterCity) Then 'Text field example
strWhere = strWhere "([City] = """ & Me.txtFilterCity & """) AND "
End If

If Not IsNull(Me.txtFilterInvoiceDate) Then 'Date field example
strWhere = strWhere & "([InvoiceDate] = " & _
Format(Me.txtFilterInvoiceDate, strcJetDate) & ") AND "
End If

lngLen = Len(strWhere) - 5 'without trailing " AND ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
MsgBox "No criteria"
End If
End Sub


For an example of how to handle a date range, see Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

If you want something more powerful than that, search for "Query by Form".

 

[Guest]

Let's say I had the filter set on customer name and address like you stated.
And the user selected customer as there filtering option and entered there
customer name and clicked the command button filter will the filter bring
back the whole record set customer address, ph #'s, email address and all
data in that record set in all of the tables the data resides in?

Thanks--Allen

In any form, there are usually only a few fields that the user is likely to
filter by. Some of these could be a range (such as a month or quarter.) What
I do is to place unbound controls of a different color in the Form Header
section, so the user can enter any combination of controls, and then click
the command button to filter them.

The button's Click event procedure builds a string from the non-null boxes,
and applies it to the Filter of the form. The filter string looks exactly
like the WHERE clause of a query, so you can mock up a query with any old
values and then switch it to SQL View (View menu) to see an example of what
you need to create.

The example below is structured so that it is very easy to add as many
filter controls as you need, tacking the AND onto the end of each one, and
removing the trailing AND at the end. It illustrates how to use the
delimiters for text and date fields, and to ensure the date is interpreted
correctly regardless of the user's regional settings.

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If Me.Dirty Then Me.Dirty = False 'Save first

If Not IsNull(Me.cboFilterCompanyID) Then 'Number field example.
strWhere = strWhere & "([CompanyID] = " & Me.cboFilterCompanyID & ")
AND "
End If

If Not IsNull(Me.txtFilterCity) Then 'Text field example
strWhere = strWhere "([City] = """ & Me.txtFilterCity & """) AND "
End If

If Not IsNull(Me.txtFilterInvoiceDate) Then 'Date field example
strWhere = strWhere & "([InvoiceDate] = " & _
Format(Me.txtFilterInvoiceDate, strcJetDate) & ") AND "
End If

lngLen = Len(strWhere) - 5 'without trailing " AND ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
MsgBox "No criteria"
End If
End Sub


For an example of how to handle a date range, see Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

If you want something more powerful than that, search for "Query by Form".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Do you have any suggestions on how I can do this? or links to places for
similiar questions,
Thanks ,

 

[Allen Browne]

The filter is just the criteria. It does not affect which fields are
returned.