How best to trap "no records" when filtering on an Access Form?
Posted on 2008-11-09
I am developing an Access 2007 (.adp) "project" as a front-end to tables stored in an SQL Server 2005 Express database. I have an Access Form that displays full address details of Customers, including an identifying code that is the primary key in the table.
I have allowed filtering on my Access Form and a "right-click" when the cursor is in the "identifying code" field brings up the in-built filtering menu, including the "text filters" sub-menu. This allows the operator to find records whose identifying codes begin with particular characters etc. and seems to work OK. However I have a couple of problems.
1. If the requested filter cannot be matched by any records in the underlying table, an "empty" record is displayed and automatically saved in the table. If this happens a second time, a "primary key" error message is displayed because we already have a recotd with a "blank" identifying code. How can I trap the "no records" filtering result before Access decides to fill out a new, empty record?
2. On my form I have "<Previous" and "Next>" buttons to move through the records and am able tp display my own "Already at the first record" or "Already at the last record" messages if they are clicked when the currently displayed record is either the first or last record in the underlying recordset. I use the "Me.Recordset.AbsolutePosition" property to check this). However, this does not seem to work with a "filtered" set of records. If I am on the first filtered record and try to go "<Previous" I get an Access error message telling me "You can't go to the specified record", and if I am on the last of the filtered records and try to go "Next>" I get a new, empty record as described in point 1 above. Why don't my own "elegant" messages kick in with a filtered subset of records?
Hope someone can help. Many thanks. Colin.