Error when viewing the form in design view or closing the form

[pwalter83]

Hi,



I am created a form to add row of data to a table (Access 2007). The form has been completed and tested and seems to be working perfectly in terms of adding row of data to a table and updating also works fine.

However, the problem I am facing is that when I try to edit the form (design view) or close the form, it gives an error:

------------------------------------------
ODBC --call failed

Cannot Insert the value NULL into the column. Column does not allow nulls. INSERT fails. The statement has been terminated.

------------------------------------------

When this error starts to appear, I am unable to view the form in design view or save it. But I am able to make entries to the table.

I am using this VBA code for the 'Add Record' button:
----------------------------------------------------------
Private Sub Add_Record_Click()
Dim strSQL As String

strSQL = "INSERT INTO dbo_VESSEL (VESSEL_NAME, VESSEL_CD,VOYAGE_NUM, LINE_CD, PORT_CD,DEPART_ACTUAL_DT,DIVISION_CD ) VALUES ('" & Me!VESSEL_NAME & "', '" & Me!VESSEL_CD & "','" & Me!VOYAGE_NUM & "', '" & Me!Text41 & "', '" & Me!PORT_CD & "', '" & Me!DEPART_ACTUAL_DT & "', '" & Me!Text45 & "');"
DoCmd.RunSQL strSQL
End Sub
------------------------------------------------------------

Does anyone know why this error appears while closing or editing the form ?

Thanks !
Paul

[hertfordkc]

Does the error occur if the form is selected and opened in design view, edited, and closed? Does it only occur if the form is opened and then switched to design view?

[pwalter83]

Does the error occur if the form is selected and opened in design view, edited, and closed? Does it only occur if the form is opened and then switched to design view?

It happens when I open the form and then test it and then try switching it to design view. At that point, it gives the error and when I click ok, it only allows me to test the form in 'Form View' but I cannot open it in 'Design View' and cannot close it without getting that error.

I have attached the screenshot of the error.

Thanks !

[hertfordkc]

I'm guessing that whether you switch to design view or close the form, your add record sub is being executed, perhaps as part of the on close event. Perhaps you could stop the error by putting an 'IF' before executing the SQL and testing the text fields for not null values.

[pwalter83]

I'm guessing that whether you switch to design view or close the form, your add record sub is being executed, perhaps as part of the on close event. Perhaps you could stop the error by putting an 'IF' before executing the SQL and testing the text fields for not null values.

Is it possible that you could give me some idea on how to do this ?

Thanks !

[hertfordkc]

Hi,

I am created a form to add row of data to a table (Access 2007). The form has been completed and tested and seems to be working perfectly in terms of adding row of data to a table and updating also works fine.

However, the problem I am facing is that when I try to edit the form (design view) or close the form, it gives an error:

------------------------------------------
ODBC --call failed

Cannot Insert the value NULL into the column. Column does not allow nulls. INSERT fails. The statement has been terminated.

------------------------------------------

When this error starts to appear, I am unable to view the form in design view or save it. But I am able to make entries to the table.

I am using this VBA code for the 'Add Record' button:
----------------------------------------------------------
Private Sub Add_Record_Click()
Dim strSQL As String
' ******************************************
If NOT isnull(me!Text41) AND Not isnull(me!Text45) then
' ****************************************
strSQL = "INSERT INTO dbo_VESSEL (VESSEL_NAME, VESSEL_CD,VOYAGE_NUM, LINE_CD, PORT_CD,DEPART_ACTUAL_DT,DIVISION_CD ) VALUES ('" & Me!VESSEL_NAME & "', '" & Me!VESSEL_CD & "','" & Me!VOYAGE_NUM & "', '" & Me!Text41 & "', '" & Me!PORT_CD & "', '" & Me!DEPART_ACTUAL_DT & "', '" & Me!Text45 & "');"
DoCmd.RunSQL strSQL
' *******************************
end If
' *******************************
End Sub
------------------------------------------------------------

Does anyone know why this error appears while closing or editing the form ?

Thanks !
Paul

I'm assuming that one or the other of your text controls is empty.
However, without knowing the details of your form, it could be any of the fields which is causing the problem. Can you identify the another field as likely causing the problem? (Remember null<>empty<>blank) In other words, an empty field wouldn't cause the error which you are seeing.

Another way to handle the problem might be with ERROR trapping. I haven't done that in a while, and since I can't test on my Access box, all I can offer is to Google 'Access Error Handling'.