Access 2010 - Filter to 0 adds record

Posted on 2013-12-03
Last Modified: 2013-12-04
I have a complex form with a few subforms.
The form is based on a Dynaset - a linked Table.
It has two unbound combo boxes at the top for selection.
If the user selects something from the two boxes, data is filled and the user can add/edit/.
If the user then makes another selection, the previous data needs to be "cleared" from the form until the selection is totally completed (both CBOs filled in). In order to do this I set a filter to a primary key = 0 and me.filter true.  There is no record with a 0
This works great for clearing the detail without affecting any existing records.

The only problem is that as soon as I issue the me.filter = "<primary_key>=0", a new record is added.  The only solution I can come up with is to delete the blank record immediately or upon exit.  
This is obviously not an optimum solution.
Anyone have ideas?
(the form is complex, so the above description will have to do...)
Question by:GNOVAK
  • 5
  • 2
  • 2
  • +1
LVL 119

Expert Comment

by:Rey Obrero
ID: 39692536
It has two unbound combo boxes at the top for selection.
If the user selects something from the two boxes, data is filled and the user can add/edit/.

so you are saying that after the selection from combo boxes, the form is filled with records.

if you clear the combo boxes, there will be no records to show.. is this correct

create a command button that will clear the combo boxes

private sub cmdClear_click()
end sub
LVL 61

Expert Comment

ID: 39692597
<< me.filter = "<primary_key>=0", a new record is added.  >>

This is not normal behavior, and shouldn't happen if no data is entered when you initially set the filter like this.

Is your PK an autonumber or is it built by custom code?

Check the Data Entry property of your form... it should be set to NO.

Also, do you have code in the form's current event to autopopulate any of the fields on your form?   If so, and something like an If-Then statement to bypass autopopulating the data unless the PK is > 0.

Author Comment

ID: 39692921
The first combo box sets up the detail query for the second.
Once the second is selected the filter is set to retrieve the correct detail based on the primary key of the bound table.

The table is an Oracle linked table. In Oracle, it has what is called a TRIGGER based on a SEQUENCE number.  The trigger is fired before a record is inserted. At first I thought the access command "DoCmd.GoToRecord , , acNewRec",  would trigger the linked table, but it doesnt.
So I had to create something that runs SQL to insert garbage in the underlying Oracle table, and returns the Primary key.
I then figured that if I set the ME.FILTER=<newprimarykey> , it would then work on that record.

However, it appears that Access doesnt know that a record has been created.
so as soon as I do a Me.FILTER=   , it creates a new record.  I dont know where this is coming from. There is no other acnewrecord going on.

Author Comment

ID: 39692970
Also, if I take the Filter= out of the process, the first time I run a me.refresh, it creates a new record.
There are no current events...

Author Comment

ID: 39693067
I found a solution -
If I use the "DoCmd.GoToRecord , , acNewRec" and then populate any bound control other than the primary key, do a refresh, Oracle sees the record. I can then get my key.
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

LVL 61

Expert Comment

ID: 39693097
Oracle, Eh?  :-)  

Just for future reference, you can include multiple topic areas in your questions - which gets your question seen by Experts with a wider variety of talents.

Having some Oracle folks involved might have helped here.

Glad you found a solution... you can close the question by accepting your own answer.
LVL 34

Accepted Solution

PatHartman earned 500 total points
ID: 39693548
I found a solution -
If I use the "DoCmd.GoToRecord , , acNewRec" and then populate any bound control other than the primary key, do a refresh, Oracle sees the record. I can then get my key.
Refresh forces Access to save the current record if it is dirty.  You put a value in the primary key field (or ANY bound field) and so that "dirtied" the record.  

Many people use Requery or Refresh because they want to save a record but both have unintended consequences.  If you want to save a record, do so explicitly.  That way people reading your code KNOW your intention.

DoCmd.RunCommand acCmdSaveRecord
or some people recommend
Me.Dirty = False
I'm sure there is some error that this gets around but in 20 years I have never encountered it so I prefer the unambiguous acCmdSaveRecord since anyone who sees that statement, even a novice will know that the intention is to save the current record.  Who knows what Me.Dirty = False means.  The first time I saw it, I thought it was cancelling the save rather than forcing it.

Why do you need to see the new PK before the record is saved?  Users shouldn't see the identity columns under any condition and your app doesn't need to see the PK ahead of time.  What happens if the user fails to complete the record?  Do you end up with a partial record since you have already saved it or do you attempt to clean up and delete it?  If you really want to show the user the generated ID, then best practice is to put a "save" button on the form for the user to use to save the record without leaving it.

But, the real issue is using filters in a non-Jet/ACE environment.  Filters are not a problem when your BE is Jet/ACE because Access is very tightly integrated with Jet/ACE and is optimized to handle forms bound to tables.  But, in the case where your BE is SQL Server or Oracle, etc, you are loosing out on one of the main benefits of using a real RDBMS in the first place.  When your form is bound to a table or to a query without criteria, Access has to request ALL the rows from the server so thousands of rows might be sent to your PC and the ODBC connection needs to keep that data "live" with refreshes of updates made by other people.  It is far better to use a query that returns only the records the user wants.  So instead of filtering a huge recordset that you brought down from the server, using criteria lets the server send only the records you request.

The simplest way to do this is to change your RecordSource query (if it isn't a query already, make it one) to include criteria that references the "filter" fields on your form.

Select ... From ... Where SomeField = Forms!yourform!findSomeField

Then in the AfterUpdate event of findSomeField, requery the form.

If you have multiple search fields, it is easiest to use a button so you don't requery until the user is ready.  I also include a clear button that clears the search fields so the user doesn't have to.  Using the button gives you the opportunity to verify that at least one filter field has criteria.  In that case, you can use complex criteria that AND's all the filter fields and ignores them if they are null.  So you can have 4 search fields for example but search on 1-4 depending on which the user filled in.

Where (fld1 = Forms!yourform!fildFld1 OR Forms!yourform!fildFld1  Is Null)
AND (fld2 = Forms!yourform!fildFld2 OR Forms!yourform!fildFld2  Is Null)
AND (fld3 = Forms!yourform!fildFld3 OR Forms!yourform!fildFld3  Is Null)
AND ....

Author Comment

ID: 39693761
thanks...I'm keeping this open for a bit because I'm having a hard time "clearing" the bound form when the user chooses the top CBO - I cant just set all the fields to "" since the record is still in the detail section.
I should have thought of the query. That may solve everything rather than directly to a table.
I'm going to be trying that in the morning...
LVL 34

Assisted Solution

PatHartman earned 500 total points
ID: 39693807
You can't "clear" a bound form, you have to move to a new record to empty all the controls.  If you use the technique I suggested, when you search for something that is not found, the form will be empty so my forms always open up "empty" since the first time the query runs, the search fields are empty so nothing is found.

Author Closing Comment

ID: 39696056
Thanks Pat - excellent.
its been a while and I forgot to always* use a query for a form's record source.

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now