Loop Through form/subform textbxoes and comboboxes change color based on value

[bewsh1987]

Hello people



I have spent most of my morning trying to get this right and I need your help

I need loop through textboxes and combo boxes on a sub form within a form


below are the names for theform and controls

Form Name is "Home"
Subform name is "Job_List_and_invoice"


'Text25 - text e.g - "Forms!Home.Job_List_and_invoice.Form.Text25"
'Total Cost - £0.00
'Job Specs - Memo
'Customer Number - £text
'Combo85 - Combo text


I have the below code that I need to loop through the controls and change the null or zeros to a red border

I know this can be done separately however I will be adding more controls and would like this to be easy to maintain

Forms!Home.Job_List_and_invoice.Form.[Text25].BorderColor = RGB(255, 0, 0)

 Private Sub Command84_Click()
 Dim mytextbox As String
 Dim i As Integer
 Dim strtextbox(1 To 5), lngPosition As Long




 If IsNull(Forms!Home.Job_List_and_invoice.Form.[Text25]) Then strtextbox(1) = 0 Else strtextbox(1) = Forms!Home.Job_List_and_invoice.Form.[Text25]
strtextbox(2) = Forms!Home.Job_List_and_invoice.Form.[Total Cost]
 If IsNull(Forms!Home.Job_List_and_invoice.Form.[Job Specs]) Then strtextbox(3) = 0 Else strtextbox(3) = Forms!Home.Job_List_and_invoice.Form.[Job Specs]
 If IsNull(Forms!Home.Job_List_and_invoice.Form.[Customer Number]) Then strtextbox(4) = 0 Else strtextbox(4) = Forms!Home.Job_List_and_invoice.Form.[Customer Number]
 If IsNull(Forms!Home.Job_List_and_invoice.Form.[Combo85]) Then strtextbox(5) = 0 Else strtextbox(5) = Forms!Home.Job_List_and_invoice.Form.[Combo85]

 For i = 1 To 5
mytextbox = strtextbox(i)
MsgBox mytextbox
 '__________Need a workaround______________
' If mytextbox = 0 Then
'mytextbox.BorderColor.Value = RGB(255, 0, 0)
' Else
'mytextbox.BorderColor.Value = background1
' End If
 '________________________________________
 Next I

 End Sub

[Missinglinq]

In the appropriate event(s)

Dim ctrl As Control

For Each ctrl In Me.Controls
    
    If TypeOf ctrl Is TextBox Or TypeOf ctrl Is ComboBox Then
        If IsNull(ctrl) Or ctrl = 0 Then
         ctrl.BorderColor = RGB(255, 0, 0)
        Else
         ctrl.BorderColor = background1
        End If
    End If

Next

Notice I dropped the .Value on your BorderColor.Value; properties do not have a Value property.

Linq ;0)>

[CJ_London]

or you can use conditional formatting - no code required

[Missinglinq]

or you can use conditional formatting - no code required

• Not for setting BorderColors, only ForeColors/BackColors!
• The OP wants to have the same formatting, when he adds new Controls, without having to address the problem each time he adds a Control!

Linq ;0)>

[bewsh1987]

Thanks for replying

I am probably making this too complicated for myself :-(

The .value was a mistake after I used the code I was trying a lot of methods.

I need the boxes to change when I click submit there is a lot more code that will go with the code in question

I have a box to count the null values to pop up before I send the invoice to customers

the current code is below if you see I have the same type of code repeating which works however I will be tripling the amount of textboxes so need a way to lower the code

Private Sub Command85_Click()
'On Error GoTo er2:

Set db = Nothing
Dim myPath As String
Dim strReportName As String
Dim strReport1, strReport2 As String
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim MyBodyText As String
Dim Attach, Attach2 As String

lngRed = RGB(255, 0, 0)
lngRed2 = background1
 
Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value = 0

'----------------[Text25]
If IsNull(Forms!Home.Job_List_and_invoice.Form.[Text25].Value) Then
Forms!Home.Job_List_and_invoice.Form.[Text25].BorderColor = lngRed
Forms!Home.Job_List_and_invoice.Form.[Text25].BorderWidth = 2
Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value = Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value + 1
Else
Forms!Home.Job_List_and_invoice.Form.[Text25].BorderColor = lngRed2
Forms!Home.Job_List_and_invoice.Form.[Text25].BorderWidth = Hairline
End If
'----------------[Total Cost]
If Forms!Home.Job_List_and_invoice.Form.[Total Cost].Value = 0 Then
Forms!Home.Job_List_and_invoice.Form.[Total Cost].BorderColor = lngRed
Forms!Home.Job_List_and_invoice.Form.[Total Cost].BorderWidth = 2
Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value = Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value + 1
Else
Forms!Home.Job_List_and_invoice.Form.[Total Cost].BorderColor = lngRed2
Forms!Home.Job_List_and_invoice.Form.[Total Cost].BorderWidth = Hairline
End If
'----------------[Job Specs]
If IsNull(Forms!Home.Job_List_and_invoice.Form.[Job Specs].Value) Then
Forms!Home.Job_List_and_invoice.Form.[Job Specs].BorderColor = lngRed
Forms!Home.Job_List_and_invoice.Form.[Job Specs].BorderWidth = 2
Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value = Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value + 1
Else
Forms!Home.Job_List_and_invoice.Form.[Job Specs].BorderColor = lngRed2
Forms!Home.Job_List_and_invoice.Form.[Job Specs].BorderWidth = Hairline
End If
'----------------[Customer Number]
If IsNull(Forms!Home.Job_List_and_invoice.Form.[Customer Number].Value) Then
Forms!Home.Job_List_and_invoice.Form.[Customer Number].BorderColor = lngRed
Forms!Home.Job_List_and_invoice.Form.[Customer Number].BorderWidth = 2
Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value = Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value + 1
Else
Forms!Home.Job_List_and_invoice.Form.[Customer Number].BorderColor = lngRed2
Forms!Home.Job_List_and_invoice.Form.[Customer Number].BorderWidth = Hairline
End If
'----------------[Combo85]
If IsNull(Forms!Home.Job_List_and_invoice.Form.[Combo85].Value) Then
Forms!Home.Job_List_and_invoice.Form.[Combo85].BorderColor = lngRed
Forms!Home.Job_List_and_invoice.Form.[Combo85].BorderWidth = 2
Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value = Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value + 1
Else
Forms!Home.Job_List_and_invoice.Form.[Combo85].BorderColor = lngRed2
Forms!Home.Job_List_and_invoice.Form.[Combo85].BorderWidth = Hairline
End If
 
If Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value <> 0 Then
MsgBox _
"      Please input required Fields to complet this step" & vbNewLine & _
Chr(9) & "               -------Or--------" & vbNewLine & _
Chr(9) & "Move Back to select inputted data", vbOKOnly
Exit Sub
Else
MsgBox "good to go"
End If
Exit Sub
er2:
If IsNull(Forms!Home.Job_List_and_invoice.Form.Reference.Value) And Not IsNull(Forms!Home.Job_List_and_invoice.Form.[Date Invoiced].Value) Then
refprt1 = Chr(WorksheetFunction.RandBetween(65, 90)) & Chr(WorksheetFunction.RandBetween(65, 90)) & Chr(WorksheetFunction.RandBetween(65, 90))
refprt2 = WorksheetFunction.RandBetween(100000, 999999)
Forms!Home.Job_List_and_invoice.Form.Reference.Value = refprt1 & refprt2

Exit Sub
End If
Exit Sub

DoCmd.OpenReport "Job List and invoice", acViewPreview
er:

myPath = "maypath"
strReport1 = Forms!Home.Job_List_and_invoice.Form.Reference.Value & "_"
strReport2 = Format(Forms!Home.Job_List_and_invoice.Form.[Date Invoiced].Value, "DD-MM-YY")
strReportName = "REF=" & strReport1 & Format(strReport2, "DD-MM-YY") & ".pdf"

On Error GoTo er:
DoCmd.OutputTo acOutputReport, "", acFormatPDF, myPath & strReportName

Attach = myPath & "REF=" & strReport1 & Format(strReport2, "DD-MM-YY") & ".pdf"
 
Set MyOutlook = New Outlook.Application
Set MyMail = MyOutlook.CreateItem(olMailItem)
With MyMail
.To = ""
.Subject = "Carpets Invoice Ref: " & strReport1
.Body = MyBodyText
.Attachments.Add Attach
.Display '.send
End With
Set MyMail = Nothing
'MyOutlook.Quit
Set MyOutlook = Nothing
'MailList.Close
Set MailList = Nothing

DoCmd.Close acReport, "Job List and invoice"
End Sub

[CJ_London]

Ah - yes - hadn't read the original post correctly - for some reason I thought the subform was continuous

[June7]

Can use looping structure.

One method uses Tag property. Can also test for the type of control (textbox, combobox, listbox, label, etc.) and only act on the desired type. Code would loop through Controls collection and test Tag property and/or type of every control and if a specific value is encountered in the Tag property, act on that control, like:

Dim ctl As Control
For Each ctl In Me.Controls
If ctl.Tag = "this" And ctl.ControlType = acTextBox Then ctl.BorderColor = something
Next

Another method relies on controls having similar names, like: tbxData1, tbxData2, etc. Then the looping structure like:

For x = 1 to number of controls involved
Me.Controls("tbxData" & x).BorderColor = something
Next

[bewsh1987]

I Really like both methods I have used the below

For x = 1 to number of controls involved
 Me.Controls("tbxData" & x).BorderColor = something
 Next

here is my final code to loop through the text boxes I want to select

thank you all for your help

Private Sub Command84_Click()
 Dim ctl As Control
 Dim mytextbox As String
 Dim lngRed As String
 Dim lngRed2 As String
 Dim i As Integer

 Dim strtextbox(1 To 5), lngPosition As Long

 lngRed = RGB(255, 0, 0)

 lngRed2 = background1


strtextbox(1) = "Text25"
strtextbox(2) = "Reference"
strtextbox(3) = "Total Cost"
strtextbox(4) = "Combo85"
strtextbox(5) = "Customer Number"
strtextbox(5) = "Job Specs"

For i = 1 To 5
mytextbox = strtextbox(i)
For x = 1 To 5

If Forms!Home.Job_List_and_invoice.Form.Controls(mytextbox).Value = "" Then

    Forms!Home.Job_List_and_invoice.Form.Controls(mytextbox).BorderColor = lngRed
    Forms!Home.Job_List_and_invoice.Form.Controls(mytextbox).BorderWidth = 2

Else

    Forms!Home.Job_List_and_invoice.Form.Controls(mytextbox).BorderColor = lngRed2
    Forms!Home.Job_List_and_invoice.Form.Controls(mytextbox).BorderWidth = hairline

End If

Next
 
Next i
 
 
End Sub

[Missinglinq]

First off, you have a problem here:

strtextbox(5) = "Customer Number"
strtextbox(5) = "Job Specs"

Secondly, your code

If Forms!Home.Job_List_and_invoice.Form.Controls(mytextbox).Value = "" Then

is not testing for a Null, which is what 'empty' Textboxes usually contain, it's testing for a Zero-Length String, which is not the same thing! In Access, ZLSs are usually only encountered if data has been imported from an app that uses ZLSs for empty fields. If a Control is 'empty' your code is not going to fire and the formatting won't take place.

Lastly, you stated "I will be adding more controls and would like this to be easy to maintain," but with this approach you'll have to rewrite your formatting code each time you add a Control! The example I gave in Post #2, is all the formatting code you'll ever need, no matter how many Textboxes or Comboboxes you add...you'll never need to modify the code.

Linq ;0)>

[bewsh1987]

First off, you have a problem here:


Lastly, you stated "I will be adding more controls and would like this to be easy to maintain," but with this approach you'll have to rewrite your formatting code each time you add a Control! The example I gave in Post #2, is all the formatting code you'll ever need, no matter how many Textboxes or Comboboxes you add...you'll never need to modify the code.

Linq ;0)>

Thanks I realised my mistake after just never came round to correcting my mistakes on here,

I like the loop through all however there is some controls that I do not want this to loop through and ignore so what I have works perfect to a point all I need to do is update strtextbox(1) part then this works fine all the values are Null to begin with now

thanks ever so

I will use the loop all for my next project as I will need it thank you so much

Dim ctl As Control
Dim mytextbox As String
Dim lngRed As String
Dim i As Integer

Dim strtextbox(1 To 4), lngPosition As Long

lngRed = RGB(255, 0, 0)

strtextbox(1) = "Text25"
strtextbox(2) = "Combo85"
strtextbox(3) = "Customer Number"
strtextbox(4) = "Job Specs"


 

Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value = 0
 
For i = 1 To 4
mytextbox = strtextbox(i)
For x = 1 To 4
If IsNull(Forms!Home.Job_List_and_invoice.Form.Controls(mytextbox).Value) Then
    Forms!Home.Job_List_and_invoice.Form.Controls(mytextbox).BorderColor = lngRed
    Forms!Home.Job_List_and_invoice.Form.Controls(mytextbox).BorderWidth = 2
    Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value = Forms!Home.Job_List_and_invoice.Form.[NumberBox1].Value + 1
Else
    Forms!Home.Job_List_and_invoice.Form.Controls(mytextbox).BorderColor = background1
    Forms!Home.Job_List_and_invoice.Form.Controls(mytextbox).BorderWidth = hairline
End If
Next
 
Next I

[June7]

If you want to use the loop all but not act on all textboxes, that's where the Tag property comes into play, or some other conditional.