Solved

Access 2010 - Filter to 0 adds record

Posted on 2013-12-03
10
658 Views
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...)
0
Comment
Question by:GNOVAK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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()
me.combo1=""
me.combo2=""
me.filter=""
me.filteron=true
end sub
0
 
LVL 61

Expert Comment

by:mbizup
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.
0
 

Author Comment

by:GNOVAK
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.

<mbizup>
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.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:GNOVAK
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...
0
 

Author Comment

by:GNOVAK
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.
0
 
LVL 61

Expert Comment

by:mbizup
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.
0
 
LVL 37

Accepted Solution

by:
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.
Me.Requery

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 ....
0
 

Author Comment

by:GNOVAK
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...
0
 
LVL 37

Assisted Solution

by:PatHartman
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.
0
 

Author Closing Comment

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

688 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