At least one of those 3 fields not null?!

[wendigo]

Hello!

Don't know if theres's a awswer for this in the forum.
I've searched the forum and google for the solution with no luck.
I've a form whith severel text fields on it.

3 of those are important because theres's a query related to them.

So what I wanted is that at least one of those 3 fields (anyone) have text before the record and if not display a message box.

I'm not very good and VB coding, so any help would be appreciated.

Sorry for the bad english and thanks in advanced.

 

[Uncle Gizmo]

How do you run the query?

 

[wendigo]

Hi Uncle Gizmo, thanks for the fast reply!

It's not a query, what a wanted is to check if one of the 3 fields have text.
If none have text than a msg will show and no record saved, but if at least one field is filled than record can be saved.

I've allready tryed

If IsNull(text1) And IsNull(text2) And IsNull(text3) Then

but with this code I've to fill at least 2 fields and I wanted only one.

Thanks

 

[Uncle Gizmo]

Well this is the basic code/idea:-

Private Function fX()
Dim Ctrl As Control
Dim intCounter As Integer

      For Each Ctrl In Me.Controls
        If Ctrl.ControlType = acTextBox Then
            If IsNull(Ctrl.Value) Then
                'Count how many are Null
                intCounter = intCounter + 1
            End If
         End If
      Next

MsgBox " >>> " & intCounter & " text boxes have a NULL Value"

'If none checked then check me (The one passed through in the function)


End Function

Sample DB on my Website HERE:-
http://www.niftyaccess.com/loop-through-a-set-of-controls/

 

[ypma]

If i understand you correctly if all three text boxes are blank then a message will appear so your code seems ok. Using the "And " ensures all three fields have to be blank before sending the message .If criteria not met record saved .

#If IsNull(Me.Text19) Or (Me.Text19) = "" And IsNull(Me.Text22) Or (Me.Text22) = "" And IsNull(Me.Text24) Or (Me.Text24) = "" Then
MsgBox " all three fields are blank "
DoCmd.GoToControl "[Client ID] " assuming you don't wish to leave the record place cursor on the field of your choice .
else
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If#

Note when you closes the record access saves it . If I have misunderstood the question please forgive me.

Regards Ypma

 

[wendigo]

Thanks for the replys.

Ypma solution still oblige me to fill at least 2 of the 3 fields!!
I believe the code is right, don't understand why is still need the 2 fields instead of only 1.

Uncle Gizmo I understand how your code work but I'm to "access dummy" to arrange it to my needs. Although I believe your code just checks what fields are null and what I need is to save a record if only one is filled.

I can easily make it work (with Ypma code) if I need to fill out at least one of two fields, should work the same way for the 3 fields but somehow oblige me to fill at least 2 fields...

Thought this should be a easy issue but I'm going crazy to figuring it out.

Thanks

 

[Uncle Gizmo]

There's a YouTube Video HERE:- Loop Through a Set of Controls

Next I will show how to adapt the code to a particular circumstance.

 

[Uncle Gizmo]

Well, we are nearly there!

This YouTube video At least One field Must have Text in it explains how the next block of code works:-

You can use the following code; place it in ypma's sample code above and it will prevent the user saving the record without an entry. However there is one slight problem, if you have more than 3 Text Boxes on your form it will not work because this code is designed to loop through all of the text boxes on the form. That's the next problem I will address.

Private Function fX() As Boolean
Dim Ctrl As Control
Dim intCounter As Integer

      For Each Ctrl In Me.Controls
        If Ctrl.ControlType = acTextBox Then
            If IsNull(Ctrl.Value) Then
                'Count how many are Null
                intCounter = intCounter + 1
            End If
         End If
      Next

If intCounter < 3 Then
    fX = True
End If

'fX = intCounter

End Function

Private Sub btnTEST_Click()
    MsgBox " >>> " & fX
End Sub

 

[wendigo]

Thanks Uncle Gizmo!

I've figured out he problem (well part of)
So the code of Ypma is indeed working but when I click the save record button, even if only one filed is filled the msg box allways pop up.

Then if I put some value on any other field on the form (even if is not the 3 fields requested) the record saves with no issue.

So it must be something missing on the code or the code is in the wrong place.

I've the code to run on click.

Many thanks for all the research and information posted.

 

[wendigo]

DONE!!

Just added refresh after the Ypma code and now works fine!
Don't ask me how but it's working!!!!

Again, many thanks to both users for the shared info.

 

[Uncle Gizmo]

Hi Wandigo, You might want to check out the next video and this code enhancement because if you add any other text boxes to your form then it will break!

YouTube Video:- Really COOL MS Access

And the Code Enhancement:-

Private Function fX() As Boolean
Dim Ctrl As Control
Dim intCounter As Integer
Dim intNumbOfCtrl As Integer

'Count the Number of Text Boxes with an "x" in their Tag Property
     For Each Ctrl In Me.Controls
        If Ctrl.ControlType = acTextBox Then
            If Ctrl.Tag = "x" Then
                intNumbOfCtrl = intNumbOfCtrl + 1
            End If
         End If
      Next

'Count the Number of Text Boxes with an "x" in their Tag Property which are NULL
     For Each Ctrl In Me.Controls
        If Ctrl.ControlType = acTextBox Then
            If Ctrl.Tag = "x" Then
                If IsNull(Ctrl.Value) Then
                    'Count how many are Null
                    intCounter = intCounter + 1
                End If
            End If
         End If
      Next

'Report the condition of the Text Boxes
If intCounter < intNumbOfCtrl Then
    fX = True
End If

End Function