469,898 Members | 1,528 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

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?

Jan 28 '08 #1
1 2755
On Jan 28, 10:48*am, sdavis1970 <simondavis1...@gmail.comwrote:
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) = 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
Jan 28 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Jon Ole Hedne | last post: by
3 posts views Thread by Gheaci Maschl | last post: by
49 posts views Thread by Yannick Turgeon | last post: by
2 posts views Thread by egoldthwait | last post: by
1 post views Thread by gordon.dtr | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.