Combo Box & Filter Error

[Guest]

I setup a combo to find records which works well. I then included a filter to
the code and when I select a line in my combo box it displays the record as
filtered but when I select another line it then displays msg:Not
Found:filtered after pressing ok it brings up the record. This then occurs
for every record I find using my combo.
My code is

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AKA] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
If Not IsNull(Me.cboMoveTo) Then
Me.FilterOn = True
Me.Filter = "[AKA] = """ & Me.cboMoveTo & """"
Else
Me.FilterOn = False
End If

End Sub

Please advise me of a solution. I think the error is in the code

Many thanks

 

[Andrew R]

Hi

Might this be because you've already filtered the results to show the
first record, then you're trying to show a second record which is not
in your (already filtered) recordset?

It sounds like this might be the case, in which case, adding this line
after your
me.dirty=false
if block might help:

if me.filteron=true then me.filteron=false

Hope this helps

Andrew

 

[Wolfgang Kais]

Hello Laurie.

I setup a combo to find records which works well. I then included
a filter to the code and when I select a line in my combo box it
displays the record as filtered but when I select another line it
then displays msg:Not Found:filtered after pressing ok it brings up
the record. This then occurs for every record I find using my combo.
My code is

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AKA] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
If Not IsNull(Me.cboMoveTo) Then
Me.FilterOn = True
Me.Filter = "[AKA] = """ & Me.cboMoveTo & """"
Else
Me.FilterOn = False
End If

End Sub

Please advise me of a solution. I think the error is in the code

There are two if-blocks in you code. The first one is looking for the
selected value in the current recordsetclone and jumps to that record
if it was found, and the second one is filtering the records to only
display that one record.
The problem:
When you have previously filtered the recordset and then try to
find another record, you probably wont't find it. I guess that the
one who wrote the code knew that, because the message that is
displayed tells you: "Can't find it, you have probably filtered!".
Afterwards, no matter if the record was found or not, the filter is
applied to the form, which usually means, that you move to another
current record (so jumping to that record before filtering makes no
sense). If the record is brought up then, the message before shows
the the record was not compliant with the previous filter.

There are two solutions: Delete one of the if blocks.

1. When you delete the first if-block (and the variable), the records
will just be filtered, and if no record was found, the user sees
a blank new record (if additions are allowed).
BTW: I'd specify the filter *before* setting FilterOn to True.
2. When you delete the second if-block (the filtering), the records
are not filtered, but the desired record is shown (if it's found).
If there was no such record, the message will be displayed.

 

[Guest]

Hi Andrew

No luck I still have the error msg

The idea is to find a single record based on combo box and show only that
record then select another record from the combo so users cant use the record
nav buttons to go to next or previous records

Hi

Might this be because you've already filtered the results to show the
first record, then you're trying to show a second record which is not
in your (already filtered) recordset?

It sounds like this might be the case, in which case, adding this line
after your
me.dirty=false
if block might help:

if me.filteron=true then me.filteron=false

Hope this helps

Andrew

I setup a combo to find records which works well. I then included a filter to
the code and when I select a line in my combo box it displays the record as
filtered but when I select another line it then displays msg:Not
Found:filtered after pressing ok it brings up the record. This then occurs
for every record I find using my combo.
My code is

Private Sub cboMoveTo_AfterUpdate()
Dim rs As DAO.Recordset

If Not IsNull(Me.cboMoveTo) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[AKA] = """ & Me.cboMoveTo & """"
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
If Not IsNull(Me.cboMoveTo) Then
Me.FilterOn = True
Me.Filter = "[AKA] = """ & Me.cboMoveTo & """"
Else
Me.FilterOn = False
End If

End Sub

Please advise me of a solution. I think the error is in the code

Many thanks