Error in filter on form (stuck on first record)

[Tsuby]

Hi,

I need help in filtering data on a form, because it is behaving in a strange
way and it really puzzles me.
The database was originally written for access 2000, but I've decided to
update to 2003 now.
Everything is working fine, up to the filter.

I have an unbound textbox (Filtro) on the form and I wish to filter all
records on the form according to the text I input. If there is no match,
then I want a message to be displayed and to stay on the original record.
This is my code:

---
Private Sub Filtro_AfterUpdate()
Dim rs As DAO.Recordset
Dim strWhere As String
strWhere = "[Title] Like " & Chr(34) & "*" & Me![Filtro] & "*" & Chr(34)
If Me.Dirty Then Me.Dirty = False
If Me.FilterOn Then Me.FilterOn = False
Set rs = Me.RecordsetClone
rs.Filter = strWhere
If rs.NoMatch Then
MsgBox "Not found"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End Sub
---

Of course... it does not work.

I had to change the (working) filter of access 2000 because it would lock up
the form if I launched the filter from the first record and there was no
match. I just can't make it work.
Can you help me find the mistake, please?
Thanks

Erika

 

[Steve Sanford]

You can't the filter property on recordsets. The Filter property applies to
Forms and Reports.

Try this:

'---------------------------------
Private Sub Filtro_AfterUpdate()

Dim rs As Recordset

'save record
If Me.Dirty Then
Me.Dirty = False
End If

'all records
If Me.FilterOn Then
Me.FilterOn = False
End If

Set rs = Me.RecordsetClone
rst.FindFirst "[Title] Like " & Chr(34) & "*" & Me![Filtro] & "*" &
Chr(34)
If rs.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rs.Close
Set rs = Nothing
End Sub
'---------------------------------

HTH

 

[Tsuby via AccessMonster.com]

You can't the filter property on recordsets. The Filter property applies to
Forms and Reports.

Try this: [...]
rst.FindFirst "[Title] Like " & Chr(34) & "*" & Me![Filtro] & "*" &
Chr(34)

[...]

Hello, thanks for the fast reply.
I tried changing the filter like that and it works, but the problem is that
it only finds 1 record, whereas I need to find ALL records that match the
criteria.
The code I was using under A2k was like this and it worked flawlessly

----
Private Sub Filtro_AfterUpdate()
Dim iSaveCode As Integer
Dim RecordCorrente As Variant

iSaveCode = Me!Contatore
Me.FilterOn = False 'annulla eventuali filtri precedenti
RecordCorrente = Me.RecordsetClone.Bookmark
Me.Filter = "[Title] Like " & Chr(34) & "*" & Me![Filtro] & "*" & Chr(34)
Me.FilterOn = True
If Me.RecordsetClone.EOF Then 'se nessun record corrisponde
MsgBox "Non ci sono risultati utili"
DoCmd.RunCommand acCmdUndo
DoCmd.ShowAllRecords
Me.Filter = ""
Me.Filter = vbNullString
With Me.RecordsetClone
.FindFirst "Contatore=" & iSaveCode
Me.Bookmark = .Bookmark
End With
End If
Exit_Filtro_AfterUpdate:
Exit Sub
End Sub
---

I kept the same code under A2003, but while it works flawlessly 90% of the
time, I get a strange error if the If Me.RecordsetClone.EOF happens when I'm
trying to filter from the first record: the code works, I get the msgbox and
the form is set on the first record again, but I'm just stuck there and I
cannot go to the next record or do anything else, I'm stuck there until I
close the form.
If I do that from the second record onwards, then it's OK. The problem is
that you usually start filtering the record when you've just opened the form,
so I'd like to solve the problem.

I am really wondering if I should simply stay with A2k and run the database
through runtime.

Thanks again for the help!

Erika

 

[Steve Sanford]

Will this work for you?

'-------------------------------
Private Sub Filtro_AfterUpdate()
Dim iSaveCode As Integer
Dim RecordCorrente As Variant

Me.FilterOn = False 'annulla eventuali filtri precedenti

'save bookmark for current record
RecordCorrente = Me.Bookmark
Me.Filter = "[last_name] Like " & Chr(34) & "*" & Me![Filtro] & "*" &
Chr(34)
Me.FilterOn = True
Me.RC = Me.Recordset.RecordCount
If Me.Recordset.RecordCount = 0 Then
' If Me.Recordset.EOF Then 'se nessun record corrisponde
MsgBox "Non ci sono risultati utili"

'remove filter
Me.FilterOn = False
Me.Filter = vbNullString

DoCmd.ShowAllRecords

'return to selected record
Me.Bookmark = RecordCorrente

End If

Exit_Filtro_AfterUpdate:
Exit Sub
End Sub
'-------------------------------


HTH

 

[Tsuby via AccessMonster.com]

Will this work for you?

I got a compiling error here
Me.RC = Me.Recordset.RecordCount

What is RC? RecordCorrente?
If I replace RC with RecordCorrente the data is correctly filtered, but I get
an error on

If Me.Recordset.RecordCount = 0 Then

Because instead on canceling the filter and do nothing (or get back to the
previous record anyway) I get a new blank record.


Thanks again for helping me!

Erika

 

[Steve Sanford]

Sorry, I didn't get all of the modifications I made during testing removed.

Replace the sub with this sub and let me know:

'-------------------------------
Private Sub Filtro_AfterUpdate()
Dim RecordCorrente As Variant

Me.FilterOn = False 'annulla eventuali filtri precedenti

'save bookmark for current record
RecordCorrente = Me.Bookmark

'create filter string
Me.Filter = "[Title] Like " & Chr(34) & "*" & Me![Filtro] & "*" & Chr(34)
Me.FilterOn = True

' after setting the FilterOn property to true,
'the recordcount will be 0
'if there were no records that matched the filter (Me.Filter)

If Me.Recordset.RecordCount = 0 Then
'se nessun record corrisponde
MsgBox "Non ci sono risultati utili"

'remove filter
Me.FilterOn = False
Me.Filter = vbNullString

DoCmd.ShowAllRecords

'return to selected record
Me.Bookmark = RecordCorrente

End If

End Sub
'-------------------------------


HTH

 

[Tsuby via AccessMonster.com]

Sorry, I didn't get all of the modifications I made during testing removed.

Replace the sub with this sub and let me know:

'-------------------------------
Private Sub Filtro_AfterUpdate()
Dim RecordCorrente As Variant

Me.FilterOn = False 'annulla eventuali filtri precedenti

'save bookmark for current record
RecordCorrente = Me.Bookmark

'create filter string
Me.Filter = "[Title] Like " & Chr(34) & "*" & Me![Filtro] & "*" & Chr(34)
Me.FilterOn = True

' after setting the FilterOn property to true,
'the recordcount will be 0
'if there were no records that matched the filter (Me.Filter)

If Me.Recordset.RecordCount = 0 Then
'se nessun record corrisponde
MsgBox "Non ci sono risultati utili"

'remove filter
Me.FilterOn = False
Me.Filter = vbNullString

DoCmd.ShowAllRecords

'return to selected record
Me.Bookmark = RecordCorrente

End If

End Sub
'-------------------------------

Hi!
Thanks so much for your reply. I am sorry to tell you that it does not really
works. The filtering part is OK, but

If Me.Recordset.RecordCount = 0 Then

is not working.
Since the behaviour is always the same, no matter how I change the code... I
am afraid that it depends on something else in the form or anywhere in the
database.
It's either a 2001 run-time error or the displayed record is a new blank
record.
This happen with any kind of filter which is not done by means of a combobox.
So the mistake must be somewhere else.

Thanks again!

Erika
I will try a couple workarounds and let you know asap!
Thanks again for your help!

 

[Linq Adams via AccessMonster.com]

Am I correct in assuming, from reading your post, that this code worked in
v2000? If som when apps run fine on one machine and not on another or run
fine in one version and not when run under a newer version, the first thing
you have to think about are missing references.

In order for your code to work it needs to have the reference library

Microsoft DAO 3.6 Object Library

selected. Is it?

 

[Tsuby via AccessMonster.com]

In order for your code to work it needs to have the reference library
Microsoft DAO 3.6 Object Library
selected. Is it?

Yes, the reference to the DAO 3.6 library is there. I checked again just to
be sure, but there's nothing wrong with the references.

I actually managed to get a filter to work on this form by filtering from
another form and using a dcount statement with criteria and checking that the
string I'm searching for is in the table *before* the form is open (this
happened as of 10 minutes ago ).
So there must be something else. The problem is that I am not very good with
vba and I can't find the point in the code which causes the strange behaviour
on the form.

What I can say is that In the form there are required fields, and once you
add a new record you cannot close the form without filling them in. So I know
where the problem is when I want to delete the new record.

The problem is that I cannot find *what* brings the new record up. If there
are no records satisfying the filter it should just stop running the code,
instead of adding a new one.

I am thinking that maybe I should turn the clause the other way round, that
is, veryfying the criteria BEFORE the filtering, with Dcount or Dlookup.
I've been trying that for a couple of hours now, but I keep getting a query
error (missing operator or something like that - sorry that I cannot give you
the exact message, but I my access is in Italian). I think I am setting the
condition in the wrong way

The code looks like this:

----
stLinkCriteria = "[Title] Like " & Chr(34) & "*" & Me![Filtro] & "*" & Chr(34)


If Dlookup("Title", "Tbl_Audio_PrincipaleAlbum", stLinkCriteria) = 0 Then
cancel = True
Else
Me.filter = "[Title] Like " & Chr(34) & "*" & Me![Filtro] & "*" & Chr(34)
Me.filteron = True
----

How should I change the stLinkCriteria ?

Thanks in advance for the help!

Erika

 

[Tsuby via AccessMonster.com]

I just wanted to let you know that I solved the problem by adjusting the
StLinkCriteria + Dcount clause:
-------------
Private Sub Filtro_AfterUpdate()
Dim stLinkCriteria As String
stLinkCriteria = "[Title] LIKE " & Chr$(34) & "*" & Me![Filtro] & "*" & Chr$
(34)
If DCount("Title", "Tbl_Audio_PrincipaleAlbum", stLinkCriteria) = 0 Then
MsgBox "pippo"
Exit Sub
Else
Me.Filter = "[Title] Like " & Chr(34) & "*" & Me![Filtro] & "*" & Chr(34)
Me.FilterOn = True
End If
Exit_Cerca_Codice_AfterUpdate:
Exit Sub
End Sub
--------------

Thanks again for your help!

Best Regards

Erika