Access, required fields, forms & ODBC

I am working on an Access 2002 database where one of the tables has
five required fields making up the key.

There is a form that is linked to this table which is used for adding
new records.

There is a close button on this form that has the following code in
it's OnClose event:

"DoCmd.close"

When the form is opened, no data entered and then the close button is
clicked - nothing happens.

It used to work fine, until I recently upsized the database into a SQL
Server database and now it's all gone horribly wrong.

If I close the form manually by clicking on the cross, I get this
error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value
NULL into column 'Product', table
'LeasebookSQL.leasebook_user.leases'; column does not allow nulls.
INSERT fails. (#515) [Microsoft][ODBC SQL Server Driver][SQL Server]
The statement has been terminated. (#3621)

Can anyone please tell me how I can get the form to close without
trying to save the record, especially when no changes have been made
to the data on the form?

Re: Access, required fields, forms & ODBC

On Jan 28, 10:48=A0am, sdavis1970 wrote:
> I am working on an Access 2002 database where one of the tables has
> five required fields making up the key.
>
> There is a form that is linked to this table which is used for adding
> new records.
>
> There is a close button on this form that has the following code in
> it's OnClose event:
>
> "DoCmd.close"
>
> When the form is opened, no data entered and then the close button is
> clicked - nothing happens.
>
> It used to work fine, until I recently upsized the database into a SQL
> Server database and now it's all gone horribly wrong.
>
> If I close the form manually by clicking on the cross, I get this
> error:
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value
> NULL into column 'Product', table
> 'LeasebookSQL.leasebook_user.leases'; column does not allow nulls.
> INSERT fails. (#515) [Microsoft][ODBC SQL Server Driver][SQL Server]
> The statement has been terminated. (#3621)
>
> Can anyone please tell me how I can get the form to close without
> trying to save the record, especially when no changes have been made
> to the data on the form?

Having DoCmd.Close in the form's OnClose event is redundant. The form
is already closing by the time the OnClose event is occurring.
Putting this in the button's Click event makes more sense.

Something is happening to make that form 'dirty', i.e. something is
making that form think that data has changed and needs to be saved.
Perhaps one or more of the field values are being set in the form's
Load, Open, or Current events or elsewhere in code. To confirm this,
set the Record Selectors property to Yes on that form if isn't set to
that already. When you open the form, if you see a little picture of
a writing pencil instead of a small black triangle in the form's
record selector then you know the record is 'dirty', i.e., data has
changed and the form will attempt to save it upon closing.

To give your users the option of closing the form without saving any
data changes you might want to add a Cancel button with the following
code in it's OnClick event:

Me.Undo
DoCmd.Close

Or alternately you might want to simply ask this question in the close
button's Click event:

If Me.Dirty Then
If MsgBox("Save changes?", vbYesNo) =3D vbNo Then Me.Undo
End If

DoCmd.Close

You might also want to check to see if the required fields for your
key have been filled in in the form's BeforeUpdate event.

Bruce