I know this seems really simple, but I can't seem to code this right. I need to stop a database update to a table if the users haven't filled in all the required fields. In the BeforeUpdate event, I have tried the following code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
Dim strMsg As String, strTitle As String
Dim intStyle As Integer
If Me.Event Is Null Or Me.Event_Date Is Null Or Me.Staff Is Null Then
MsgBox "Event, Date and Staff fields must be Entered"
Cancel = True
Thanks, but what's the difference. I also have a hard time understanding when to use the 'Required' property of the field in the table editor, the 'Required' property of the field in the form, code checking for nulls on the OnExit event of the field or code checking for nulls on the BeforeUpdate of the record. What's the difference? Is one way better than another?
Check out access vba help on the "Nz" function and the "IsNull" fucntion. I can't say it better than the help does. It does give give a comparison between the two. I like to use the Nz fucntion - this will trap Nulls and empty strings - but there is a time and place for each of these functions.
>I also have a hard time understanding when to use the 'Required' property of the field in the table editor
Depends on you needs - for example LastName, SS#, Address1 would most likely be required but Address2 would not since not everyone has a second address. If you must have the data for whatever reason then set it to required in the table. You will then trap for the missing data in the form that sits on top of the table to provide a user friendly warning message. You should always check on the form's BeforeUpdate - the user may not have never entered the control, so the OnExit Event would never fire. This is not to say that you should not use both. You may want to loop through the controls in the forms even and provide a generic warning and a control specific waning for the control's event.
I did set the required field on the table column as my first resort. But, when I entered just info into FIELD1, but not FIELD2 or FIELD3, and then went to the next record, Access just happily added the record to the table. I'm not sure if it's putting in zeros by default and that's allowing the records to pass the 'required' test. So, I eventually put the code into the BeforeUpdate event as you suggested. That seemed to work fine when I used the Nz function. Though I still don't understand why saying
If Me.field1 IS NULL OR Me.field1 = 0 Then
didn't work. If I separated them into two separte if statements that worked. Or if I used the NZ (which I guess combines the two statements) that worked. But why not the original syntax.
I'm still befuddled, though at least the form is semi-functional.
So still having a problem with this. The code for the message box now works, but the data is saved anyway. I don't know how to prevent an update to the data here. I've looked at the CancelEvent, CancelUpdate, Cancel=True, etc. and can't get anywhere.
It seems that it should be so simple, but in the BeforeUpdate event, if I vet the data and find a problem, what's the best way to cancel an update to the data?