Maintaining a Filter

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

Guest

I posed this question in formscoding ten days ago and received no responses.
Maybe someone here can help.

I have a continuous form that the user can use the Filter by Selection or
Filter by Form options to limit the records in view. Each line (record) also
has a command button controlled by a macro which opens a detailed form for
the specific record using a Where condition.

However, the toolbar with navigation buttons at the bottom of the screen is
filtered to the single record. I would like all of the records defined by
the user in the continuous form to be available through the navigation
buttons. Can anyone help me with this?

Lloyd
 
I'm not really clear, but I think you are wanting to open another form,
filtered to the same records as this one, and with the same record current?

If so, use the Filter of the current form in the WhereCondition of the other
form. Then to locate the same record, FindFirst in the RecordsetClone of the
other form.

This kind of thing:
Dim strWhere As String
Dim frm As Form

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

If Me.FilterOn Then 'Is the filter in use?
strWhere = Me.Filter
End If
DoCmd.OpenForm "Form2", WhereCondition:= strWhere

If Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
Else
Set frm = Forms("Form2")
With frm.RecordsetClone
strWhere = "ID = " & Me.[ID]
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found in Form2."
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If
 
I think you understand what I am trying to do, but I haven't been able to get
your coding to work. Following is my coding. When I click on the button,
the first record of the unfiltered recordset opens and an error highlights
..FindFirst (probably related to the line above). A button on
frmGrantSumProgramView is opening frmGrantSumDE. DLCDGrant# is a not numeric.

One additional question - it seems odd to define a filter and use it in the
where property, not the filter property of the form.

Thanks, Lloyd

Dim strWhere As String
Dim frm As Form

If Me.FilterOn Then 'Is the filter in use?
strWhere = Me.Filter
End If
DoCmd.OpenForm "frmGrantSumDE", , strWhere

If Me.NewRecord Then
RunCommand acCmdRecordsGoToNew
Else
Set frm = Forms("frmGrantSumDE")
With frm.RecordsetClone
strWhere = "[DLCGrant#] = '" & Me![DLCDGrant#] & "'"
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found in this Form."
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If

Allen Browne said:
I'm not really clear, but I think you are wanting to open another form,
filtered to the same records as this one, and with the same record current?

If so, use the Filter of the current form in the WhereCondition of the other
form. Then to locate the same record, FindFirst in the RecordsetClone of the
other form.

This kind of thing:
Dim strWhere As String
Dim frm As Form

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

If Me.FilterOn Then 'Is the filter in use?
strWhere = Me.Filter
End If
DoCmd.OpenForm "Form2", WhereCondition:= strWhere

If Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
Else
Set frm = Forms("Form2")
With frm.RecordsetClone
strWhere = "ID = " & Me.[ID]
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found in Form2."
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If

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

Reply to group, rather than allenbrowne at mvps dot org.
Lloyd said:
I posed this question in formscoding ten days ago and received no
responses.
Maybe someone here can help.

I have a continuous form that the user can use the Filter by Selection or
Filter by Form options to limit the records in view. Each line (record)
also
has a command button controlled by a macro which opens a detailed form for
the specific record using a Where condition.

However, the toolbar with navigation buttons at the bottom of the screen
is
filtered to the single record. I would like all of the records defined by
the user in the continuous form to be available through the navigation
buttons. Can anyone help me with this?

Lloyd
Click to expand...
Click to expand...
 
The Filter of a form is just the WHERE clause of a SQL statement. That's why
you can take it and use it in the WhereCondition of OpenForm. You need an
extra comma in this line:
DoCmd.OpenForm "frmGrantSumDE", , , strWhere
That should open the other form filtered in the same way as this form.

Now to the error.
The code assumes that:
- This is an Access mdb (not an adp), so the recordset is DAO.
- The target form does have the field named DLCGrant# in its RecordSource.

You could test the first assumption by adding this line to the top of the
proc:
Dim rs As DAO.Recordset
and then replacing:
With frm.RecordsetClone
with:
Set rs = frm.RecordsetClone
With rs

If this still causes an error, make sure you have a reference to the DAO
library under Tools | References. More on references:
http://members.iinet.net.au/~allenbrowne/ser-38.html

BTW, you are checking that the code compiles? (Compile on Debug menu).

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

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

Lloyd said:
I think you understand what I am trying to do, but I haven't been able to
get
your coding to work. Following is my coding. When I click on the button,
the first record of the unfiltered recordset opens and an error highlights
.FindFirst (probably related to the line above). A button on
frmGrantSumProgramView is opening frmGrantSumDE. DLCDGrant# is a not
numeric.

One additional question - it seems odd to define a filter and use it in
the
where property, not the filter property of the form.

Thanks, Lloyd

Dim strWhere As String
Dim frm As Form

If Me.FilterOn Then 'Is the filter in use?
strWhere = Me.Filter
End If
DoCmd.OpenForm "frmGrantSumDE", , strWhere

If Me.NewRecord Then
RunCommand acCmdRecordsGoToNew
Else
Set frm = Forms("frmGrantSumDE")
With frm.RecordsetClone
strWhere = "[DLCGrant#] = '" & Me![DLCDGrant#] & "'"
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found in this Form."
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If

Allen Browne said:
I'm not really clear, but I think you are wanting to open another form,
filtered to the same records as this one, and with the same record
current?

If so, use the Filter of the current form in the WhereCondition of the
other
form. Then to locate the same record, FindFirst in the RecordsetClone of
the
other form.

This kind of thing:
Dim strWhere As String
Dim frm As Form

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

If Me.FilterOn Then 'Is the filter in use?
strWhere = Me.Filter
End If
DoCmd.OpenForm "Form2", WhereCondition:= strWhere

If Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
Else
Set frm = Forms("Form2")
With frm.RecordsetClone
strWhere = "ID = " & Me.[ID]
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found in Form2."
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If

Lloyd said:
I posed this question in formscoding ten days ago and received no
responses.
Maybe someone here can help.

I have a continuous form that the user can use the Filter by Selection
or
Filter by Form options to limit the records in view. Each line
(record)
also
has a command button controlled by a macro which opens a detailed form
for
the specific record using a Where condition.

However, the toolbar with navigation buttons at the bottom of the
screen
is
filtered to the single record. I would like all of the records defined
by
the user in the continuous form to be available through the navigation
buttons. Can anyone help me with this?

Lloyd
Click to expand...
Click to expand...
Click to expand...
 
Thanks Allen. Adding the comma and correcting the control name fixed the
problem.

I have not been doing a regular compile. I just completed one and corrected
several errors. Can you direct me to information about when, how often I
ought to be compiling my source? There isn't much info in Help or the
materials I have.

Thanks again, Lloyd

Allen Browne said:
The Filter of a form is just the WHERE clause of a SQL statement. That's why
you can take it and use it in the WhereCondition of OpenForm. You need an
extra comma in this line:
DoCmd.OpenForm "frmGrantSumDE", , , strWhere
That should open the other form filtered in the same way as this form.

Now to the error.
The code assumes that:
- This is an Access mdb (not an adp), so the recordset is DAO.
- The target form does have the field named DLCGrant# in its RecordSource.

You could test the first assumption by adding this line to the top of the
proc:
Dim rs As DAO.Recordset
and then replacing:
With frm.RecordsetClone
with:
Set rs = frm.RecordsetClone
With rs

If this still causes an error, make sure you have a reference to the DAO
library under Tools | References. More on references:
http://members.iinet.net.au/~allenbrowne/ser-38.html

BTW, you are checking that the code compiles? (Compile on Debug menu).

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

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

Lloyd said:
I think you understand what I am trying to do, but I haven't been able to
get
your coding to work. Following is my coding. When I click on the button,
the first record of the unfiltered recordset opens and an error highlights
.FindFirst (probably related to the line above). A button on
frmGrantSumProgramView is opening frmGrantSumDE. DLCDGrant# is a not
numeric.

One additional question - it seems odd to define a filter and use it in
the
where property, not the filter property of the form.

Thanks, Lloyd

Dim strWhere As String
Dim frm As Form

If Me.FilterOn Then 'Is the filter in use?
strWhere = Me.Filter
End If
DoCmd.OpenForm "frmGrantSumDE", , strWhere

If Me.NewRecord Then
RunCommand acCmdRecordsGoToNew
Else
Set frm = Forms("frmGrantSumDE")
With frm.RecordsetClone
strWhere = "[DLCGrant#] = '" & Me![DLCDGrant#] & "'"
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found in this Form."
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If

Allen Browne said:
I'm not really clear, but I think you are wanting to open another form,
filtered to the same records as this one, and with the same record
current?

If so, use the Filter of the current form in the WhereCondition of the
other
form. Then to locate the same record, FindFirst in the RecordsetClone of
the
other form.

This kind of thing:
Dim strWhere As String
Dim frm As Form

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

If Me.FilterOn Then 'Is the filter in use?
strWhere = Me.Filter
End If
DoCmd.OpenForm "Form2", WhereCondition:= strWhere

If Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
Else
Set frm = Forms("Form2")
With frm.RecordsetClone
strWhere = "ID = " & Me.[ID]
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found in Form2."
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If

I posed this question in formscoding ten days ago and received no
responses.
Maybe someone here can help.

I have a continuous form that the user can use the Filter by Selection
or
Filter by Form options to limit the records in view. Each line
(record)
also
has a command button controlled by a macro which opens a detailed form
for
the specific record using a Where condition.

However, the toolbar with navigation buttons at the bottom of the
screen
is
filtered to the single record. I would like all of the records defined
by
the user in the continuous form to be available through the navigation
buttons. Can anyone help me with this?

Lloyd
Click to expand...
Click to expand...
Click to expand...
 
The code works fine in one area, but not in another place. When I open the
summary form, the records display and clicking a View Record button opens the
correct record with the desired filter.

However, if I apply a Filter by Selection to the summary form, the
"combined" filter is shown correctly as the filter for the form, but I get a
message box which says "Enter Parameter Value" which lists the field to which
the Filter by Selection was applied and the desired form with a blank record
opens.

Any additional thoughts?

Lloyd

Allen Browne said:
The Filter of a form is just the WHERE clause of a SQL statement. That's why
you can take it and use it in the WhereCondition of OpenForm. You need an
extra comma in this line:
DoCmd.OpenForm "frmGrantSumDE", , , strWhere
That should open the other form filtered in the same way as this form.

Now to the error.
The code assumes that:
- This is an Access mdb (not an adp), so the recordset is DAO.
- The target form does have the field named DLCGrant# in its RecordSource.

You could test the first assumption by adding this line to the top of the
proc:
Dim rs As DAO.Recordset
and then replacing:
With frm.RecordsetClone
with:
Set rs = frm.RecordsetClone
With rs

If this still causes an error, make sure you have a reference to the DAO
library under Tools | References. More on references:
http://members.iinet.net.au/~allenbrowne/ser-38.html

BTW, you are checking that the code compiles? (Compile on Debug menu).

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

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

Lloyd said:
I think you understand what I am trying to do, but I haven't been able to
get
your coding to work. Following is my coding. When I click on the button,
the first record of the unfiltered recordset opens and an error highlights
.FindFirst (probably related to the line above). A button on
frmGrantSumProgramView is opening frmGrantSumDE. DLCDGrant# is a not
numeric.

One additional question - it seems odd to define a filter and use it in
the
where property, not the filter property of the form.

Thanks, Lloyd

Dim strWhere As String
Dim frm As Form

If Me.FilterOn Then 'Is the filter in use?
strWhere = Me.Filter
End If
DoCmd.OpenForm "frmGrantSumDE", , strWhere

If Me.NewRecord Then
RunCommand acCmdRecordsGoToNew
Else
Set frm = Forms("frmGrantSumDE")
With frm.RecordsetClone
strWhere = "[DLCGrant#] = '" & Me![DLCDGrant#] & "'"
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found in this Form."
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If

Allen Browne said:
I'm not really clear, but I think you are wanting to open another form,
filtered to the same records as this one, and with the same record
current?

If so, use the Filter of the current form in the WhereCondition of the
other
form. Then to locate the same record, FindFirst in the RecordsetClone of
the
other form.

This kind of thing:
Dim strWhere As String
Dim frm As Form

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

If Me.FilterOn Then 'Is the filter in use?
strWhere = Me.Filter
End If
DoCmd.OpenForm "Form2", WhereCondition:= strWhere

If Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
Else
Set frm = Forms("Form2")
With frm.RecordsetClone
strWhere = "ID = " & Me.[ID]
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found in Form2."
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If

I posed this question in formscoding ten days ago and received no
responses.
Maybe someone here can help.

I have a continuous form that the user can use the Filter by Selection
or
Filter by Form options to limit the records in view. Each line
(record)
also
has a command button controlled by a macro which opens a detailed form
for
the specific record using a Where condition.

However, the toolbar with navigation buttons at the bottom of the
screen
is
filtered to the single record. I would like all of the records defined
by
the user in the continuous form to be available through the navigation
buttons. Can anyone help me with this?

Lloyd
Click to expand...
Click to expand...
Click to expand...
 
When Access asks for the parameter value, it means there is a name somewhere
it cannot resolve.

There may be a field in the first form that the 2nd one does not have.
Alternatively, if you have a combo in the first form, and the Filter string
contains something like:
Lookup_...
then the filter is being applied on one of the columns of the combo which
may be the issue. You may be able to add the same combo to the target form,
or you may prefer to provide a better filter interface.

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

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

Lloyd said:
The code works fine in one area, but not in another place. When I open
the
summary form, the records display and clicking a View Record button opens
the
correct record with the desired filter.

However, if I apply a Filter by Selection to the summary form, the
"combined" filter is shown correctly as the filter for the form, but I get
a
message box which says "Enter Parameter Value" which lists the field to
which
the Filter by Selection was applied and the desired form with a blank
record
opens.

Any additional thoughts?

Lloyd

Allen Browne said:
The Filter of a form is just the WHERE clause of a SQL statement. That's
why
you can take it and use it in the WhereCondition of OpenForm. You need an
extra comma in this line:
DoCmd.OpenForm "frmGrantSumDE", , , strWhere
That should open the other form filtered in the same way as this form.

Now to the error.
The code assumes that:
- This is an Access mdb (not an adp), so the recordset is DAO.
- The target form does have the field named DLCGrant# in its
RecordSource.

You could test the first assumption by adding this line to the top of the
proc:
Dim rs As DAO.Recordset
and then replacing:
With frm.RecordsetClone
with:
Set rs = frm.RecordsetClone
With rs

If this still causes an error, make sure you have a reference to the DAO
library under Tools | References. More on references:
http://members.iinet.net.au/~allenbrowne/ser-38.html

BTW, you are checking that the code compiles? (Compile on Debug menu).


Lloyd said:
I think you understand what I am trying to do, but I haven't been able
to
get
your coding to work. Following is my coding. When I click on the
button,
the first record of the unfiltered recordset opens and an error
highlights
.FindFirst (probably related to the line above). A button on
frmGrantSumProgramView is opening frmGrantSumDE. DLCDGrant# is a not
numeric.

One additional question - it seems odd to define a filter and use it in
the
where property, not the filter property of the form.

Thanks, Lloyd

Dim strWhere As String
Dim frm As Form

If Me.FilterOn Then 'Is the filter in use?
strWhere = Me.Filter
End If
DoCmd.OpenForm "frmGrantSumDE", , strWhere

If Me.NewRecord Then
RunCommand acCmdRecordsGoToNew
Else
Set frm = Forms("frmGrantSumDE")
With frm.RecordsetClone
strWhere = "[DLCGrant#] = '" & Me![DLCDGrant#] & "'"
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found in this Form."
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If

:

I'm not really clear, but I think you are wanting to open another
form,
filtered to the same records as this one, and with the same record
current?

If so, use the Filter of the current form in the WhereCondition of the
other
form. Then to locate the same record, FindFirst in the RecordsetClone
of
the
other form.

This kind of thing:
Dim strWhere As String
Dim frm As Form

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

If Me.FilterOn Then 'Is the filter in use?
strWhere = Me.Filter
End If
DoCmd.OpenForm "Form2", WhereCondition:= strWhere

If Me.NewRecord Then
RunCommand acCmdRecordsGotoNew
Else
Set frm = Forms("Form2")
With frm.RecordsetClone
strWhere = "ID = " & Me.[ID]
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found in Form2."
Else
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
End If

I posed this question in formscoding ten days ago and received no
responses.
Maybe someone here can help.

I have a continuous form that the user can use the Filter by
Selection
or
Filter by Form options to limit the records in view. Each line
(record)
also
has a command button controlled by a macro which opens a detailed
form
for
the specific record using a Where condition.

However, the toolbar with navigation buttons at the bottom of the
screen
is
filtered to the single record. I would like all of the records
defined
by
the user in the continuous form to be available through the
navigation
buttons. Can anyone help me with this?

Lloyd
Click to expand...
Click to expand...
Click to expand...
 
I'm having a similar problem: I have a form that I use to find records in a
document database via Filter by Form. I have two records which can be
launched post-filter: one that summarizes the documents matching the filter,
and another that displays scanned images of the documents themselves. The
records work perfectly when filtered against the content of a text box, but
if a combo box is used, I get a dialog box as described below.

I assume I need to add code to the records, or change my means of filtering.
I'd admittedly prefer the former... any suggestions?

Justin
 
A combo works fine for filtering, so the interface is not the problem.

Check out what the Bound Column of the combo is, and set it up so that you
can filter on that. Microsoft has created a great deal of confusion by
obfuscating what is actually stored in the table through the "Lookup
Wizard". For more info, see:
The Evils of Lookup Fields in Tables
at:
http://www.mvps.org/access/lookupfields.htm
 
Back
Top