Help with VBA Validation & Conditional Formatting (1 Viewer)

[torz]

Hi guys,

I am very new to VBA, I did quite a bit back in the day (20 odd years ago) and moved into a different field of work and just can't really remember any of it now that I need it again

I have been searching this forum and done a whole bunch of googling trying to find an answer or sample that worked for me before posting but have been unsuccessful (This may be because of the database design itself, I'm sure there is a much better way to design it but I'm horrible with relationships as well)

Anyyyyyyyyways, what I'm trying to do is...

I have a form that is set for Data Entry only, it has a whole bunch of text boxes and combo boxes, some of which lookup their values from a table I have called tblLookupValues & tblAgentLookup + a couple of others (These tables have static values in them and are highly unlikely to change). Everything on the form feeds into a table called tblFLSFeedback, now the form itself works great no complaints with how everything is working at the moment except for validation & conditional formatting.

If someone is able to help with 1 or 2 examples I should be able to work out the rest of what I need from any code samples that work for me

So the form name is frmFLSFeedback
First example I need help with is a combo box which is called cboCallValidation (This looks up its values from the tblLookupVallues and on submission stores everything in the tblFLSFeedback) there is 4 options in this combo box and which are Normal FLS Call, Abandoned Call, Customer Call & Communicator. Based on the selection I need to make a number of text / combo boxes / list boxes a mandatory field and would like it to shade the background of the field red until it has focus if it loses focus I'd like it to go back to red but if it is filled out i'd like it to go green.

So an example would be if they select Normal FLS Call or Communicator in the first box the next box cboName needs to be made mandatory & red and change to green once someone has been selected, if they were to select Abandoned Call or Customer Call the cboName field can't be mandatory (and I have already set it up to hide the field in this case)

If someone is able to give me a sample of how to do that I would be extremely grateful and I should be able to work out everything else from there myself, if not I'm sure you'll hear from me again

Thank you everyone in advance!!!! I have been :banghead::banghead::banghead: for the last week or two trying all these different examples I've found but none have seemed to work....

I hope I have given you guys enough info to figure it out, seems to be a lot more detailed to the majority of posts I have seen over the last couple of weeks.

Last thing, it will need to work with Access 2010 - 2013, I will be making sure everyone has upgraded before they Access it ....

 

[burrina]

Try this Using both the OnCurrentEvent and AfterUpdateEvent.

If Not IsNull(Me.cboCallValidation) Then
Me.cboCallValidation.BackColor = vbGreen
Else
Me.cboCallValidation.BackColor = vbRed
End If

OnGotFocusEvent;

Me.cboCallValidation.BackColor = vbWhite

OnLostFocusEvent;

Me.cboCallValidation.BackColor = vbRed

Also set under your Data tab, Limit to List (YES)
HTH

 

[torz]

thanks for the quick reply burrina...

Maybe I made it a bit to complicated when I was trying to explain it

to simplify it, there are 2 combo boxes first called cboCallValidation (limited to list with 4 options Normal FLS Call, Abandonded Call, Customer Call, Communicator) this field is already mandatory on the table level (Required value)

So based on the selection of the 1st combo box there are 2 options:

1. Normal FLS Call or Communicator - cboName needs to be made mandatory before the form can be submitted (If they try it needs the popup box to tell them what field they have missed) and I'd like it to be highlighted in red as soon as they select normal fls call or communicator, once they select someone from the cboName box it should change to green.

2. if they select Abandoned Call or Customer Call - cboName can NOT be mandatory (I have already set this up to be hidden on the selection of these options.


Also, just want to add one other request if possible, is there an easy way to clear all fields on a form if a combo box changes (cboCallValidation value changes I'd like it to clear everything as if it was a new form at the moment I just have me.fieldname = "" for every field, just wondering if there was a better way)


Thanks guys!!

 

[torz]

P.s. I've managed to get the code you had working for the call validation field with a little bit of modification but just unsure how to make the 2nd field mandatory and have this apply to the 2nd field

would i be correct in guessing it would be ElseIf statements on the after update of the call validation field to make the conditional formatting work for all the other fields??

If so its just how to make certain fields mandatory based on other fields selections & if there is an easier way to clear all the fields if the call validation changes (other than what I mentioned before)


Thanks again guys!!

 

[burrina]

I don't know what you mean by the second field? But yes, you can add a ElseIf or another If statement.

FYI

Me.cboCallValidation =""

 

[torz]

field 1 is cboCallValidation
field 2 is cboName

Private Sub cboCallValidation_AfterUpdate()
	If Me.cboCallValidation = "Normal FLS Call" OR "Communicator" Then
        Me.cboName.BackColor = cbRed
    Else
        Me.cboName.Visible = False
    End If
End Sub

does that look right? and how do I make cboName a mandatory field ONLY if Normal FLS Call or Communicator is selected in the first field?? :banghead:

 

[burrina]

You will have to modify this to suit your needs, Color Me Done!
OnCurrentEvent:

If Not IsNull(Me.[cboCallValidation]) Then 'Set Visibility Criteria.
Me.fls1.Visible = False
Else
Me.fls1.Visible = True
End If

If (Me.cboCallValidation) = "Normal FLS Call" Or (Me.cboCallValidation) = "Communicator" Then 'Set Color Criteria.
Me.cboCallValidation.BackColor = vbGreen
Me.cboCallValidation.ForeColor = vbWhite
Else
Me.cboCallValidation.BackColor = vbRed
Me.cboCallValidation.ForeColor = vbWhite
End If


If ([fls1]) = "Abandoned Call" Or ([fls1]) = "Customer Call" Then 'Set Color Criteria.
Me.fls1.BackColor = vbRed
Me.fls1.ForeColor = vbWhite
End If

AfterUpdateEvent: Same Thing.

OnGotFocusEvent: For ComboBox Number On

Me.cboCallValidation.BackColor = vbWhite
Me.cboCallValidation.ForeColor = vbBlack

cboCallValidation.SelStart = 0 'DeSelect Focus.
cboCallValidation.SelLength = 0

OnLostFocusEvent:

Me.cboCallValidation.BackColor = vbRed
Me.cboCallValidation.ForeColor = vbWhite

OnGotFocusEvent: Second ComboBox, Same Thing,Just Change Names.

Good Luck!

 

[torz]

Thanks Burrina has helped quite a bit, still a little stuck through...

I snapped up a quick example db and attached for you so you get a better idea of what I'm trying to do (Pretty sure you are already spot on, but always easier if you can actually see it )

So far I have:

Private Sub LANUserID_AfterUpdate()
    If Not IsNull(Me.[LANUserID]) Then 'Set Visibility Criteria.
        Me.CustomerAccountNumber.Visible = True
    Else
        Me.CustomerAccountNumber.Visible = False
    End If

    If (Me.CallValidation) = "Normal FLS Call" Or (Me.CallValidation) = "Communicator" Then 'Set Color Criteria.
        Me.LANUserID.BackColor = vbGreen
        Me.LANUserID.ForeColor = vbWhite
    Else
        Me.LANUserID.BackColor = vbRed
        Me.LANUserID.ForeColor = vbWhite
    End If

    If (Me.CallValidation) = "Abandoned Call" Or (Me.CallValidation) = "Customer Call" Then 'Set Color Criteria.
        Me.LANUserID.BackColor = vbRed
        Me.LANUserID.ForeColor = vbWhite
        Me.LANUserID.Visible = False
    End If
End Sub

Private Sub LANUserID_GotFocus()
    Me.LANUserID.BackColor = vbWhite
    Me.LANUserID.ForeColor = vbBlack

    LANUserID.SelStart = 0 'DeSelect Focus.
    LANUserID.SelLength = 0
End Sub

Private Sub LANUserID_LostFocus()
    If Not IsNull(Me.LANUserID) Then
        Me.LANUserID.BackColor = vbGreen
        Me.LANUserID.ForeColor = vbWhite
    Else
        Me.LANUserID.BackColor = vbRed
        Me.LANUserID.ForeColor = vbWhite
    End If
End Sub

Private Sub UndoRecord_Click()
    If Me.Dirty Then
        Me.Undo
    End If
End Sub

Private Sub Form_Current()
    If Not IsNull(Me.[CallValidation]) Then 'Set Visibility Criteria.
        Me.LANUserID.Visible = True
    Else
        Me.LANUserID.Visible = False
    End If

    If (Me.[CallValidation]) = "Normal FLS Call" Or (Me.[CallValidation]) = "Communicator" Then 'Set Color Criteria.
        Me.CallValidation.BackColor = vbGreen
        Me.CallValidation.ForeColor = vbWhite
    Else
        Me.CallValidation.BackColor = vbRed
        Me.CallValidation.ForeColor = vbWhite
    End If

    If (Me.CallValidation) = "Abandoned Call" Or (Me.CallValidation) = "Customer Call" Then 'Set Color Criteria.
        Me.LANUserID.BackColor = vbRed
        Me.LANUserID.ForeColor = vbWhite
        Me.LANUserID.Visible = False
    End If
    
End Sub

Private Sub CallValidation_AfterUpdate()
    If Not IsNull(Me.[CallValidation]) Then 'Set Visibility Criteria.
        Me.LANUserID.Visible = True
    Else
        Me.LANUserID.Visible = False
    End If

    If (Me.CallValidation) = "Normal FLS Call" Or (Me.CallValidation) = "Communicator" Then 'Set Color Criteria.
        Me.CallValidation.BackColor = vbGreen
        Me.CallValidation.ForeColor = vbWhite
    Else
        Me.CallValidation.BackColor = vbRed
        Me.CallValidation.ForeColor = vbWhite
    End If

    If (Me.CallValidation) = "Abandoned Call" Or (Me.CallValidation) = "Customer Call" Then 'Set Color Criteria.
        Me.LANUserID.BackColor = vbRed
        Me.LANUserID.ForeColor = vbWhite
    End If

End Sub

Private Sub CallValidation_GotFocus()
    Me.CallValidation.BackColor = vbWhite
    Me.CallValidation.ForeColor = vbBlack

    CallValidation.SelStart = 0 'DeSelect Focus.
    CallValidation.SelLength = 0
End Sub

Private Sub CallValidation_LostFocus()
    If Not IsNull(Me.CallValidation) Then
        Me.CallValidation.BackColor = vbGreen
        Me.CallValidation.ForeColor = vbWhite
    Else
        Me.CallValidation.BackColor = vbRed
        Me.CallValidation.ForeColor = vbWhite
    End If
    If (Me.CallValidation) = "Abandoned Call" Or (Me.CallValidation) = "Customer Call" Then
        Me.LANUserID.Visible = False
    Else
        Me.LANUserID.Visible = True
    End If
End Sub

There is a whole bunch more fields but should be okay if I can at least get this lot working....

So if CallValidation is null then it needs to be highlighted red once filled out it changes to green (Field is required at the table level)

If you select Normal FLS Call or Communicator then LANUserID needs to be highlighted red and become mandatory, once someone is selected from the LANUserID combobox it should go green and then the CustomerAccountNumber AND CustomerBillingAccount become red (At least 1 of these two fields need to be filled out) and it will continue on from there...

If you select Abandoned Call then all the fields except AbandondedCallNumber & CallID should be hidden, and both fields should become red, at least one of these fields needs to be filled out. and go green once done.

If CustomerCall is selected only LANUserID & AbandonedCallNumber become hidden and the CustomerAccountNumber & CustomerBillingAccount become red and at least 1 field needs to be filled - changes to green once filled.

The last part of it the form needs to null values if they change the callvalidation before submission just to ensure they dont select an option that would be invalid on a different validation selection, EG: select Normal FLS Call then LANUserID and then they change their mind to make it CustomerCall (A CustomerCall Should NOT have a LANUserID).



I will end up hiding ALL of the fields except the CallValidation, and they will become visible as you work your way through the form based on your selections.

Hope this all makes sense and someone is able to help...


Again,

burrina, thank you so much for everything so far, it has got me so much closer to what I'm after! So many hours wasted googling and trying code, when I obviously dont have a clue

If it is easier to just have the conditional formatting (Coloring) done and it runs a validation when you try to submit the form to ensure all required fields are filled based on your selections that would be cool too

 

[bob fitz]

IMHO the best place to run some validation code that will check if all required fields have been completed would be in the forms Before Update event which can of course be cancelled if the validation fails.

 

[torz]

Thats what I figured, but I am totally lost in how to write it, spent hours googling and trying different bits of code but I don't know enough to get it working :banghead::banghead::banghead:

 

[bob fitz]

What are the names of the controls on the form that must have data, to pass the validation.

 

[torz]

There is a whole bunch with a whole bunch of combinations....

I've uploaded an example with a couple of the fields, if anyone can get it to work for me I should be okay to work out the other fields myself

 

[bob fitz]

There is a whole bunch with a whole bunch of combinations....

So, can you tell us in plain English what they are.

 

[burrina]

Try this. Add this "(*)" to your Property Sheet/Other Tag field.

Private Sub Form_BeforeUpdate(Cancel As Integer) 'Required Data Validation.
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control


On Error GoTo Form_BeforeUpdate_Error

nl = vbNewLine & vbNewLine

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.name & "' field!" & nl & _
"You can't save this record until this data is provided!" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
Cancel = True
Exit For
End If
End If
Next

 

[torz]

THANK YOU BURRINA!

could of little bits missing out of the last code but got it working and it works great and I'm sure I'll figure out the rest of the fields with the conditional formatting easy enough.... thanks so much!

1 last thing, (hopefully the last thing)

With the last bit of code you posted is it easy enough to put a bunch of If ElseIf statements in for the validation?

Eg:

If Me.CallValidation = "Normal FLS Call" Or Me.CallValidation = "Communicator" Then it would need to run run validation on a field for cboLANUserID to ensure an option has been selected from the combo box.

Where as if Me.CallValidation = "Abandoned Call" Or Me.CallValidation = "Customer Call" Then cboLANUserID must not have a value selected in this case