ODBC Error 3621 closing bound form (1 Viewer)

jmwoolst

Registered User.
Local time
Today, 03:05
Joined
Aug 1, 2019
Messages
18
I have a bound access form in which users enter new records to a SQL table. The table in which it is adding records to has a few required fields. I am able to successfully enter records into the table, and use VBA to mandate that the required fields are filled out before submitting the new records. The issue is that when I try to close the form and go back to the main menu, I am getting an ODBC error:

".... Cannot insert the value NULL into column 'BisID', table 'AdjustmentsMain'; column does not allow nulls. INSERT fails (#515) ..... The statement has been terminated. (#3621)."

It appears since the form is bound that it creates a new record in the SQL table each time the blank form is brought up. I would prefer not to unbind the form, since it was bound in an effort to allow multiple users to enter new records without having duplicate or locking issues.

Is there a way I can exit the form without having the blank record attempt to be inserted in the table? Ideally there would be an option for the user to close without saving to the table, perhaps through error handling?

Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:05
Joined
Oct 29, 2018
Messages
21,357
Hi. It all depends on the user experience you want to achieve. For example, in the BeforeUpdate event of the form, you can check for any missing required fields and ask the user to provide them or cancel the record.
 

jmwoolst

Registered User.
Local time
Today, 03:05
Joined
Aug 1, 2019
Messages
18
Hi. It all depends on the user experience you want to achieve. For example, in the BeforeUpdate event of the form, you can check for any missing required fields and ask the user to provide them or cancel the record.

Would BeforeUpdate mean they would be presented with that option before any field on the form is updated, essentially when the form is opened?

The option of cancelling a record seems to be exactly what I am looking for.

Thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:05
Joined
Oct 29, 2018
Messages
21,357
Would BeforeUpdate mean they would be presented with that option before any field on the form is updated, essentially when the form is opened?

The option of cancelling a record seems to be exactly what I am looking for.

Thank you
Well, the BeforeUpdate event of the form fires before the record is saved to the table. So, no, it shouldn't prompt the user if you simply open the form. Only if you made a change to the record or create a new one.
 

jmwoolst

Registered User.
Local time
Today, 03:05
Joined
Aug 1, 2019
Messages
18
Well, the BeforeUpdate event of the form fires before the record is saved to the table. So, no, it shouldn't prompt the user if you simply open the form. Only if you made a change to the record or create a new one.

The main issue is that a record is attempting to insert when a blank form is closed, so I don't know if a "BeforeUpdate" event would get the chance to trigger since it hasn't been edited or updated. Just opening and closing the form is causing the error, so it is sometimes occurring without any changes being made to the form. Would creating the user option to save/delete as an "On Close" event be my best option?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:05
Joined
Oct 29, 2018
Messages
21,357
The main issue is that a record is attempting to insert when a blank form is closed, so I don't know if a "BeforeUpdate" event would get the chance to trigger since it hasn't been edited or updated. Just opening and closing the form is causing the error, so it is sometimes occurring without any changes being made to the form. Would creating the user option to save/delete as an "On Close" event be my best option?
Hi. A normal form shouldn't attempt to insert a record at all. Can you show us your form and any code you have behind it? Thanks.
 

jmwoolst

Registered User.
Local time
Today, 03:05
Joined
Aug 1, 2019
Messages
18
Hi. A normal form shouldn't attempt to insert a record at all. Can you show us your form and any code you have behind it? Thanks.

Unfortunately I am not able to share it as it contains company information. I was able to find a simple workaround however, I was able to unbind the form and still avoid locking/ duplication issues. Not sure what I did differently than last time I attempted to use the form without binding it but it was successful this time.

Thank you for your time and insight.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:05
Joined
Oct 29, 2018
Messages
21,357
Unfortunately I am not able to share it as it contains company information. I was able to find a simple workaround however, I was able to unbind the form and still avoid locking/ duplication issues. Not sure what I did differently than last time I attempted to use the form without binding it but it was successful this time.

Thank you for your time and insight.
Okay. Glad to hear you got it sorted out. Good luck with your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:05
Joined
Feb 19, 2002
Messages
42,970
I was able to unbind the form and still avoid locking/ duplication issues.
That is the wrong solution. theDBGuy was offering to help figure out what is wrong with the code behind the form. YOUR code is dirtying the record and it shouldn't be. Going to an unbound form isn't the solution. The solution is getting rid of the bad code.

I'm not sure how the code behind the form contains company information. Code shouldn't contain data at all. No one asked you to upload any data.
 

Users who are viewing this thread

Top Bottom