Select Case using control names?

  • Thread starter Thread starter lcox400w
  • Start date Start date
L

lcox400w

I am trying to write code, but having trouble with one part. When my form
opens, I want to run some code to see if there are any missing/mandatory
fields and if so I am going ot highlight them and advise the user to complete
them. I am using select case which will list the various control source
names but its not working properly. I have tried several variations of the
below, but I'm apperantly not grabbing the correct name to ID the control
source so I can check it to see if it has data in it and then take some
action. I'm sure this is pretty simple, but I'm stuck. Anyone out there who
can help would be appreciated. These are a few of the variations I have
tried:

Select Case ControlSource
Select Case Me.ControlSource
Select Case Me.Name

Case "CaseType"
If Not IsNull(CaseType) Then
'Add code to highlight the field
End If

Case "DateOccd"
If Not IsNull(DateOccd) Then
'Add code to highlight the field
Else
End If

End Select
 
You could use the TAG property of your controls and use code similar to
below

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Place an asterisk (*) in the Tag Property of the text
'boxes you wish to validate.
'Then in the BeforeUpdate Event of the form, copy/paste the following:
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control
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
End Sub

Allan
 
I ran into one problem. If the field is a combo box the code errors out with
a message "runtime error 57097...Application defined or object defined error".

If the field is a standard textbox it works fine. any clue?
 
Instead of the msg box, I want to just highlight all the fields by changing
the background color to something like yellow.

I tried to change the code to use

ctl.Name.BackColor = "#FFC20E"

but it errors saying its an invalid use. Plus I need to do it on all
control types. I see where the code only does text boxes, but not sure how I
can modify that if you can offer any other suggestions?
 
Back
Top