Required fields in Access 2010 - VBA

K

knarlyd@hotmail.com

Registered User.
Local time
Today, 08:00
Joined
Sep 6, 2013
Messages
43
I found some code to check if all the fields in a form are complete, and if not, highlight and not allow Save until all fields are complete.

The issue I'm having is with another piece of code from Allen Browne (ajbFindAsUType) that finds records as you type.
The code that checks for the blank fields also highlights the unbound control on that function, which is where users enter a value to search upon.
Can someone explain (if possible) how to not check that field for a value?
Any help would be greatly appreciated as I know very little about VBA.

I'd post the database but it has confidential information. :(

Here's the code for validating the form:

Private Function ValidateFormComplete() As Boolean
Dim Ctr As Control
Dim FoundError As Boolean
Stop
FoundError = False
For Each Ctr In Me.Controls
Select Case Ctr.ControlType
Case acComboBox, acTextBox, acCheckBox
If IsNull(Ctr.value) Or Ctr.value = "" Then
Ctr.BackColor = RGB(223, 167, 165)
FoundError = True
Else
Ctr.BackColor = RGB(255, 255, 255)
End If
End Select
Next Ctr
If Not FoundError Then
ValidateFormComplete = True
Else
ValidateFormComplete = False
End If
End Function
 
You can put something in the Tag property of desired controls and check that in your code, or check the name of the control and skip that one.
 
The For Each ctl loop goes through every control on the page. If you want to exclude one, simply add the name to your if statement within the case statement.

Code: 
Private Function ValidateFormComplete() As Boolean
Dim Ctr As Control
Dim FoundError As Boolean
Stop
FoundError = False
For Each Ctr In Me.Controls
    Select Case Ctr.ControlType
        Case acComboBox, acTextBox, acCheckBox
            [COLOR="Red"]If Ctr.Name <> "MySearchControl" AND (IsNull(Ctr.value) Or Ctr.value = "") Then[/COLOR]
                Ctr.BackColor = RGB(223, 167, 165)
                FoundError = True
            Else
                Ctr.BackColor = RGB(255, 255, 255)
            End If
    End Select
Next Ctr
If Not FoundError Then
    ValidateFormComplete = True
Else
    ValidateFormComplete = False
End If
End Function
 
Try this out. Just pass the name of the control as the optional parameter when you call the function. Alternatively, you could use the tag property of the control to tell the function to ignore it. In that case, there are no parameters needed at all, but it's up to you which you think is better.

So if you have two controls that should not be validated called ctrl1 and ctrl2, call the function like this:

Call ValidateFormComplete("ctrl1;ctrl2")

Code: 
Private Function ValidateFormComplete(Optional csvIgnoredControls As String) As Boolean
    
    Dim Ctr As Control
    Dim FoundError As Boolean
    
    For Each Ctr In Me.Controls
        If InStr(1, csvIgnoredControls, Ctr.Name) = 0 Then
            Select Case Ctr.ControlType
                Case acComboBox, acTextBox, acCheckBox
                    If IsNull(Ctr.Value) Or Ctr.Value = "" Then
                        Ctr.BackColor = RGB(223, 167, 165)
                        FoundError = True
                    Else
                        Ctr.BackColor = RGB(255, 255, 255)
                    End If
            End Select
        End If
    Next Ctr
    
    If Not FoundError Then
        ValidateFormComplete = True
    Else
        ValidateFormComplete = False
    End If
    
End Function
 
Can someone explain (if possible) how to not check that field for a value?
Click to expand...

The test to omit an unbound control is that it would have a blank .ControlSource property, so you could use

if not ( Nz([control].ControlSource,"") = "" ) Then .... test the control

And of course, if it has no control source, skip it.

NOTE, however, that your FOR loop will catch ALL controls on the form. If you have ANY unbindable controls - like labels or lines, the control won't even have that property, so you might need to put an error trapper around the whole thing to prevent problems. If you have any lines at all, this code would also have another problem since a line doesn't have a .BackColor either - it only has a .BorderColor - so that code that changes back-colors of the control might fail in that case.

In this snippet, supply variables as needed for it to make sense to you.

Code: 
On Error Resume Next

For Each ctlX in Me.Controls
sCSrc = ""
sCSrc = ctlX.ControlSource
if sCSrc <> "" Then
'    this is a bound control
End If
Next ctlX

The above works because the On Error Resume Next essentially tells Access error handling to shut up. The sCSrc = "" pre-sets the string to empty. If the attempt to look up the control source property fails, that assignment statement is NOT executed, so the sCSrc string stays empty, which you can then test. But if the property exists and was not empty, you will see a source and can process it. Finally, if the property exists but is dynamically assigned and hasn't been assigned yet, you will ALSO skip processing since a probe of an empty property returns an empty string.

Outside this code, you should probably have another error trap handler in force since unhandled traps are a good way to kill an application.
 
if you are going to use pbaldy's suggestion using Tag, while on form's design view click on the unbound control. on it's property sheet->other->tag, put a the value "Unbound". then your code would be like this:
Code: 
Private Function ValidateFormComplete() As Boolean
    Dim Ctr As Control
    Dim FoundError As Boolean
    'Stop
    FoundError = False
    For Each Ctr In Me.Controls
        Select Case Ctr.ControlType
            Case acComboBox, acTextBox, acCheckBox
                If Ctr.tag & "" <> "Unbound" Then
                    If IsNull(Ctr.Value) Or Ctr.Value = "" Then
                        Ctr.BackColor = RGB(223, 167, 165)
                        FoundError = True
                    Else
                        Ctr.BackColor = RGB(255, 255, 255)
                    End If
                End If
        End Select
    Next Ctr
    If Not FoundError Then
        ValidateFormComplete = True
    Else
        ValidateFormComplete = False
    End If
End Function
 
you cannot rely always on control.controlsource to be blank, since an unbound textbox can have a controlsource of an expression, ie "=[fieldname]", or =function().
 
arnelgp said:
if you are going to use pbaldy's suggestion using Tag, while on form's design view click on the unbound control. on it's property sheet->other->tag, put a the value "Unbound". then your code would be like this:
Code: 
Private Function ValidateFormComplete() As Boolean
    Dim Ctr As Control
    Dim FoundError As Boolean
    FoundError = False
    For Each Ctr In Me.Controls
        Select Case Ctr.ControlType
            Case acComboBox, acTextBox, acCheckBox
                If Ctr.tag & "" <> "Unbound" Then
                    If IsNull(Ctr.Value) Or Ctr.Value = "" Then
                        Ctr.BackColor = RGB(223, 167, 165)
                        FoundError = True
                    Else
                        Ctr.BackColor = RGB(255, 255, 255)
                    End If
                End If
        End Select
    Next Ctr
    If Not FoundError Then
        ValidateFormComplete = True
    Else
        ValidateFormComplete = False
    End If
End Function
Click to expand...

It looks like it's close to working with what you posted as well as using pbaldy's tag property suggestion.
It now appears to skip that control but still says the form is not complete?
Here's the other part of code for the form validation which I forgot to post(doh!):

Private Sub btnSaveRecord_Click()

If ValidateFormComplete Then
'Save Record Code Here
Me.btnAddNewRecord.Enabled = True
Else
MsgBox "Form not complete. Please complete all highlighted fields."
End If
 
here's a modified one:

Code: 
Private Function ValidateFormComplete() As Boolean
    Dim Ctr As Control
    Dim FoundError As Boolean
    'Stop
    FoundError = False
    For Each Ctr In Me.Controls
        Select Case Ctr.ControlType
            Case acComboBox, acTextBox, acCheckBox
                If Ctr.tag & "" <> "Unbound" Then
                    If Trim(Ctr.Value & "") = "" Then
                        Ctr.BackColor = RGB(223, 167, 165)
                        FoundError = True
                    Else
                        Ctr.BackColor = RGB(255, 255, 255)
                    End If
                End If
        End Select
    Next Ctr
    ValidateFormComplete = Not (FoundError)
End Function
 
arnelgp said:
here's a modified one:

Code: 
Private Function ValidateFormComplete() As Boolean
    Dim Ctr As Control
    Dim FoundError As Boolean
    'Stop
    FoundError = False
    For Each Ctr In Me.Controls
        Select Case Ctr.ControlType
            Case acComboBox, acTextBox, acCheckBox
                If Ctr.tag & "" <> "Unbound" Then
                    If Trim(Ctr.Value & "") = "" Then
                        Ctr.BackColor = RGB(223, 167, 165)
                        FoundError = True
                    Else
                        Ctr.BackColor = RGB(255, 255, 255)
                    End If
                End If
        End Select
    Next Ctr
    ValidateFormComplete = Not (FoundError)
End Function
Click to expand...

Unfortunately it still acts the same :(
 
Did you try this?

TJPoorman said:
The For Each ctl loop goes through every control on the page. If you want to exclude one, simply add the name to your if statement within the case statement.

Code: 
Private Function ValidateFormComplete() As Boolean
Dim Ctr As Control
Dim FoundError As Boolean
Stop
FoundError = False
For Each Ctr In Me.Controls
    Select Case Ctr.ControlType
        Case acComboBox, acTextBox, acCheckBox
            [COLOR="Red"]If Ctr.Name <> "MySearchControl" AND (IsNull(Ctr.value) Or Ctr.value = "") Then[/COLOR]
                Ctr.BackColor = RGB(223, 167, 165)
                FoundError = True
            Else
                Ctr.BackColor = RGB(255, 255, 255)
            End If
    End Select
Next Ctr
If Not FoundError Then
    ValidateFormComplete = True
Else
    ValidateFormComplete = False
End If
End Function
Click to expand...
 
Did you change "MySearchContro" to the name of the control you want to skip. :p
 
try putting breakpoint to the function, step through the code (f8), inspect the control name where the FoundError value becomes true.
 
arnelgp said:
try putting breakpoint to the function, step through the code (f8), inspect the control name where the FoundError value becomes true.
Click to expand...

Okay, so it appears that "Label3" is the issue. See attached picture with a red box around label 3. (that is if I know how to make an attachment here!)

From there it exits and then returns to the rest of his code to the section that next displays: "Form not complete. Please complete all highlighted fields."
The search field is not highlighted when it arrives at that junction of his code.
Hope this makes sense...
Thanks again for your help thusfar.:)

Code: 
Private Sub btnSaveRecord_Click()
     Stop
      If ValidateFormComplete Then
        'Save Record Code Here
        Me.btnAddNewRecord.Enabled = True
    Else
        MsgBox "Form not complete.  Please complete all highlighted fields."
    End If
 

Attachments

  • Allen.gif
    Allen.gif
    3.2 KB · Views: 76
again please try:
Code: 
Private Function ValidateFormComplete() As Boolean
    
    Dim Ctr As Control
    Dim FoundError As Boolean
    
    For Each Ctr In Me.Controls
        If TypeOf Ctr Is Access.CheckBox Or _
            TypeOf Ctr Is Access.ComboBox Or _
            TypeOf Ctr Is Access.TextBox Then
            If Ctr.tag & "" <> "Unbound" Then
                If Trim(Ctr.Value & "") = "" Then
                    Ctr.BackColor = RGB(223, 167, 165)
                    FoundError = True
                Else
                    Ctr.BackColor = RGB(255, 255, 255)
                End If
            End If
        End If
    Next Ctr
    
    If Not FoundError Then
        ValidateFormComplete = True
    Else
        ValidateFormComplete = False
    End If
    
End Function
 
arnelgp said:
again please try:
Code: 
Private Function ValidateFormComplete() As Boolean
    
    Dim Ctr As Control
    Dim FoundError As Boolean
    
    For Each Ctr In Me.Controls
        If TypeOf Ctr Is Access.CheckBox Or _
            TypeOf Ctr Is Access.ComboBox Or _
            TypeOf Ctr Is Access.TextBox Then
            If Ctr.tag & "" <> "Unbound" Then
                If Trim(Ctr.Value & "") = "" Then
                    Ctr.BackColor = RGB(223, 167, 165)
                    FoundError = True
                Else
                    Ctr.BackColor = RGB(255, 255, 255)
                End If
            End If
        End If
    Next Ctr
    
    If Not FoundError Then
        ValidateFormComplete = True
    Else
        ValidateFormComplete = False
    End If
    
End Function
Click to expand...

ARG! no change...:(
 
You must log in or register to reply here.

Similar threads

V
Need help with VBA to check for required fields
Replies
6
Views
617
MajP
MajP
jwcolby54
Building a table for my book
Replies
15
Views
429
jwcolby54
jwcolby54
U
Export blob OLE Object Field Item instead of Attachment Field
Replies
9
Views
786
jdraw
jdraw
J
Code to change Date in a field if the checkbox is checked
Replies
5
Views
412
LarryE
LarryE
jwcolby54
OpenArgs (my) class way
3 4 5
Replies
89
Views
2,982
Josef P.
J

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom