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.AbsolutePosi
Hope someone can help. Many thanks. Colin.
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.AbsolutePosi
Hope someone can help. Many thanks. Colin.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
colinasad,
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: (https://www.experts-exchange.com/help.jsp#hi23)
JeffCoachman
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: (https://www.experts-exchange.com/help.jsp#hi23)
JeffCoachman
ASKER
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.AbsolutePosi
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.AbsolutePosi
colinasad,
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?
JeffCoachman
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?
JeffCoachman
colinasad,
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)"
Again,....
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.
;-)
JeffCoachman
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)"
Again,....
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.
;-)
JeffCoachman
boag2000,
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".
colinasad,
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?
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".
colinasad,
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?
What mode did you expect from us for A grade? -> What more did you expect from us for A grade?
ASKER
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.
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.
vadimrapp1,
ADP?
How could I have missed that!
:-O
OK, then never mind.
:-(
ADP?
How could I have missed that!
:-O
OK, then never mind.
:-(
colinasad,
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.
JeffCoachman
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.
JeffCoachman
ASKER
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.
I am now accepting the solution that acknowledged my problem, replicated it, and suggested a workaround.
#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.