VBA Validation

[xPaul]

All,

I have an if statement using various controls that are found on my form.

For example, I have three fields:

Date, Call Ref, Logged by.

I have button that checks those fields to see if they have been filled out or not, using the following code:

If IsNull(Me.Date) Then
lresponse = MsgBox("Please insert today's date", vbOKOnly, "Warning!")
Me.Date.SetFocus
ElseIf IsNull(Me.Call_Ref) Then
lresponse = MsgBox("Please insert a call reference number.", vbOKOnly, "Warning!")
Me.Call_Ref.SetFocus
ElseIf IsNull(Me.Logged_by) Then
lresponse = MsgBox("Please select the person who logged this call.", vbOKOnly, "Warning!")
Me.Logged_by.SetFocus
Else
lresponse = MsgBox("The Form has been successfully checked.  No errors have been found.  Please now press the 'Save Record' button to log this call and then press 'New Record' if you have another call to log.  Otherwise please exit.", vbOKOnly, "Information!")
Me.SaveRecord.Enabled = True
End if

Now this does work in theory, though say if I was to enter information into the Call Ref field, click upon the CheckForm button, it would prompt me to fill out the other two controls (Date and Logged By).

The issue starts whenever I remove the text I have just placed in the Call Ref field. It doesn't prompt me to enter anything in the Call Ref field when pressing on the Check Form button.

Originally I tried:

If Me.Date= "" then
lresponse . . . .

Which did not work.

Is there a way that I can get around this?

Best regards

 

[pwbrown]

Are Date, Call_Ref and Logged_by textboxes?

 

[xPaul]

Date and Call Ref are text boxes, yes. However Logged By is a Combo Box.

 

[pwbrown]

Well for the text boxes you should be able to do something like:

If (Len(Trim(Date.Text)) <> 0) Then
....

Just as a side note I wouldn't use "date" as a textbox name, can become confusing, I normally stick a letter before the word date.

Edit: sorry noticed you were checking if it was an error and not that it was right.
Change "<> 0" to "= 0"

 

[xPaul]

Yeah, I know. I'm having the same issue with my other forms.

It's just trying to get the time to get all the forms with their proper naming convention and to ensure it doesn't break something elsewhere.

I'll try that, and come back to you. Thank you.

 

[xPaul]

No joy. When I do that it brings up a runtime error of 2815, which is fine. I can assign the set focus property to the control and it will work fine, with no issues at all, but I have to split my code out from the elseifs.

Me.txt_date.SetFocus
If (Len(Trim(txt_date.Text)) = 0) Then
lresponse = MsgBox("Please insert today's date", vbOKOnly, "Warning!")
End If
 
Me.txt_call_ref.SetFocus
If (Len(Trim(txt_call_ref.Text)) = 0) Then
lresponse = MsgBox("Please insert a call reference number.", vbOKOnly, "Warning!")
End If

If I was to put the whole thing into a elseif statement format, the run time error starts on the txt_call_ref field as I cant set the focus unless the condition is true:

Me.txt_date.SetFocus
If (Len(Trim(txt_date.Text)) = 0) Then
lresponse = MsgBox("Please insert today's date", vbOKOnly, "Warning!")
Me.txt_call_ref.SetFocus
ElseIf (Len(Trim(txt_call_ref.Text)) = 0) Then
lresponse = MsgBox("Please insert a call reference number.", vbOKOnly, "Warning!")
End If

Though, I definitely do need the elseif statement due to having an else at the end:

Else
lresponse = MsgBox("The Form has been successfully checked.  No errors have been found.  Please now press the 'Save Record' button to log this call and then press 'New Record' if you have another call to log.  Otherwise please exit.", vbOKOnly, "Information!")
Me.btn_savercd.Enabled = True

Totally leaves me running in circles.

 

[xPaul]

Okay,

Had to bodge it a bit:

Me.txt_date.SetFocus
If (Len(Trim(txt_date.Text)) = 0) Then
lresponse = MsgBox("Please insert today's date", vbOKOnly, "Warning!")
Else
Me.txt_call_ref.SetFocus
If (Len(Trim(txt_call_ref.Text)) = 0) Then
lresponse = MsgBox("Please insert a call reference number.", vbOKOnly, "Warning!")
Else
lresponse = MsgBox("The Form has been successfully checked.  No errors have been found.  Please now press the 'Save Record' button to log this call and then press 'New Record' if you have another call to log.  Otherwise please exit.", vbOKOnly, "Information!")
End If
End If

Will see how this goes . . .

 

[pwbrown]

Try this:

If (Len(Trim(txt_date.Text)) = 0) Then
   lresponse = MsgBox("Please insert today's date", vbOKOnly, "Warning!")
ElseIf (Len(Trim(txt_call_ref.Text)) = 0) Then
   lresponse = MsgBox("Please insert a call reference number.", vbOKOnly, "Warning!")
Else
   lresponse = MsgBox("The Form has been successfully checked.  No errors have been found.  Please now press the 'Save Record' button to log this call and then press 'New Record' if you have another call to log.  Otherwise please exit.", vbOKOnly, "Information!")
   Me.btn_savercd.Enabled = True
End If

 

[pr2-eugin]

Paul, what you need is Three individual IF with an Exit Sub statement, not ElseIf.. As the basic ElseIf works as..

If [COLOR=Blue]<logic1>[/COLOR] True Then
    [COLOR=Green]'do something[/COLOR]
ElseIf [COLOR=Red]<logic2> [/COLOR]True Then ' i.e. if Logic 1 Fails check for another logic.
    'do something
Else 'i.e. If [COLOR=SandyBrown] [B]ALL <logic>[/B][/COLOR] is False Then
  [COLOR=Green] 'do something else[/COLOR]

So basically between Logic 1 and N any statement is True all others are ignored including the final Else.. So try this..

If Len(Me.[Date] & vbNullString) = 0 Then
    Call MsgBox("Please insert today's date", vbOKOnly, "Warning!")
    Me.Date.SetFocus
    Exit Sub
End If
    
If Len(Me.Call_Ref & vbNullString) = 0 Then
    Call MsgBox("Please insert a call reference number.", vbOKOnly, "Warning!")
    Me.Call_Ref.SetFocus
    Exit Sub
End If

If Len(Me.Logged_by & vbNullString) = 0 Then
    Call MsgBox("Please select the person who logged this call.", vbOKOnly, "Warning!")
    Me.Logged_by.SetFocus
    Exit Sub
End If

Call MsgBox("The Form has been successfully checked.  No errors have been found.  Please now press the 'Save Record' button to log this call and then press 'New Record' if you have another call to log.  Otherwise please exit.", vbOKOnly, "Information!")
Me.SaveRecord.Enabled = True

 

[pr2-eugin]

Just a small thing, pwbrown and xPaul.. The usage of .Text property of the control is not required. As using Me.<control name> will suffice, on top of that when you use .Text it requires the control to have focus..

If you just want a generic message, you can use all checks in one line..

If ((Len(Me.[Date] & vbNullString) = 0) And (Len(Me.Call_Ref & vbNullString) = 0) And (Len(Me.Logged_by & vbNullString) = 0)) Then
    Call MsgBox("Please enter 'ALL' required information !", vbOKOnly, "Warning!")
Else
    Call MsgBox("The Form has been successfully checked.  No errors have been found.  Please now press the 'Save Record' button to log this call and then press 'New Record' if you have another call to log.  Otherwise please exit.", vbOKOnly, "Information!")
    Me.SaveRecord.Enabled = True
End If

 

[xPaul]

So many ways of doing the same things. Thanks all.

Will go and try this out.

 

[pwbrown]

Paul is right, my god, how did I not see that. I wasn't really paying attention to the logic just the method to check the textbox.
I have always used .Text, don't like using Me., I don't remember using setfocus though.
To be fair I haven't really used text boxes in access much. Been near 3 years since I last used a lot of text boxes and that was vba in visual studio although it should be the same!

Been mainly using java and c# since.

 

[xPaul]

Okay,

Strangely enough, the same thing is happening but in reverse. The text boxes are fine when you put something in them, delete it and then check the form.

When you do the same (select something and then delete it) with the combo boxes, the code doesn't check it, and instead the access table validation takes over instead. 'You must enter a value in the 'tbl . . . . . field'.

 

[pwbrown]

I think Paul Eugin thought all three were text boxes.
Use the method you had before for the combobox, IsNull.

 

[pr2-eugin]

I think Paul Eugin thought all three were text boxes.

Yes I did.. Sorry did not see that.. okay.. for the ComboBox try this..

If Me.Logged_by[COLOR=Red][B].ListIndex = -1[/B][/COLOR] Then Then
    Call MsgBox("Please select the person who logged this call.", vbOKOnly, "Warning!")
    Me.Logged_by.SetFocus
    Exit Sub
End If

 

[xPaul]

Nope.

I'm assuming that Access is doing something at the table level of the form. The reason why I say this is that, whenever you select something from the combo box, delete the selection, and try to select another control, that's when the error handling from the table field comes into play.

Will have a little play around.

I suppose if I have to create my form again, all the better as I'll be able to ensure the naming convention.

 

[pr2-eugin]

Okay, see if any event is associated with the Combo, BeforUpdate, AfterUpdate.. Also check the Control Source type and the Rowsource, Bound column.. Something will sprout up..

 

[xPaul]

No event is associated with the combo box at all.

Control Source: Logged By
Row Source: It is selecting the correct table (the properties for all fields here are set to Required: Yes) - which I believe is causing the issue.
Bound colum: 1
Limit to List: Yes
Allow Value List Edits: No
List items Edit Form:
Inherit Value List: Yes
Show Only Row Source Values: No
Input Mask:
Default Value:
Validation Rule:
Validation Text:
Enables: Yes
Locked: No
Auto Expand: Yes
Smart Tags:

 

[xPaul]

Okay, if I remove the property 'Required: Yes' on the table design, it clears the error. However, I am left with mutliple erroraneous records.

Say, you only select both controls on the form (even without saving), and then go to the switch board, you'll have a record in the BE table that shouldn't be there.