Solved Error about assigning Null value when clicking away from deleted form field

[isaksp00]

I have a form created to allow editing of a record in a table of people. The db is in SQL Server. Most of the columns are Not Nullable, e.g., Last Name, First Name.

This form (mostly) works when editing a record, that is, if I change a value such as name and save it. I am adding event code to detect whether any required fields are empty or the empty string, copying code I have working in the companion form that is used to add a new person.

The reason it is only "mostly" working is that if I click on a required field and delete all characters then click on another field this pops up:

I then added VBA to several events of the field in which the text was deleted, that just had a few debug.print commands so I could use a breakpoint. I tried the On Exit and On Lost Focus events. The error message box pops up before these events are triggered.

Advice from searches I have done is that Access tries to save the record at the time I click away from that field, which seems wrong to me (why try to save until the save button?). I can't make the table column nulable as it is required. I also can't figure out if there's some event I can "catch" with VBA code to warn the user without this unfriendly dialog. Basically, I am confused by the event model used by Access and exactly what it is doing to cause this, and how it can be improved.

Thanks!

 

[theDBguy]

The reason it is only "mostly" working is that if I click on a required field and delete all characters then click on another field this pops up:

This error appears to be a result of your VBA code. You'll have to show us that part. You can modify it to either check for nulls or auto change nulls to something else. For example, you can use the Nz() function for that.

The error message box pops up before these events are triggered.

Did you try the Before Update event?

Advice from searches I have done is that Access tries to save the record at the time I click away from that field, which seems wrong to me (why try to save until the save button?).

That can't be right. Access should only try to save the record when you navigate to a different record or close the form.

 

[Pat Hartman]

Advice from searches I have done is that Access tries to save the record at the time I click away from that field,

100% wrong. Do not believe anything these people say. There are multiple situations where Access will attempt to save a record but this is not one of them. In general, they revolve around changing to a different/new record or closing a dirty form or the database.

How did you "delete" a field value? The only correct way is to use the escape key. If you use the space key or the backspace, you leave a ZLS in the field.

 

[The_Doc_Man]

Clicking away from a field differs from tabbing away from a field in that clicking on a control is essentially a random-access operation. Tabbing out of the last field on a form CAN result in an implied SAVE RECORD operation based on FILE >> SETTINGS >> CURRENT DATABASE options related to TAB character behavior. A random click in and of itself cannot do that without something else going on.

A SAVE RECORD can update a record based on a random click IF AND ONLY IF there is code behind the field such as the LOST FOCUS event, OR if in clicking away from the control you click TO a command button that does a SAVE RECORD.

Therefore, something about your description of actions is incomplete and thus leading us to wonder what you aren't telling us.

 

[isaksp00]

Thanks for all this so quickly!

I just tried the Before Update event on the field that has been deleted. It fired but after the error dialog in my image opened.

I wasn't sure which VB code to attach as none of it seemed to be invoked before the error appeared, but it is below. This is all the code from the Form's vba module.

I deleted the control's value in the Access UI by clicking in it and backspacing, or sometimes by highlighting it all and hitting Delete. Then I clicked on the next field down in the form, causing the error. I have not yet tried tabbing out of the field. However, if this app is deployed, any user might use backspace or the Del key, so I have to figure out what is happening. If anyone needs any other info about the sequence let me know.

Option Compare Database

Private Sub Cancel_Btn_Click()

On Error GoTo Close_Form_Button_Click_Err

Dim myName As String
myName = Me.Name
DoCmd.Close acForm, myName, acSaveNo

Close_Form_Button_Click_Exit:
Exit Sub

Close_Form_Button_Click_Err:
MsgBox Error$
Resume Close_Form_Button_Click_Exit

End Sub

Private Sub Delete_Click()

DoCmd.OpenForm "Remove Participant Form", acNormal, , , acFormPropertySettings, acDialog, Me.ID

End Sub

Private Sub Form_Open(Cancel As Integer)

Me.Filter = "[ID] = " & Me.OpenArgs 'Sets the filter criteria
Me.FilterOn = True ' Applies the filter
Me.Updated_Date = Now

End Sub

Private Sub Last_Name_BeforeUpdate(Cancel As Integer)

Debug.Print "Before update occurred"
Dim myvar As String
myvar = "Before update"

End Sub

Private Sub Last_Name_Exit(Cancel As Integer)

' Debug.Print "Exit occurred"
Dim myvar As String
myvar = "Exit"

End Sub

Private Sub Last_Name_LostFocus()

' Debug.Print "Lost focus occurred"
Dim myvar As String
myvar = "Lost focus"

End Sub

Private Sub Save_Participant_Btn_Click()

Dim hasErr As Integer
Dim ctrlName As String
hasErr = 0 'set to show no errors yet

hasErr = hasErr + verifyControl(Me, "[Last Name]")
hasErr = hasErr + verifyControl(Me, "[First Name]")
hasErr = hasErr + verifyControl(Me, "[Address]")
hasErr = hasErr + verifyControl(Me, "[City]")

If Me.State_Cbox.ListIndex = -1 Then 'no State value has been selected

hasErr = hasErr + 1
SetBorder Me, "[State CBox]", "red"

Else
SetBorder Me, "[State CBox]", "black"
End If

hasErr = hasErr + verifyControl(Me, "[Zip]")
hasErr = hasErr + verifyControl(Me, "[Phone]")

If hasErr > 0 Then 'at least one control is empty
MsgBox "The highlighted fields cannot be empty!"
Exit Sub
End If

Dim bMark As Integer
bMark = Me.ID 'save the id of the person being edited before form is closed

Dim myName As String
myName = Me.Name
DoCmd.Close acForm, myName, acSaveYes

Forms![Participants Edit].Requery 'Update the underlying snapshot to reflect the edit
Dim rs As DAO.Recordset
Set rs = Forms![Participants Edit].RecordsetClone 'create a clone of the recordset in the main form
rs.FindFirst "ID = " & bMark 'find the first (and only) record for this person's ID
If Not rs.NoMatch Then
Forms![Participants Edit].Bookmark = rs.Bookmark
Else
MsgBox "Record not found!"
End If
Set rs = Nothing 'release the recordset object

End Sub

 

[Pat Hartman]

The reason it is only "mostly" working is that if I click on a required field and delete all characters then click on another field this pops up:

I guess you didn't read my comment in #3. I explained how you were probably "deleting" the value incorrectly although select all and then delete should work.

 

[theDBguy]

This is all the code from the Form's vba module.

Quick question: What is the name of the field that you were deleting when you got that error?

 

[isaksp00]

Pat, I did see your comment in #3. Maybe I don't understand it. If I understood it you meant that the way to try to delete text in a text box is to use Esc? I tried selecting the field text, the Del key and then Esc. That restored the original value. But if a user somehow deleted the prior entry and then clicked into another field (maybe by accodent) they should be able to get a friendly error message. I can't think of an event that could handle that case, check for ZLS and then handle it.

The field I was deleting from is [Last Name]. I then clicked into [First Name]. Here is that form:

In case it helps, here is the subroutine it calls that is in a module, but that code should not be the culprit because it isn't called until the Save button is clicked.

Public Function verifyControl(myForm As Form, cName As String) As Integer

Dim theForm As Form
Dim ctrlVal As Variant
Dim ctrlName As String
Dim isErr As Integer

Set theForm = myForm 'get reference to the form that called this sub
ctrlName = cName 'get string passed in by calling code
ctrlVal = theForm.Controls(ctrlName).Value 'get the value of the specified control

If IsNull(ctrlVal) Or Trim(ctrlVal & vbNullString) = vbNullString Then 'empty string or null entry

' Empty, so set border red and 2 pt, and return error value 1

SetBorder theForm, ctrlName, "red"
isErr = 1 'control has an error

' Not mpty, so set border black and hairline, and return error value 0

Else

SetBorder theForm, ctrlName, "black"
isErr = 0 'control has no error

End If

verifyControl = isErr

End Function

Public Sub SetBorder(myForm As Form, cName As String, style As String)

Dim hexColor As String
Dim vbaColorVal As Long
Dim bWidth As Integer
Dim theForm As Form

Set theForm = myForm

Select Case style
Case "red"
hexColor = "0000FF" 'set color as Red
vbaColorVal = CLng("&H" & hexColor)
bWidth = 2 'set to 2 pt width
Case "black"
hexColor = "000000" 'set color as black
vbaColorVal = CLng("&H" & hexColor)
bWidth = 0 'set to hairline width
End Select

theForm.Controls(cName).BorderColor = vbaColorVal 'set border color
theForm.Controls(cName).BorderWidth = bWidth 'set border width

End Sub

 

[Pat Hartman]

Which line of code is failing? Add an error trap to both procedures. Just display the error number and error message.
When you get the error message, use cntl break to stop the code. Then click on the line that says "Resume". That will move the cursor to the line that failed. Note this is a debugging trick. Resume will never be executed in normal operation since it immediately follows a command to resume execution in the ExitProc sub

Use this as a model.

Sub CheckDirectory(PathName As String)
    Dim CheckDir As String
    On Error GoTo ErrProc
    
        CheckDir = Dir(PathName)
        If CheckDir <> "" Then
        Else
            MkDir PathName
        End If

ExitProc:
        Exit Sub
ErrProc:
        Select Case Err.Number
            Case Else
                MsgBox Err.Number & " -- " & Err.Description
                Resume ExitProc
                Resume
        End Select
End Sub

 

[arnelgp]

the Save and Cancel buttons are of no use if you are not using Transation. when you press any button, the form will try to be saved before any of your button code will be called.

 

[isaksp00]

Which line of code is failing? Add an error trap to both procedures. Just display the error number and error message.
When you get the error message, use cntl break to stop the code. Then click on the line that says "Resume". That will move the cursor to the line that failed. Note this is a debugging trick. Resume will never be executed in normal operation since it immediately follows a command to resume execution in the ExitProc sub

Use this as a model.

Sub CheckDirectory(PathName As String)
    Dim CheckDir As String
    On Error GoTo ErrProc
 
        CheckDir = Dir(PathName)
        If CheckDir <> "" Then
        Else
            MkDir PathName
        End If

ExitProc:
        Exit Sub
ErrProc:
        Select Case Err.Number
            Case Else
                MsgBox Err.Number & " -- " & Err.Description
                Resume ExitProc
                Resume
        End Select
End Sub

Thanks, Pat! It doesn't seem to me that any specific line of code is failing. My uneducated guess is that it is something inherent in what Access does before any of the VBA code is triggered. But I don't know how to detect that.
I added code per your model, example in the Cancel button When Clicked event (see below). I also added it to the Before Update event code of the Last Name field.
I put a breakpoint on the line in red. I open the main "Person" form and click the Edit Participant button, which opens the form in question. The text in the topmost field (Last Name) is all selected on open. I hit the delete key (simulating a user error) which clears the field. I then click on the next field down (First Name). The Null Value error dialog immediately appears. Hitting Ctrl-Break does nothing. Clicking the OK button in the error dialog closes it and I'm back in the form. I can only escape (without putting some text back in the emptied field) by right clicking on the dialog's title and then Close. This displays the dialog again, but then if I click OK I get a dialog telling me I can't save the record:

I don't understand why it seems to try to save the record after just clicking on another field - perhaps there is some Form or Field property that causes this that I have set incorrectly? I checked the Validation Rule property of the field and it is blank. Or maybe Access sees that the design of the table the form is bound to has this column as Not Null and it throws an error at some (seemingly uncatchable) event related to exiting that field?
Clicking Yes closes the Edit Participant form. No VBA debug break occurs, so I don't see a way to Resume anything to see the msgbox output you suggested.

Private Sub Cancel_Btn_Click()

On Error GoTo ErrProc

Dim myName As String
myName = Me.Name
DoCmd.Close acForm, myName, acSaveNo

'Close_Form_Button_Click_Exit:
' Exit Sub
'
'Close_Form_Button_Click_Err:
' MsgBox Error$
' Resume Close_Form_Button_Click_Exit

ExitProc:
Exit Sub
ErrProc:
Select Case Err.Number
Case Else
MsgBox Err.Number & " : " & Err.Description
Resume ExitProc
Resume
End Select

End Sub

 

[Gasman]

Upload a copy of your DB, with just enough to see the issue.

 

[isaksp00]

I can try, but it uses SQL Server as a backend. Right now it's a server running SQL Server Express on a PC at home. It can also be linked to an Azure server, but that has PI like phone nums and addresses and it'd be hard to anonymize it. I don't think anything would work since you can't get to my home server, but I'll try to see if it can show the issue.

 

[isaksp00]

Ok, an update. I changed that Last Name col in the SQL Server DB to allow nulls and relinked the table. This (to me, odd) behavior disappeared. I can use a workaround where I rely solely on event code in the Access front end to prevent null/empty values.
But, it is still baffling to me why the UI can't be configured to deal with this case.

 

[Gasman]

I have never used SQL server, but if it was an access BE, one can convert to local tables. You could just use a query to populate a local table?
If you are worried about data sensitivity then encode it using this.

Again, just enough to see the issue, so that might just be one form and one table?

 

[isaksp00]

I'm going to mark this as solved. I don't know why Access behaves this way, and I think it is a design error or bug in Access itself, but by disabling the Not Null validation at the server level I can use form event code to ensure data is entered.

While I'd like to know why this behavior occurs, it's much more work to try to sanitize the data and then expose the server to let others do debugging than to add the event code (which I was intending anyway).

Thanks!

 

[Minty]

Because the backend is SQL server and your Server table field didn't allow nulls, Access doesn't know about that until it's too late.

Table level validation is a pain to use with anything other than an Access table, as Access doesn't apply the rules, the server does. Table level constraints need validating in the FORMS Before Update event (Not the individual field controls).

 

[isaksp00]

Minty, in my case it feels like Access does know about the table being Not Null and it tries to validate the form field contents too early. The error I got pops up before any Event trigger I tried, and it occurs if the linked table has that field set to Not Null but does not occur if the field is Nullable. This is well before any attempt to update in the database, at least, any attempt in my VBA code.