tdenny
asked on
Validate Null values
Hello experts,
I have tried several things to solve this problem but have failed so far. I don't do lot of Access dev work any more.
I have several fields on a form that are required. I have them set as required and not allowed zero length. The problem is I don't like the system generated error messages. So.......
Do I use a validation rule/text at the table level? Or at the form level? Do I use an OnClose event - what would the code look like. I have tried some code in the onClick event of the Save button see below but the record seems to save anyway. If I get this code to work then do I remove the properties (required, and no zero length) I set at the table level? Thanks a million.
Dim strMessage As String
'Test for Requester length
If Len(Me.Requester) = 0 Then
strMessage = strMessage & vbCrLf & "The Requester must be filled"
Me.Requester.SetFocus
End If
'Test for Log Description length
If Len(Me.LogDesc) = 0 Then
strMessage = strMessage & vbCrLf & "The Log Description must be filled"
Me.LogDesc.SetFocus
End If
'Test for Server Name length
If Len(Me.ServerName) = 0 Then
strMessage = strMessage & vbCrLf & "The Server Name must be filled"
Me.ServerName.SetFocus
End If
'Test for Problem Description length
If Len(Me.ProbDesc) = 0 Then
strMessage = strMessage & vbCrLf & "The Problem Description must be filled"
Me.ProbDesc.SetFocus
End If
'Test or error occured
If Len(strMessage) > 0 Then
MsgBox strMessage
Else
' the save action you get automatically when inserting a record/save button
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If
End Sub
I have tried several things to solve this problem but have failed so far. I don't do lot of Access dev work any more.
I have several fields on a form that are required. I have them set as required and not allowed zero length. The problem is I don't like the system generated error messages. So.......
Do I use a validation rule/text at the table level? Or at the form level? Do I use an OnClose event - what would the code look like. I have tried some code in the onClick event of the Save button see below but the record seems to save anyway. If I get this code to work then do I remove the properties (required, and no zero length) I set at the table level? Thanks a million.
Dim strMessage As String
'Test for Requester length
If Len(Me.Requester) = 0 Then
strMessage = strMessage & vbCrLf & "The Requester must be filled"
Me.Requester.SetFocus
End If
'Test for Log Description length
If Len(Me.LogDesc) = 0 Then
strMessage = strMessage & vbCrLf & "The Log Description must be filled"
Me.LogDesc.SetFocus
End If
'Test for Server Name length
If Len(Me.ServerName) = 0 Then
strMessage = strMessage & vbCrLf & "The Server Name must be filled"
Me.ServerName.SetFocus
End If
'Test for Problem Description length
If Len(Me.ProbDesc) = 0 Then
strMessage = strMessage & vbCrLf & "The Problem Description must be filled"
Me.ProbDesc.SetFocus
End If
'Test or error occured
If Len(strMessage) > 0 Then
MsgBox strMessage
Else
' the save action you get automatically when inserting a record/save button
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ildc,
Nesting isn't necessary as the strMessage acts as the "error memory" and will enable the display of all errors.
BTW the table validation on the fields still can be set to protect the filling when people use the table "straight away" and incase you made a coding error..
Nic;o)
Nesting isn't necessary as the strMessage acts as the "error memory" and will enable the display of all errors.
BTW the table validation on the fields still can be set to protect the filling when people use the table "straight away" and incase you made a coding error..
Nic;o)
ASKER
OK,
I will try some of these suggestions. Nico5038 - I tried the NZ() function before as well but it didn't work either. I will revise the code to put it back in because I think it's the smart way to go. Nico/ildc as far as nesting the If's I'm not sure the best way to do this. The way I have the code now if you get returned a false then you go back to the form with the cursor to that control. I agree that the before update event would be a good place.
I will try some of these suggestions. Nico5038 - I tried the NZ() function before as well but it didn't work either. I will revise the code to put it back in because I think it's the smart way to go. Nico/ildc as far as nesting the If's I'm not sure the best way to do this. The way I have the code now if you get returned a false then you go back to the form with the cursor to that control. I agree that the before update event would be a good place.
Yep,
But if he doesn't nest and Requester(or another one) and ProbDesc are empty, the focus will always be on the latter one, and setting the focus in all those if's doesnt serve any purpose...
Regards
But if he doesn't nest and Requester(or another one) and ProbDesc are empty, the focus will always be on the latter one, and setting the focus in all those if's doesnt serve any purpose...
Regards
ASKER
Nico,
If I keep the table validation in place won't the system validation over ride the form validation? I agree with your assessment of the table validation but hopefully none of the users will be able to get to it. I'm not going to worry about that at the moment.
If I keep the table validation in place won't the system validation over ride the form validation? I agree with your assessment of the table validation but hopefully none of the users will be able to get to it. I'm not going to worry about that at the moment.
ASKER
ildc,
I'm happy to try and test it either way (nested or as is) Do you have a suggestion on how to revise the code?
I'm happy to try and test it either way (nested or as is) Do you have a suggestion on how to revise the code?
ASKER
ildc,
I'm happy to try and test it either way (nested or as is) Do you have a suggestion on how to revise the code?
I'm happy to try and test it either way (nested or as is) Do you have a suggestion on how to revise the code?
ASKER
Nico,
I revised the code with the NZ function in it and put in in the before update event. I also changed the Save code per ildc's suggestion. I got a run time error. I then switched my save code back to the DoCmd code above and also got a run time error.
The good news is that when I went to one of the fields that was not required and clicked the save button all of the fields that cannot be null showed up in the error message. The message eliminated each one as I filled them in. This worked cool. The only drawback was that the cursor always went to the last field I had not filled in. I suppose the nesting issue would solve this but it's really a nit in my opinion. Any suggestions on how to handle the Save part of my transaction. I will get the exact message.
I revised the code with the NZ function in it and put in in the before update event. I also changed the Save code per ildc's suggestion. I got a run time error. I then switched my save code back to the DoCmd code above and also got a run time error.
The good news is that when I went to one of the fields that was not required and clicked the save button all of the fields that cannot be null showed up in the error message. The message eliminated each one as I filled them in. This worked cool. The only drawback was that the cursor always went to the last field I had not filled in. I suppose the nesting issue would solve this but it's really a nit in my opinion. Any suggestions on how to handle the Save part of my transaction. I will get the exact message.
ASKER
Nico, Ildc
I tested putting the required (Yes) fields and allow zero length (No) settings back on and I got the system generated error message after I got the custom errors. Just FYI. Any way the Run-Time error is as follows for the save function:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Run-Time Error '2115'
the Macro or function set to the before update property for this field is preventing Microsoft Access from saving the data in the field.
The code with the save function set to
Application.RunCommand acmdsaverecord
Run-Time Error 2501
The RunCommand action was cancelled
I tested putting the required (Yes) fields and allow zero length (No) settings back on and I got the system generated error message after I got the custom errors. Just FYI. Any way the Run-Time error is as follows for the save function:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Run-Time Error '2115'
the Macro or function set to the before update property for this field is preventing Microsoft Access from saving the data in the field.
The code with the save function set to
Application.RunCommand acmdsaverecord
Run-Time Error 2501
The RunCommand action was cancelled
ASKER
Nico, Ildc
I tested putting the required (Yes) fields and allow zero length (No) settings back on and I got the system generated error message after I got the custom errors. Just FYI. Any way the Run-Time error is as follows for the save function:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Run-Time Error '2115'
the Macro or function set to the before update property for this field is preventing Microsoft Access from saving the data in the field.
The code with the save function set to
Application.RunCommand acmdsaverecord
Run-Time Error 2501
The RunCommand action was cancelled
I tested putting the required (Yes) fields and allow zero length (No) settings back on and I got the system generated error message after I got the custom errors. Just FYI. Any way the Run-Time error is as follows for the save function:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Run-Time Error '2115'
the Macro or function set to the before update property for this field is preventing Microsoft Access from saving the data in the field.
The code with the save function set to
Application.RunCommand acmdsaverecord
Run-Time Error 2501
The RunCommand action was cancelled
ASKER
Nico, Ildc
I tested putting the required (Yes) fields and allow zero length (No) settings back on and I got the system generated error message after I got the custom errors. Just FYI. Any way the Run-Time error is as follows for the save function:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Run-Time Error '2115'
the Macro or function set to the before update property for this field is preventing Microsoft Access from saving the data in the field.
The code with the save function set to
Application.RunCommand acmdsaverecord
Run-Time Error 2501
The RunCommand action was cancelled
I tested putting the required (Yes) fields and allow zero length (No) settings back on and I got the system generated error message after I got the custom errors. Just FYI. Any way the Run-Time error is as follows for the save function:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Run-Time Error '2115'
the Macro or function set to the before update property for this field is preventing Microsoft Access from saving the data in the field.
The code with the save function set to
Application.RunCommand acmdsaverecord
Run-Time Error 2501
The RunCommand action was cancelled
ASKER
Sorry all for the repetitive comments. Bad habit of using the refresh button.
Basically you need to test:
If Len(Me.Requester) = 0 OR IsNull(Me.Requester) Then
...
I guess the NZ will work when used like:
If Len(NZ(Me.Requester,"")) then
Just keep the code as is, just make sure the fields are tested from bottom to top, thus moving the focus to the first field in error.
Nic;o)
If Len(Me.Requester) = 0 OR IsNull(Me.Requester) Then
...
I guess the NZ will work when used like:
If Len(NZ(Me.Requester,"")) then
Just keep the code as is, just make sure the fields are tested from bottom to top, thus moving the focus to the first field in error.
Nic;o)
ASKER
Nico,
OK, I figured out the Run-Time errors. I had that save code in the above code as well as the save code attached to the save button. It now seems to work but.....I have found another problem. If I hit the save button it gives me the error message. I then say ok to the message. If I hit save again the record takes. Any ideas on how to make this more bullet proof? Thanks.
OK, I figured out the Run-Time errors. I had that save code in the above code as well as the save code attached to the save button. It now seems to work but.....I have found another problem. If I hit the save button it gives me the error message. I then say ok to the message. If I hit save again the record takes. Any ideas on how to make this more bullet proof? Thanks.
Can you drop the compacted and zipped .mdb in my nico5038 mailbox "at" yahoo.com and I'll have a look.
Nic;o)
Nic;o)
To validate Null try
If IsNull(variable)
If Not IsNull(variable)
If variable = Null
If variable = Nothing
If variable = ""
guarantee one of these will work.
If IsNull(variable)
If Not IsNull(variable)
If variable = Null
If variable = Nothing
If variable = ""
guarantee one of these will work.
Hi CyberAccess,
This "If variable = Null" can never work. Nothing is equal to the Null valuen not even Null...
regards
This "If variable = Null" can never work. Nothing is equal to the Null valuen not even Null...
regards
ASKER
Thanks to all for their suggestions. Nico - really appreciate your help.
You're welcome, hope your users value the changes ;-)
Nic;o)
Nic;o)
ASKER
I'm sure they will find their new tool very worthwhile.
Your code looks pretty good, but you'd better nest the if's because now the first if will be evaluated, and then the second and so on even if the first one returns a true.
You best put this in the beforupdate of the form so it can still be cancelled and will not be saved.
Forget also the coding you used to save the record, use this one instead :
application.runcommand acmdsaverecord
regards