Access not validating code 7

[justlearning2003]

Good day,

It is something really weird going on with the code below. I am tring to validate textboxes on my form. But what is happening is that if I leave any or all of the textboxes blank, access skips my code validating empty boxes and jumps right into the sql down below. Any ideas on this one? I am using Access 2K.

Private Sub cmdopensubmit_Click()


If txtcasenum.Value = "" Then
MsgBox "You must enter a Case Number...."
Exit Sub
End If
If cbostatus.Value = "" Then
MsgBox "You must enter the Status....", vbCritical
Exit Sub
End If
If referraltype.Value = "" Then
MsgBox "You must enter Referral Type....", vbCritical
Exit Sub
End If
If txtopendate.Value = "" Then
MsgBox "You must enter a Open Date....", vbCritical
Exit Sub
End If
If txtagentidopen.Value = "" Then
MsgBox "You must enter the Agent ID who opened the case....", vbCritical
Exit Sub
End If
If txtworked.Value = "" Then
MsgBox "You must enter if the case has been worked or not....", vbCritical
Exit Sub
End If
If cbowaiting.Value = "" Then
MsgBox "You must verify if the case is done or not....", vbCritical
Exit Sub
End If
If txtrecentfollowup.Value = "" Then
MsgBox "You must the follow up date....", vbCritical
Exit Sub
End If
If txtextracomments.Value = "" Then
MsgBox "You must the comments....", vbCritical
Exit Sub
End If



Dim SqlStr As String

SqlStr = "INSERT INTO tblopenref (casenum, status, referraltype, opendate, agentidopen, worked, waiting, waitingothercomments, recentfollowup, extracomments, closedate) Values" _
& "('" & Forms!frmopenrefv2!txtcasenum & "', '" & Forms!frmopenrefv2!cbostatus & "','" & Forms!frmopenrefv2!referraltype & "', #" & Forms!frmopenrefv2!txtopendate & "#, '" & Forms!frmopenrefv2!txtagentidopen & "', '" & Forms!frmopenrefv2!txtworked & "', '" & Forms!frmopenrefv2!cbowaiting & "', '" & Forms!frmopenrefv2!waitingothercomments & "', #" & Forms!frmopenrefv2!txtrecentfollowup & "#, '" & Forms!frmopenrefv2!txtextracomments & "', #" & Forms!frmopenrefv2!closedate & "#)"

DoCmd.RunSQL (SqlStr)



End Sub

 

[kjv1611]

First thought is this: if you use a conditional statement, and you don't want part of the code to run, you need to use an else, or put an Exit Sub in each If/Then clause... so if want else, then do this
If txt1 = "" Then
"Enter a value"
Else
Run SQL Code
End If

Or with the ExitSub statement added in:
If txt1 = "" Then
"Enter a value"
Exit Sub
End If

Run SQL Code

I just got through doing something similar for a database form. I had to use If cmbCombo1 <> "" Then statements. For some reaso (someone else probably knows), Access seemed to just ignore my statements when I used If txtText1 = "" Then.
When I used <> "" instead of = "", it worked fine.. here's what I used, it should give you an idea of what I'm talking about... and it works (just change the values you want to use:

Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim strMessage As String
strMessage = "Please enter audit criteria to export."

If cmbYear <> "" Then
    If cmbMonth <> "" Then
        If cmbTeamLeader <> "" Then
            If cmbStatus <> "" Then
                [SQL Code here]
            Else
                MsgBox strMessage
                cmbStatus.SetFocus
            End If
        Else
            MsgBox strMessage
            cmbTeamLeader.SetFocus
        End If
    Else
        MsgBox strMessage
        cmbMonth.SetFocus
    End If
Else
    MsgBox strMessage
    cmbYear.SetFocus

End If

    
Exit_cmdRunQuery_Click:
    Exit Sub

Err_cmdRunQuery_Click:
    MsgBox Err.Description
    Resume Exit_cmdRunQuery_Click

End Sub

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[justlearning2003]

Ok Great.

Thanks for the help.

 

[TheAceMan1]

How are ya justlearning2003 . . . . .

The problem is your pinging against a [blue]Null String[/blue] which is different from the [blue]Null Value[/blue] ([purple]your not checking Nulls![/purple]). Change you conditionals to the following:

If Len(txtcasenum.Value & "") = 0 Then

Should do it!

See Ya! . . . . . .

 

[willir]

The Ace is right again. If X = "" will fail on Null values.

I use a slightly different variation which has not failed me since LittleSmudge showed me this trick.

For numerics, I use...

If Nz(Me.MyNumeric, 0) = 0 Then

For strings, I use...

If Len(Nz(Me.MyNumeric, "") = 0 Then

Microsoft, in their wizards, use a variation of the latter.

Richard

 

[RoyVidar]

And just to add a little something, the above should take care of Nulls and null strings, but sometimes you might encounter a text control where you'd also need to test if it might contain only a space, then perhaps:

[tt]if (len(trim$(me("txtMyText").Value & vbnullstring)) = 0) then[/tt]

or

[tt]if (len(trim$(nz(me("txtMyText").value, vbnullstring))) = 0) then[/tt]

Since it's weekend, some faq's and threads I find interesting;-)

Here's a faq by TonyJollans on "Nulls and other nothings" faq707-3710, one from jrbarnett offering some suggestions on how to get around them faq181-3884 (much the same as here), an interesting discussion on nulls thread669-820770. Yeah - and a discussion on vbnullstring vs "" thread222-259902 (from VB5/6 forum), some tehcnicalities, but vbnullstring is somewhat faster than "".

Roy-Vidar

 

[kjv1611]

Well, I've yet learned more valuable info! I tried to answer, but found answers myself! [SMILE] Thanks guys!

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[ZmrAbdulla]

Hi Roy, Aceman1 & Richard
I am using the code below (it is used as Public)

If IsNull(Forms!FrmEmpMaster!EmployeeID) Then
MsgBox "Enter employee ID"
    Forms!FrmEmpMaster.EmployeeID.SetFocus
Else
If IsNull(Forms!FrmEmpMaster!JoiningDate) Then
MsgBox "Please enter Joining Date!"

Forms!FrmEmpMaster.EmployeeID.SetFocus
Else
Forms!FrmEmpMaster.TabCtl1.Pages(1).SetFocus
End If
End If

I am using the same code for text & numerical values
is that ok? or should I change to something else?
If it is currency filed then I use

If Forms!FrmEmpMaster.SalaryAmount.Value <= 0 then
MsgBox "Salary Amount can't be a negative Value or Zero!"
Forms!FrmEmpMaster.SalaryAmount.SetFocus
End If

I am studying Access with your great help....
Thanks

Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[TheAceMan1]

How are ya ZmrAbdulla . . . . .

[blue]I am using the same code for text & numerical values is that ok?[/blue]

No problem in the case you've cited . . . . .

Not sure what your looking/asking for with the currency code. Please specify . . . . .

See Ya! . . . . . .

 

[ZmrAbdulla]

Thanks Aceman1,
The currency field default value will be "0.00" So I can't use If IsNull...
Or should I use If Is Null... also to force the user enter a value? If yes how can I?

If Forms!FrmEmpMaster.SalaryAmount.Value <= 0 then
MsgBox "Salary Amount can't be a negative Value or Zero!"
Forms!FrmEmpMaster.SalaryAmount.SetFocus
Else
If IsNull(Forms!FrmEmpMaster!SalaryAmount) Then
MsgBox "Plaese enter Salary Amount"
    Forms!FrmEmpMaster.SalryAmount.SetFocus

End If
End If

Is that possible to shorten the code?

Thanks


Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[TheAceMan1]

ZmrAbdulla . . . . .

Realize default is specifically for a new record. More that that its easily deleted by the user, who can then move on to another control, leaving a Null!

Wether you checking for Null or some value or some comparison, your talking [blue]Validation[/blue]. As such if required, you can set validation rules for most controls. Be aware, the focus will not be allowed to leave the control until the rule is met. This can be desirable/undesirable depending on your needs. You can also validate with your own custon code.

As the DB designer, it is up to you to decide what those rules will be if applied. This is a sticky area in design. Sometimes its easy, at other times it can drive you mad.

Along with Validation, there's the Required property in table design, for most fields.

Search the FAQs here, I believe you'll find more info there. If not, I'll return with some myself.

See Ya! . . . . . .

 

[ZmrAbdulla]

Thanks Aceman1,
As you said it is the sticky area of designing...
The save button validates the code and if there is any field missing it forces the user to enter it. Here in this case I am using it in Public modules (so that I can avoid the cluster) because the form I am using have a tabcontrol with 10 pages , 12 subforms and more than 60 fields!! Most of them are required.I need my own messages than Access'.. User can't move to another subform until he filled the required field. Either he has to enter the data as required and click Save button to enable Close button or click delete button to remove the data from the Master and subtables and enable Close button.(like he is trapped!!!)
Setting validation rules also a good idea. I have little knowledge about validation rule setting in the table design. Is there any article to read about this?

Thanks again

Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[willir]

Zameer

A big can of worms here. I can not think of a single reference that addresses your specific query - but various books on Access will have chapters dedicated to error handling. From my perspective, there are two approaches...

- Use the database design to assist and presever data integrity. Setup relationships, setup fields as mandatory where required. By doing so, Access will prevent bad data being written to the table.

- Grunt work. Use a well thought out approach to "bullet proof". There are some "coders" that participate in Tek-Tip that leave me in their dust. However, I like to...
-- keep coding simple, and easy to read where possible. For example, use SELECT CASE instead of nested IF's. Use meaningful names for your variables.
-- modularize and centralize code

I like to use a few "flags". For example:

Function PostIt (....,strMsg as String) as Boolean

booPass = True
strMsg = ""

'Check To make sure transaction to post
If curTransAmount = 0 Then
    booPass = False
    strMsg = strMsg & "Zero transaction payment made" & vbCrLf
End If

'Type of transaction
If IsNull(strTransType) Then
    booPass = False
    strMsg = strMsg & "No Transaction type" & vbCrLf
End If
    
'Valid date for transaction
If Not IsDate(dtTransDate) Then
    booPass = False
    strMsg = strMsg & "Invalide date " & dtTransDate & vbCrLf
End If

'Member to charge transaction against
If Nz(lngMemberID) > 0 Then
    lngTest = DLookup("[stMemberID]", "stMemberTbl", "[stMemberID] = " & lngMemberID)
    If lngTest = 0 Then
        booPass = False
        strMsg = strMsg & "Invalid Member ID " & lngMemberID & vbCrLf
    End If
Else
    booPass = False
    strMsg = strMsg & "No Member assigned to transaction" & vbCrLf
End If
...

PostIt = booPass

And then this module is called elsewhere from the forms.

booPost = PostIt (...., strMsg)

OR

If PostIt (...., strMsg) Then...

This example shows how I avoided a convaluted nested IF coding by using a flag. Moreover, I use a function to test data integrity which can be called from more than one location, and is very readable. A SELECT CASE also works in some cases.

Richard

 

[ZmrAbdulla]

Richard,
That was great!! I was looking for these types of codes.
I never had a systematic approach on learning Access. Sometimes I don't know the basic things and know some advenced.
So I can reduce the size of database by saving the code in a Public module and calling from anywhere..

I will certainly move to these type approach on data validation

Thanks again

Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[RoyVidar]

Yes - validation is a huge topic, and most of the references cover small samples... When building more complex forms etc, one often end up with conflicting events or other funny stuff.

I'm not very fond of validation rules, but sometimes use the required property and set the allow zero length of text fields to no.

If your other validation code doesn't trap this error, there will be a form error which can be "trapped". It's often the dataerr 3314 (think 2107 would be breakding a control validation, 2113 text in numeric controls...), something like this in the form error event could make you replace the default access message in such cases, and point the user to the relevant control:

[tt]Private Sub Form_Error(DataErr as Integer, Response as Integer)
dim ctl as control
dim strControl as string
if dataerr=3314 then
set ctl = screen.activecontrol
strControl = ctl.name
msgbox "you need to fill in " & ctl.name & "!"
set ctl=nothing
end if
end sub[/tt]

(sometimes this will bomb (activecontrol), because the user has clicked somewhere else, then using some testing and the .previouscontrol property might work)

This isn't exactly validation, but more hove to avoid Access default messages and use custom messages (healing symptoms, not the disease;-)).

But perhaps a more dynamic way. The above would give one message per control. So if the user has filled in two controls, and there are ten requied controls, (s)he will first be prompted to fill in the "third control", after that, a new prompt for the "fourth control"...

Looping thru the form controls could give means of a more dynamic approach (see faq702-5010 for some samples of form controls looping). One of the issues, would be how to notify a "general" sub or function about which controls to check?

One of the methods, could be to use the .Tag property of the controls, to give the routine some information. Now, the next item is what to display when some control doens't mathc the validation rules? I always use a naming convention on fields and controls that wouldn't mean much for users, so one would need some means of providing meaningfull messages, I'll later discuss how to collect the caption of attached labels but here place the name of the control as the user perceives it. Then perhaps also information whether this control should be validated as numeric or text, and for numerics, perhaps if 0 is allowed...

So for a control I've named: cboClass, some relevant information could be:
Name: "Select Class"
Datatype: Numeric
Allow 0: False

A control named: txtFName
Name: "First Name"
Datatype: Text
Allow 0: (don't put any information here)

I'd input this in the controls .Tag property:
Select Class;Numeric;False - for cboClass
First Name;Text - for txtFName

Then in a relevant event, for instance the before update of the form, the save button..., call a validation sub (or function) passing the form:

[tt]dim strMsg as String
strMsg = rvsValidateForm(Me)
if len(strMsg)>0 then
' give the user the message box, allow for different
' atcions, edit the record, delete/undo...
endif[/tt]

Such function might perhaps have some similariteis with this (note - haven't tested this much, and would probably need some amending...):

Public Function rvsValidateForm(frm As Form) As String
Dim ctl As Control
Dim strProperties() As String
Dim strMsgTmp As String
Dim bolZero As Boolean
For Each ctl In frm.Controls
  bolZero = True
  Select Case ctl.ControlType
    Case acComboBox, acTextBox
      ' only perform validation on selected controltypes
      If Len(ctl.Tag & vbNullString) > 0 Then
        ' check for contents in the .Tag property
        strProperties = Split(ctl.Tag, ";")
        ' fetch the properties into an array
        If UBound(strProperties) > 0 Then
          ' don't do anything unless there are at
          ' least two arguments passed in the
          ' .Tag property
          If strProperties(1) = "Text" Then
            If Len(ctl.Value & vbNullString) = 0 Then
              strMsgTmp = strMsgTmp & vbTab & strProperties(0) & _
                  " can't be Null, enter a value!" & vbNewLine
            End If
          End If
          If strProperties(1) = "Date" Then
            If Len(ctl.Value & vbNullString) > 0 Then
              ' testing for null
              If Not IsDate(ctl.Value) Then
                ' testing for valid date
                strMsgTmp = strMsgTmp & vbTab & strProperties(0) & _
                    " contains a non valid date, please amend!" & vbNewLine
              End If
            Else
              strMsgTmp = strMsgTmp & vbTab & strProperties(0) & _
                  " can't be Null, enter a value!" & vbNewLine
            End If
          End If
          If strProperties(1) = "Numeric" Then
             ' here allowing for no "True/False" tag
             ' setting allow zero to false if it's
             ' missing
             If UBound(strProperties) > 1 Then
               bolZero = (strProperties(2) = "True")
             Else
               bolZero = False
             End If
             If Len(ctl.Value & vbNullString) > 0 Then
               ' testing for null
               If IsNumeric(ctl.Value) Then
                 ' testing for numeric
                 ' note - if it isn't, and the control is
                 ' bound to a table field, it would
                 ' proably invoke a form error first.
                 If Not bolZero And ctl.Value = 0 Then
                   strMsgTmp = strMsgTmp & vbTab & strProperties(0) & _
                       " can't be 0, enter a value!" & vbNewLine
                 End If
               Else
                 strMsgTmp = strMsgTmp & vbTab & strProperties(0) & _
                     " must be numeric!" & vbNewLine
               End If
             Else
               strMsgTmp = strMsgTmp & vbTab & strProperties(0) & _
                  " can't be Null, enter a value!" & vbNewLine
             End If
          End If
        Else
          strMsgTmp = strMsgTmp & vbTab & strProperties(0) & _
              " misses at least one arguement in the .Tag property!" & vbNewLine
        End If
      End If
  End Select
Next ctl
Set ctl = Nothing
rvsValidateForm = strMsgTmp
End Function

Note - for this to work, none of the fields the controls being validated are bound to can have their required property set to No, and if a control bound to a numeric field has letters/characters in it, it will give the standard Access message (which can be altered thru the form error event). And if there are validation rules, what TheAceMan1 mentiones might happen (not able to leave the control)

To use the caption of the attached labels, you could alter the message box lines to something like this:

[tt]' at the top
dim ctl2 as control
dim strTmp as string

' in stead of the message box lines above, or perhaps
' use the lines until the linebreak somewhere at the
' top of the validation code (after the line testing
' the upper bound of the strProperties?)
if ctl.controls.count>0 then
strTmp=vbNullstring
for each ctl2 in ctl.controls
if ctl2.controltype = acLabel then
strTmp = ctl2.Caption
exit for
end if
next ctl2
end if
if len(strMsg)=0 then
strTmp = strProperties(0)
end if

strMsgTmp = strMsgTmp & vbTab & strTmp & _
"<and the appropriate 'rest of text>'" & vbNewLine[/tt]

- if the control has an attached label, the caption from the label is used, if not, the first value from the .Tag property.

Else I sometimes use the KeyDown event of the control to only allow numerics:

[tt]Private Sub txtSomeControl_KeyDown(KeyCode As Integer, Shift As Integer)
Dim bAlt As Boolean
bAlt = (Shift And acAltMask) <> 0
If (Not (bAlt And (KeyCode = vbKeyL Or KeyCode = vbKeyH))) Then
Select Case KeyCode
Case vbKeyNumpad0, vbKeyNumpad1, vbKeyNumpad2, vbKeyNumpad3, _
vbKeyNumpad4, vbKeyNumpad5, vbKeyNumpad6, vbKeyNumpad7, _
vbKeyNumpad8, vbKeyNumpad9, _
vbKey0, vbKey1, vbKey2, vbKey3, vbKey4, vbKey5, _
vbKey6, vbKey7, vbKey8, vbKey9, _
vbKeyEscape, vbKeyTab, vbKeyReturn, vbKeyBack, _
vbKeyDelete, vbKeyLeft, vbKeyRight, vbKeyUp, vbKeyDown
Case Else
KeyCode = 0
End Select
End If
End Sub[/tt]

This should enable only numerics, but also delete, backspace, arrow keys, escape. It is also allowing Alt + L and Alt + H, which are functions I need to be allowed.

Roy-Vidar

 

[ZmrAbdulla]

Hi Roy again,
It is time to leave now. I have read your post once. I should take it into my heart!!
When I started learning VBA I tried to skip those anonymous language like "strMsgTmp" "VbNewLine" ect... It was hard for me. Then a day came that forced me to learn that beautiful language. I started dreaming in that language..
As I said I was not systematic on Access learned and skiped a lot.
The huge notes and code that you have written gives me a lot of information. I never think of user entering text on numerical field. So it is also important to force user to enter appropriate value to there.
There is one more doubt
How do you validate two date fields in a form not to be equal value or the first one must be smaller etc...

There are chances of entering text,null etc..

Thanks for now & a Star




Zameer Abdulla

http://www.geocities.com/zameerabd/

 

[TheAceMan1]

Roy! . . . . . .

Nice piece of work! . . . . worth a pinky . . . . .

See Ya! . . . . . .

 

[willir]

Roy...

There are some "coders" that participate in Tek-Tip that leave me in their dust.

Thanks for sharing...
Richard

 

[RoyVidar]

Thank you all!
Some of the above is something I'm working on currently, trying the same approach Willir mentioned, keep it simple, make it readable and sentralize/generalize if possible.

Willir - thank you, but I don't think I'm one of them, and much of this is picked up here, and just put in another context...

ZmrAbdulla:
Validation is a huge topic, and there's no "This Is The Ccorrect Way Of Validation". We've only touched some of the alternatives here, hopefully putting some stuff together that might benefit. There are lot's of valid approaches, where the important part is that the requirements should guide you to which method of validation to choose for a particular application, form, control... (also - if the controls are bound or not, and what properties have which values set, could implicate on how to validate). Well, preferences, is of course also one of the "guides";-)

The important part, again, is that the validation you use should comply with the requirements of what you're working on (form/control...) AND not be unlogic or pose unnecessary restrains to/on the user. For instance, if no message, label or help is provided for the above keydown event, how would the user deal with the control not accepting other than numerics?

Two dates, where the second date needs to be greater than the first. I think I'd start with testing for Null. The below sample should take care of (or more correct give a message box on) some of the things one might run into with dates in unbound controls without formatting.

[tt]If (Len(Me("txtStart").Value & vbNullString) > 0) Then
If (Len(Me("txtEnd").Value & vbNullString) > 0) Then
If (IsDate(Me("txtStart").Value) And IsDate(Me("txtEnd").Value)) Then
If Not (CDate(Me("txtStart").Value) < CDate(Me("txtEnd").Value)) Then
MsgBox "End is not greater than start"
End If
Else
MsgBox "Either start or end contains an invalid date"
End If
Else
MsgBox "End can't be null"
End If
Else
If (Len(Me("txtEnd").Value & vbNullString) > 0) Then
MsgBox "Start can't be null"
Else
MsgBox "Start and End can't be null"
End If
End If[/tt]

IsDate - to test whether the contents can be evaluated as a date
CDate - convert to date before processing

Does one need all of this? Depending on requirements, perhaps sometimes...

In lot of cases, just using IsNull and compare the dates would suffice.

And if the control is formatted as date, you'll get the default access message "The value you entered..." when leaving the control (or trap/handle it thru DataErr 2113) which would ensure a valid date, so that wouldn't be necessary to test...

If they're bound to fields with the required property set to yes, then "The field 'blah blah' cannot contain a Null..." should occur when trying to save the record (trap/handle thru DataErr 3314), but doing some validation in the forms before update, could handle that...

So - again - lots of ways of validating;-)

Roy-Vidar

 

[ZmrAbdulla]

Hi Roy, thanks for that again

I was so thirsty on downloading databases, samples and programs to see the interface, design, code etc…etc… Out of hundred a few have a good interface and falseproof data entry system I have found. I like it in that way.
Also I hate the msg boxes that comes one by one like
“Enter name”
Then
“Enter telephone.”
Then
“Enter address”
Then
“Blah…”
Then…

Or the one supplied by Access itself that common user can’t understand.
I like to have a common message box to say the things very simple &
The code that validates data must be false proof.
Simple msgbox like

“Enter Name & Telephone”
Or
“Enter address & Blah…”
I have to go a lot to learn these entire things. I think the real fruits of the user group comes now. When you get an Instructor who can really teach you…

Thanks & Bye for now


Zameer Abdulla

http://www.geocities.com/zameerabd/