In my project I have a main form which shows all records. You can add
records by clicking a command button. When the button is pressed a new form
opens which has a tab control with 6-8 tabs, with each tab having a different
subform. Each subform has an associated table. Each table is related to one
main table on a one-one relationship. Referential integrity is checked with
cascading delete checked.
The only way to close the form is by clicking the "X". In the form Unload
event I have code to ask if you want to save the record.

My problem is, once I click the "X" I get a msg box stating that my primary
key field can't contain a null value and to enter a value. I click ok then
another msg box comes up stating that my project encountered a problem saving
the record and if I close I will lose my changes. I click yes to close the
database object anyway. Then I finally get my message box asking to save the
changes.

My question is how can I exit the form without saving the record and
avoiding the record errors.

Thanks in advance for your help

On Thu, 14 Jan 2010 09:57:01 -0800, Which1 <(E-Mail Removed)>
wrote:

>The only way to close the form is by clicking the "X". In the form Unload
>event I have code to ask if you want to save the record.

Too late, unfortunately!

Access saves a mainform record the moment that you set focus to any subform on
that mainform (it must, in order to provide a parent record so that
referential integrity can be enforced). Likewise, it saves the subform record
to disk the moment you set focus back to the mainform, or to any other
subform.

If you're trying to let the users enter data into multiple tables, perhaps
multiple records into each subform, and THEN accept or reject the changes en
masse, you'll need some more complex logic, e.g. basing the form on a set of
"scratchpad" tables, and having your "accept" button or Unload event run an
Append query to copy the data from the scratchpad tables to the real tables,
and then delete the scratchpad data. VERY complex! In practice it's usually
better to train the users to only enter data when they want to enter data.

--

John W. Vinson [MVP]

On Thu, 14 Jan 2010 09:57:01 -0800, Which1
<(E-Mail Removed)> wrote:

>In my project I have a main form which shows all records. You can add
>records by clicking a command button. When the button is pressed a new form
>opens which has a tab control with 6-8 tabs, with each tab having a different
>subform. Each subform has an associated table. Each table is related to one
>main table on a one-one relationship. Referential integrity is checked with
>cascading delete checked.
>The only way to close the form is by clicking the "X". In the form Unload
>event I have code to ask if you want to save the record.
>
>My problem is, once I click the "X" I get a msg box stating that my primary
>key field can't contain a null value and to enter a value. I click ok then
>another msg box comes up stating that my project encountered a problem saving
>the record and if I close I will lose my changes. I click yes to close the
>database object anyway. Then I finally get my message box asking to save the
>changes.
>
>My question is how can I exit the form without saving the record and
>avoiding the record errors.
>
>Thanks in advance for your help

Other than it sounding like you have some basic design flaws in the
database, you can base the form(s) on unbound fields and put a button
on the form that calls code to write all the data to each of the
tables all at once. If you close the form without clicking the button,
nothing is saved.

Think that might work for you?

RD

Is there any way I can trap the error and delete the record in the back
ground then close the form?

"RD" wrote:

> On Thu, 14 Jan 2010 09:57:01 -0800, Which1
> <(E-Mail Removed)> wrote:
>
> >In my project I have a main form which shows all records. You can add
> >records by clicking a command button. When the button is pressed a new form
> >opens which has a tab control with 6-8 tabs, with each tab having a different
> >subform. Each subform has an associated table. Each table is related to one
> >main table on a one-one relationship. Referential integrity is checked with
> >cascading delete checked.
> >The only way to close the form is by clicking the "X". In the form Unload
> >event I have code to ask if you want to save the record.
> >
> >My problem is, once I click the "X" I get a msg box stating that my primary
> >key field can't contain a null value and to enter a value. I click ok then
> >another msg box comes up stating that my project encountered a problem saving
> >the record and if I close I will lose my changes. I click yes to close the
> >database object anyway. Then I finally get my message box asking to save the
> >changes.
> >
> >My question is how can I exit the form without saving the record and
> >avoiding the record errors.
> >
> >Thanks in advance for your help
>
> Other than it sounding like you have some basic design flaws in the
> database, you can base the form(s) on unbound fields and put a button
> on the form that calls code to write all the data to each of the
> tables all at once. If you close the form without clicking the button,
> nothing is saved.
>
> Think that might work for you?
>
> RD
>
> .
>

On Fri, 15 Jan 2010 13:48:01 -0800, Which1
<(E-Mail Removed)> wrote:

>"RD" wrote:
>
>> On Thu, 14 Jan 2010 09:57:01 -0800, Which1
>> <(E-Mail Removed)> wrote:
>>
>> >In my project I have a main form which shows all records. You can add
>> >records by clicking a command button. When the button is pressed a new form
>> >opens which has a tab control with 6-8 tabs, with each tab having a different
>> >subform. Each subform has an associated table. Each table is related to one
>> >main table on a one-one relationship. Referential integrity is checked with
>> >cascading delete checked.
>> >The only way to close the form is by clicking the "X". In the form Unload
>> >event I have code to ask if you want to save the record.
>> >
>> >My problem is, once I click the "X" I get a msg box stating that my primary
>> >key field can't contain a null value and to enter a value. I click ok then
>> >another msg box comes up stating that my project encountered a problem saving
>> >the record and if I close I will lose my changes. I click yes to close the
>> >database object anyway. Then I finally get my message box asking to save the
>> >changes.
>> >
>> >My question is how can I exit the form without saving the record and
>> >avoiding the record errors.
>> >
>> >Thanks in advance for your help
>>
>> Other than it sounding like you have some basic design flaws in the
>> database, you can base the form(s) on unbound fields and put a button
>> on the form that calls code to write all the data to each of the
>> tables all at once. If you close the form without clicking the button,
>> nothing is saved.
>>
>> Think that might work for you?
>>
>> RD
>>
>> .
>Is there any way I can trap the error and delete the record in the back
>ground then close the form?
>
Sorry about the delayed answer. My Usenet provider has been MIA for a
few days.

The idea is to validate all the input before you even write the
record. IOW, don't write the record if it contains an error. No record
= no reason to "delete the record in the background".

Once all the data has been input and validated, your code controls the
order the records are written, starting with the parent table. You
must commit the record in the parent table before writing any records
in child tables otherwise you get that null value in the key field
error.

Is this making sense? Sorry. Been a long day/week.

RD