If NULL don't Save Record

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

How do I modify the function below so that I CANNOT save
the record if the textbox field is empty?

Tom

*************************


Private Sub SaveRecord_Click()
On Error GoTo Err_SaveRecord_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70

Exit_SaveRecord_Click:
Exit Sub

Err_SaveRecord_Click:
MsgBox Err.Description
Resume Exit_SaveRecord_Click

End Sub
 
Tom said:
How do I modify the function below so that I CANNOT save
the record if the textbox field is empty?

Tom

*************************


Private Sub SaveRecord_Click()
On Error GoTo Err_SaveRecord_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu,
acSaveRecord, , acMenuVer70

Exit_SaveRecord_Click:
Exit Sub

Err_SaveRecord_Click:
MsgBox Err.Description
Resume Exit_SaveRecord_Click

End Sub
Click to expand...

The simplest approach is to make the field a Required field in the
table. Then no record can be saved if the field is Null. You may want
to check first here in the form anyway, so as to display a more
meaningful message. That could look something like this:

'----- start of revised code -----
Private Sub SaveRecord_Click()

On Error GoTo Err_SaveRecord_Click

If IsNull(Me!YourTextField) Then
MsgBox "Required field 'YourTextField' is empty, so " & _
"the record can't be saved."
Me!YourTextField.SetFocus
Else
RunCommand acCmdSaveRecord
End If

Exit_SaveRecord_Click:
Exit Sub

Err_SaveRecord_Click:
MsgBox Err.Description
Resume Exit_SaveRecord_Click

End Sub
'----- end of revised code -----
 
The *only* way to prevent the record being saved in code is to cancel the
BeforeUpdate event of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[YourTextBoxNameHere]) Then
Cancel = True
Msgbox "Cancelled!"
End If
End Sub

When your command button tries to save the record, the code above will block
it.

There is a simpler solution that requires no code.
Open the table in Design View, select the field that should not be blank,
and set its Required property to Yes. If it is a Text, Memo, or Hyperlink
type field in Access 2000 or above, also set its Allow Zero Length property
to No.
 
Absolutely... I'm working on a bunch of functions right
now that the easiest didn't even come to mind.

Thanks,
Tom
 
Back
Top