How best to trap "no records" when filtering on an Access Form?

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.
Who is Participating?
Vadim RappConnect With a Mentor Commented:
> How can I trap the "no records" filtering result before Access decides to fill out a new, empty record?

1. if you don't want the user to be able to add new records, maybe you can try to just set form's property "allow additions" to False.
2. or you can create OnCurrent event like this:
    If NewRecord Then
        Filter = ""
        FilterOn = False
        MsgBox "no match",vbExclamation
    End If

I have to say however that I tried the above code in my access 2002 and it did not work, i.e. even though FilterOn was correctly set to False, the form still showed filtered new record, although in the navigation bar it did not show "(Filtered)". This looks like bug in Access, maybe it was fixed in a later than mine version - try in your, 2007, maybe it will work.

> 2. Are you suggesting I can somehow test the result of a "DoCmd.GotoRecord, , acPrevious" or "DoCmd.GotoRecord, , acNext" command before actually issuing the comand? How do I do that?

This indeed seems to be very difficult, if at all possible. Once it's filtered, absoluteposition does not help, since it still applies to the full recordset. The only thing you have is CurrentRecord. So, half of the problem - figuring out when it's the first record and preventing going back - seems to be easy, just test if currentrecord=1. But figuring if it's the last one is much more difficult since Access does not expose how many records are filtered. What I meant previously was looking at the filter; issuing equivalent sql like this:

currentproject.connection.execute(me.recordsource & ' where ' & me.filter)

and seeing how many records are returned - then you would know if you are on the last record or not - by comparing currentrecord to that number. But for that, you would need to translate Filter value into SQL first, which is probably not trivial.

So, overall, it looks like your users will have to live with these imperfections.

Maybe it would be better idea to implement some more familiar search box and then return results basing on the entered search term. I don't know about your users, but I don't think that mine would be capable of effective using Access filtering. Plus the fact that Access loves to resave the form with the applied filter and ordering, which causes problems when the form is opened next time. I think, entering a word in the box and clicking button "search" is much more user-friendly.
Vadim RappCommented:
#1: the first that comes to mind is: since you apply the filter from your own code, issue separate sql query equivalent to the filter before applying, and apply filter only if it returns records.

#2: probably because absoluteposition is not affected by filtering. Same as in #1, you can "try" it in your code before issuing the command.

Let me know if you need more details.
colinasadAuthor Commented:
Thanks for the response, vadimrapp1, but I'm not sure I understand how to do what you suggest.

1. I don't think I am applying the filter from my own code. I'm realy just going with the "in-built" filtering that happens when a user "right-clicks" while in one of my form's fields. Access just seems to "do it".

I see the Form has "On Filter" and "On Apply Filter" event properties and the "On Apply Filter" event is triggered when the "in-built" filtering is used by the operator. Presumably it is here that I inercept the "in-built" filtering that has been requested in order to test it. How would I know what filtering has been selected from the many possible filters that are offered?  Do I do some sort of "requery" on the form?
I tried a "Me.Recordset.Requery" in the Form's "Form_ApplyFilter" procedure, but that didn't seem to help. The record numbers displayed in the forms's navigation bar are changed to reflect the filtering that has been applied; can I access these values directly myself?

2. Are you suggesting I can somehow test the result of a "DoCmd.GotoRecord, , acPrevious" or "DoCmd.GotoRecord, , acNext" command before actually issuing the comand? How do I do that?

Apologies if I am asking some fairly dim questions here. Thanks. Colin.
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Jeffrey CoachmanMIS LiasonCommented:
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."
Why are you allowing Empty reords to be saved?
Why not check for at least one record, or else, ... don't save the record?

2.     "Why don't my own "elegant" messages kick in with a filtered subset of records?"
This is really a separate question.
This can cause confusion as to what the final *answer* to this question should be.
See here: (

colinasadAuthor Commented:
Thanks, Jeff.

1. My problem is that I'm not aware that I am "allowing" empty records to be saved - Access is displaying an "empty" record when Access's "in-built" filtering has nothing to find (or goes beyond the last record in a found subset of filtered records). Even if the record is not explicitly "saved" by the operator, it appears to be automatically saved by Access if the operator makes a further attempt at filtering (which they are likely to do if their first attempt at filtering did not produce the records they expected). As a beginner with Access and VBA, I would like some pointers on ways to trap "nothing found" by the filtering process before Access displays a new, empty record. If I can also find a way of stopping Access automatically saving records, that would be good too.

2. Apologies for bundling 2 issues into a single question - I will keep them separate in future. But my second issue did "contain" my first issue. If an operator's use of the "in-built" filtering (right-clicking on a displayed field) does produce a subset of records, when the operator navigates beyond the end of last record in the filtered subset, Access again displays a new, empty record. I'm still unaware how to control the navigation through this filtered subset of records. The Form's  "Me.Recordset.AbsolutePosition" and "Me.Recordset.RecordCount" properties always seem to refer to the original "global" recordset. Can we not access the "filtered" record counters for the subset of records that are correctly displayed in the Form's own navigation bar?
Jeffrey CoachmanMIS LiasonCommented:

Thanks for the clarification.

My question is "why" would filtering the form "Create" a record?
If I create a form from a table in Access, then I filter that form, that is all that happens.
Why/How would that filtered record (Blank or otherwise) get "added" to the table?

Jeffrey CoachmanMIS LiasonCommented:

Then why did you accept our posts, if your issue is not resolved?

Accepting a post lists that post as the solution.
If the posts(s) you selected are not valid answers, then thoes answers become meaningless when someone is searching here for the same issue.
Because they will see your "Accepted solutions", then see your statement that the issue is not resolved.
(They will wonder why you designated 4 answers as solutions, when your issue is not resolved)
This is very confusing and misleading.

Questions here can remain open for up to 21 days.
And I was certainly not putting you under any pressure to post back, when you were not ready to.

The issue remains:
"(it looks like Access is doing things behind the scenes that we can "see" but cannot "work with", which is a shame)"

Access really does not do things like this on it's own.

You still have not explained to us how the simple act of "Filtering" a form, creates a new record.

Perhave there is some code involved here that you are not aware of.
We need to be sure of this before we "Blame" Access.

Vadim RappCommented:
how new record is displayed when filtering, and how Access is indeed doing things on his own is easy to see if you create a sample adp like described in the question. The question was very detailed and I had no problem to reproduce it and see that it worked exactly as described, i.e. incorrectly. There's difference between "not resolved" and "proven that it's impossible".

we have reproduced your problem by creating on our own a sample adp equivalent to yours, confirmed that the problem does exist, explained how it works, and made suggestions on workaround. Some things are impossible, some are difficult, it all has been evaluated. What mode did you expect from us for A grade?
Vadim RappCommented:
What mode did you expect from us for A grade? -> What more did you expect from us for A grade?
colinasadAuthor Commented:
I apologise and now understand that my way of closing the question might have caused some unintentional problems. I'm still relatively new to Experts Exchange and hadn't really thought through the implications of "accepting" a solution that gets added to the knowledgebase when it hasn't fully solved the problem.

I do feel an element of pressure to close a question when I receive an email from EE reminiding me of "open questions". I also feel obliged (and willing) to give points to the experts who have taken the time to try and help me. I perhaps should have elaborated on my closing comment that I had not found a "fix" and that at least I hadn't been missing something obvious and therefore still had to come up with some sort of workaround. Even though they do not always solve my immediate problem, I always learn something from the responses I receive and they might still help other users who are having similar problems.

For an "A" Grade award I guess I was hoping for the problem to be eliminated. As far as I'm aware it hasn't been. vadimrapp1 managed to replicate the problem (which I tried to describe as clearly as possible in my initial posting) and his/her not finding a solution at least re-assured me that I was not making a basic error.

I will pay a bit more attention to the closing options in future questions.
Regards. Colin.
Jeffrey CoachmanMIS LiasonCommented:

How could I have missed that!

OK, then never mind.
Jeffrey CoachmanMIS LiasonCommented:

Sorry for the confusion, as you can see I mis-interpreted the question and vadimrapp1 seems to have had a firm grasp on the situation.

As far as closing and Accepting posts is concerned, you have 21 days to resolve a question.
There are now many way to do this.

If you have any doubts at all, please click the "Request Attention" button in your original question and ask for help.

colinasadAuthor Commented:
I'm tidying up loose ends here and this question has been open for some time.
I am now accepting the solution that acknowledged my problem, replicated it, and suggested a workaround.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.