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...)
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...)
<< 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.
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.
ASKER
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.
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.
ASKER
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...
There are no current events...
ASKER
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.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
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...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Pat - excellent.
its been a while and I forgot to always* use a query for a form's record source.
its been a while and I forgot to always* use a query for a form's record source.
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