Solved

Validate Null values

Posted on 2002-07-18
21
324 Views
Last Modified: 2012-05-04
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
0
Comment
Question by:tdenny
  • 12
  • 5
  • 3
  • +1
21 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 100 total points
ID: 7162544
Did you try:

If Len(nz(Me.Requester)) = 0 Then
 strMessage = strMessage & vbCrLf & "The Requester must be filled"
 Me.Requester.SetFocus
End If

Nic;o)
0
 
LVL 7

Expert Comment

by:ildc
ID: 7162579
Hi first of all, I would indeed remove the checking of the table, you don't really need them. (neither in the form BTW)

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
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7162620
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)
0
 

Author Comment

by:tdenny
ID: 7162627
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.
0
 
LVL 7

Expert Comment

by:ildc
ID: 7162632
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
0
 

Author Comment

by:tdenny
ID: 7162635
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.
0
 

Author Comment

by:tdenny
ID: 7162639
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?
0
 

Author Comment

by:tdenny
ID: 7162648
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?
0
 

Author Comment

by:tdenny
ID: 7162684
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.
0
 

Author Comment

by:tdenny
ID: 7162727
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
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:tdenny
ID: 7162750
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
0
 

Author Comment

by:tdenny
ID: 7162820
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
0
 

Author Comment

by:tdenny
ID: 7162822
Sorry all for the repetitive comments.  Bad habit of using the refresh button.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7163115
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)
0
 

Author Comment

by:tdenny
ID: 7163152
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.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7163310
Can you drop the compacted and zipped .mdb in my nico5038 mailbox "at" yahoo.com and I'll have a look.

Nic;o)
0
 

Expert Comment

by:CyberAccess
ID: 7164710
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.
0
 
LVL 7

Expert Comment

by:ildc
ID: 7164719
Hi CyberAccess,

This "If variable = Null" can never work. Nothing is equal to the Null valuen not even Null...

regards
0
 

Author Comment

by:tdenny
ID: 7169614
Thanks to all for their suggestions.  Nico - really appreciate your help.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7169675
You're welcome, hope your users value the changes ;-)

Nic;o)
0
 

Author Comment

by:tdenny
ID: 7170487
I'm sure they will find their new tool very worthwhile.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now