Access 2010 - Filter to 0 adds record

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...)
Who is Participating?
PatHartmanConnect With a Mentor Commented:
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 ....
Rey Obrero (Capricorn1)Commented:
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
<< 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.
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

GNOVAKAuthor Commented:
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.
GNOVAKAuthor Commented:
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...
GNOVAKAuthor Commented:
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.
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.
GNOVAKAuthor Commented:
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...
PatHartmanConnect With a Mentor Commented:
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.
GNOVAKAuthor Commented:
Thanks Pat - excellent.
its been a while and I forgot to always* use a query for a form's record source.
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.