Remove Filter keeping found record

  • Thread starter Thread starter ATSBC03
  • Start date Start date
A

ATSBC03

This is a little complicated but I am going to try and explain it in as much
detail as possible. If you need more information please let me know.

I have a form that holds all my record and that the user can edit what is
already there as well as apply new records. To help users find a specific
field I have created a command button on my form (frmOccurrenceReport) to
open Allen Browne's search form. I have modified the search form to fit my
needs and it works perfectly. However, I am now attempting to doubleclick on
the records that appear to open my main form to that record. I have been able
to get this to work as well. As an FYI, upon entered the Search form
(SearchForm) I close frmOccurrenceReport. Upon cancelling/closing SearchForm
or upon double-clicking a record it closes searchform and opens
frmOccurrenceReport. The only issue that I am having is upon opening
frmOccurrenceReport to the specific record it is showing it as filtered. I
want to be able to show all records or add new records as before. So in other
words I want to find the specific record and then remove the filter but keep
the found record in view. As of now when I remove the filter it takes me to
the first record and not the one that was found. I know this probably doesn't
make much sense but please let me know where I need to elaborate.

Thanks in advance!
 
Adapt the code below to suit your needs.

The main idea is to search the RecordsetClone of the form to find the record
you want. You then display that record in the form by setting its Bookmark
to the Bookmark of the record you found in the clone set.

There's a few things that can go wrong along the way, so the code handles
them:
a) The form can't move to the desired record if it's already open and has an
incomplete record (e.g. a required field is not yet filled in.) So, we save
any edits first (by setting its Dirty property to no.)

b) The form may be filtered so the desired record is not present. So, we
turn the filter off first.

c) The desired record might not be found for some reason. So we test the
NoMatch property after the FindFirst.

You will have to adjust the strWhere to the filter string you are currently
using.

The example code:

Dim rs As DAO.Recordset
Dim strWhere As String
strWhere = "[MyID] = 99"
With Forms!frmOccuranceReport
If .Dirty Then .Dirty = False
If .FilterOn Then .FilterOn = False
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
End With
Set rs = Nothing
 
Allen,

Thanks for the code. I have adapted it to my needs and pasted the adaptation
below because I have been unable to figure out why I am getting a Data Type
Mismatch error stopping on the following line: rs.FindFirst
strWhereResult. Do you know? I really appreciate all your help with this.
Explanations of my variables are in parenthesis below. I am sorry if it has
taken me so long to get back to this questions. Thanks in advance

Private Sub Form_Click()
(this code is called upon clicking on record from search form that you want
to display in main form). Search form closes and main form opens to all
records with one click and selected record is shown).

Dim rs As DAO.Recordset
Dim strWhereResult As String

strWhereResult = "[DCN] = " & Me.txtResultDCN

(me.txt resultDCN this is a text box on my search form and [DCN] is a text
box on my main form (it is also my primary key so always unique).

DoCmd.OpenForm "frmOccurrenceReport"

With Forms!frmOccurrenceReport
If .Dirty Then .Dirty = False
If .FilterOn Then .FilterOn = False
Set rs = .RecordsetClone
rs.FindFirst strWhereResult - I am getting the error on this
line.
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
End With
Set rs = Nothing

DoCmd.Close acForm, "SearchForm"
End Sub

Allen Browne said:
Adapt the code below to suit your needs.

The main idea is to search the RecordsetClone of the form to find the record
you want. You then display that record in the form by setting its Bookmark
to the Bookmark of the record you found in the clone set.

There's a few things that can go wrong along the way, so the code handles
them:
a) The form can't move to the desired record if it's already open and has an
incomplete record (e.g. a required field is not yet filled in.) So, we save
any edits first (by setting its Dirty property to no.)

b) The form may be filtered so the desired record is not present. So, we
turn the filter off first.

c) The desired record might not be found for some reason. So we test the
NoMatch property after the FindFirst.

You will have to adjust the strWhere to the filter string you are currently
using.

The example code:

Dim rs As DAO.Recordset
Dim strWhere As String
strWhere = "[MyID] = 99"
With Forms!frmOccuranceReport
If .Dirty Then .Dirty = False
If .FilterOn Then .FilterOn = False
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
End With
Set rs = Nothing

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

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

ATSBC03 said:
This is a little complicated but I am going to try and explain it in as
much
detail as possible. If you need more information please let me know.

I have a form that holds all my record and that the user can edit what is
already there as well as apply new records. To help users find a specific
field I have created a command button on my form (frmOccurrenceReport) to
open Allen Browne's search form. I have modified the search form to fit my
needs and it works perfectly. However, I am now attempting to doubleclick
on
the records that appear to open my main form to that record. I have been
able
to get this to work as well. As an FYI, upon entered the Search form
(SearchForm) I close frmOccurrenceReport. Upon cancelling/closing
SearchForm
or upon double-clicking a record it closes searchform and opens
frmOccurrenceReport. The only issue that I am having is upon opening
frmOccurrenceReport to the specific record it is showing it as filtered. I
want to be able to show all records or add new records as before. So in
other
words I want to find the specific record and then remove the filter but
keep
the found record in view. As of now when I remove the filter it takes me
to
the first record and not the one that was found. I know this probably
doesn't
make much sense but please let me know where I need to elaborate.

Thanks in advance!
Click to expand...
Click to expand...
 
Suggestions:

1. What data type is the CDN field? You need extra quotes if it's a Text
type field:
strWhereResult = "[DCN] = """ & Me.txtResultDCN & """"
Explanation of the quotes:
http://allenbrowne.com/casu-17.html

2. Is this data coming from an Access table? Is is it an attached table,
where the data comes from SQL Server or some non-Access database? The
declaration of a DAO recordset should be right if the data is in Access.

3. It could be a problem with library references:
http://allenbrowne.com/ser-38.html

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

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

ATSBC03 said:
Allen,

Thanks for the code. I have adapted it to my needs and pasted the
adaptation
below because I have been unable to figure out why I am getting a Data
Type
Mismatch error stopping on the following line: rs.FindFirst
strWhereResult. Do you know? I really appreciate all your help with this.
Explanations of my variables are in parenthesis below. I am sorry if it
has
taken me so long to get back to this questions. Thanks in advance

Private Sub Form_Click()
(this code is called upon clicking on record from search form that you
want
to display in main form). Search form closes and main form opens to all
records with one click and selected record is shown).

Dim rs As DAO.Recordset
Dim strWhereResult As String

strWhereResult = "[DCN] = " & Me.txtResultDCN

(me.txt resultDCN this is a text box on my search form and [DCN] is a text
box on my main form (it is also my primary key so always unique).

DoCmd.OpenForm "frmOccurrenceReport"

With Forms!frmOccurrenceReport
If .Dirty Then .Dirty = False
If .FilterOn Then .FilterOn = False
Set rs = .RecordsetClone
rs.FindFirst strWhereResult - I am getting the error on this
line.
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
End With
Set rs = Nothing

DoCmd.Close acForm, "SearchForm"
End Sub

Allen Browne said:
Adapt the code below to suit your needs.

The main idea is to search the RecordsetClone of the form to find the
record
you want. You then display that record in the form by setting its
Bookmark
to the Bookmark of the record you found in the clone set.

There's a few things that can go wrong along the way, so the code handles
them:
a) The form can't move to the desired record if it's already open and has
an
incomplete record (e.g. a required field is not yet filled in.) So, we
save
any edits first (by setting its Dirty property to no.)

b) The form may be filtered so the desired record is not present. So, we
turn the filter off first.

c) The desired record might not be found for some reason. So we test the
NoMatch property after the FindFirst.

You will have to adjust the strWhere to the filter string you are
currently
using.

The example code:

Dim rs As DAO.Recordset
Dim strWhere As String
strWhere = "[MyID] = 99"
With Forms!frmOccuranceReport
If .Dirty Then .Dirty = False
If .FilterOn Then .FilterOn = False
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
End With
Set rs = Nothing

ATSBC03 said:
This is a little complicated but I am going to try and explain it in as
much
detail as possible. If you need more information please let me know.

I have a form that holds all my record and that the user can edit what
is
already there as well as apply new records. To help users find a
specific
field I have created a command button on my form (frmOccurrenceReport)
to
open Allen Browne's search form. I have modified the search form to fit
my
needs and it works perfectly. However, I am now attempting to
doubleclick
on
the records that appear to open my main form to that record. I have
been
able
to get this to work as well. As an FYI, upon entered the Search form
(SearchForm) I close frmOccurrenceReport. Upon cancelling/closing
SearchForm
or upon double-clicking a record it closes searchform and opens
frmOccurrenceReport. The only issue that I am having is upon opening
frmOccurrenceReport to the specific record it is showing it as
filtered. I
want to be able to show all records or add new records as before. So in
other
words I want to find the specific record and then remove the filter but
keep
the found record in view. As of now when I remove the filter it takes
me
to
the first record and not the one that was found. I know this probably
doesn't
make much sense but please let me know where I need to elaborate.
Click to expand...
Click to expand...
Click to expand...
 
Thank you for all your help. I have been working on this for days and totally
forgot about the extra quotes for a text type field. After fixing the quotes
it now works perfectly. Thank you so much for all you help!

Allen Browne said:
Suggestions:

1. What data type is the CDN field? You need extra quotes if it's a Text
type field:
strWhereResult = "[DCN] = """ & Me.txtResultDCN & """"
Explanation of the quotes:
http://allenbrowne.com/casu-17.html

2. Is this data coming from an Access table? Is is it an attached table,
where the data comes from SQL Server or some non-Access database? The
declaration of a DAO recordset should be right if the data is in Access.

3. It could be a problem with library references:
http://allenbrowne.com/ser-38.html

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

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

ATSBC03 said:
Allen,

Thanks for the code. I have adapted it to my needs and pasted the
adaptation
below because I have been unable to figure out why I am getting a Data
Type
Mismatch error stopping on the following line: rs.FindFirst
strWhereResult. Do you know? I really appreciate all your help with this.
Explanations of my variables are in parenthesis below. I am sorry if it
has
taken me so long to get back to this questions. Thanks in advance

Private Sub Form_Click()
(this code is called upon clicking on record from search form that you
want
to display in main form). Search form closes and main form opens to all
records with one click and selected record is shown).

Dim rs As DAO.Recordset
Dim strWhereResult As String

strWhereResult = "[DCN] = " & Me.txtResultDCN

(me.txt resultDCN this is a text box on my search form and [DCN] is a text
box on my main form (it is also my primary key so always unique).

DoCmd.OpenForm "frmOccurrenceReport"

With Forms!frmOccurrenceReport
If .Dirty Then .Dirty = False
If .FilterOn Then .FilterOn = False
Set rs = .RecordsetClone
rs.FindFirst strWhereResult - I am getting the error on this
line.
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
End With
Set rs = Nothing

DoCmd.Close acForm, "SearchForm"
End Sub

Allen Browne said:
Adapt the code below to suit your needs.

The main idea is to search the RecordsetClone of the form to find the
record
you want. You then display that record in the form by setting its
Bookmark
to the Bookmark of the record you found in the clone set.

There's a few things that can go wrong along the way, so the code handles
them:
a) The form can't move to the desired record if it's already open and has
an
incomplete record (e.g. a required field is not yet filled in.) So, we
save
any edits first (by setting its Dirty property to no.)

b) The form may be filtered so the desired record is not present. So, we
turn the filter off first.

c) The desired record might not be found for some reason. So we test the
NoMatch property after the FindFirst.

You will have to adjust the strWhere to the filter string you are
currently
using.

The example code:

Dim rs As DAO.Recordset
Dim strWhere As String
strWhere = "[MyID] = 99"
With Forms!frmOccuranceReport
If .Dirty Then .Dirty = False
If .FilterOn Then .FilterOn = False
Set rs = .RecordsetClone
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
.Bookmark = rs.Bookmark
End If
End With
Set rs = Nothing

This is a little complicated but I am going to try and explain it in as
much
detail as possible. If you need more information please let me know.

I have a form that holds all my record and that the user can edit what
is
already there as well as apply new records. To help users find a
specific
field I have created a command button on my form (frmOccurrenceReport)
to
open Allen Browne's search form. I have modified the search form to fit
my
needs and it works perfectly. However, I am now attempting to
doubleclick
on
the records that appear to open my main form to that record. I have
been
able
to get this to work as well. As an FYI, upon entered the Search form
(SearchForm) I close frmOccurrenceReport. Upon cancelling/closing
SearchForm
or upon double-clicking a record it closes searchform and opens
frmOccurrenceReport. The only issue that I am having is upon opening
frmOccurrenceReport to the specific record it is showing it as
filtered. I
want to be able to show all records or add new records as before. So in
other
words I want to find the specific record and then remove the filter but
keep
the found record in view. As of now when I remove the filter it takes
me
to
the first record and not the one that was found. I know this probably
doesn't
make much sense but please let me know where I need to elaborate.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top