Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-11-2019, 12:18 PM   #1
jmwoolst
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
jmwoolst is on a distinguished road
ODBC Error 3621 closing bound form

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

jmwoolst is offline   Reply With Quote
Old 09-11-2019, 12:22 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,145
Thanks: 46
Thanked 971 Times in 952 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: ODBC Error 3621 closing bound form

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
The Following User Says Thank You to theDBguy For This Useful Post:
jmwoolst (09-11-2019)
Old 09-11-2019, 12:36 PM   #3
jmwoolst
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
jmwoolst is on a distinguished road
Re: ODBC Error 3621 closing bound form

Quote:
Originally Posted by theDBguy View Post
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

jmwoolst is offline   Reply With Quote
Old 09-11-2019, 12:41 PM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,145
Thanks: 46
Thanked 971 Times in 952 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: ODBC Error 3621 closing bound form

Quote:
Originally Posted by jmwoolst View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-11-2019, 01:01 PM   #5
jmwoolst
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
jmwoolst is on a distinguished road
Re: ODBC Error 3621 closing bound form

Quote:
Originally Posted by theDBguy View Post
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?
jmwoolst is offline   Reply With Quote
Old 09-11-2019, 01:05 PM   #6
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,145
Thanks: 46
Thanked 971 Times in 952 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: ODBC Error 3621 closing bound form

Quote:
Originally Posted by jmwoolst View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-11-2019, 01:56 PM   #7
jmwoolst
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 1
Thanked 0 Times in 0 Posts
jmwoolst is on a distinguished road
Re: ODBC Error 3621 closing bound form

Quote:
Originally Posted by theDBguy View Post
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.

jmwoolst is offline   Reply With Quote
Old 09-11-2019, 01:59 PM   #8
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,145
Thanks: 46
Thanked 971 Times in 952 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: ODBC Error 3621 closing bound form

Quote:
Originally Posted by jmwoolst View Post
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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-11-2019, 02:24 PM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,982
Thanks: 13
Thanked 1,538 Times in 1,463 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: ODBC Error 3621 closing bound form

Quote:
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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Tags
access to sql , error 3621 , insert error , odbc error

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to avoid odbc-call failed mandatory field not entered error when closing form joe789 Forms 0 04-04-2016 09:50 AM
Question Closing ODBC Connection To Database khwaja General 1 11-28-2012 09:19 PM
closing a form gives an error sven2 Modules & VBA 1 11-17-2007 11:23 AM
OLE Bound Object Error On My Form Shlepdog Forms 0 11-07-2007 11:03 AM
how to update a table bound to a data entry form without closing the form betsyr Modules & VBA 3 11-01-2001 06:09 PM




All times are GMT -8. The time now is 08:27 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World