Solved Changing subform background with if and statement

[dconway]

Hello Everyone, I'm quite new to access and if you know how to make the majority of the code please help. TIA

I would like to know if there is a way to change a subform back color with an if and statement based off the day of the week.
For example if today is Monday or (2) then the background of the Monday subform would be yellow else if today is not Monday or (2) then the back color is white. See below for an example of what my form looks like.

 

[tvanstiphout]

Design the form, select the Date field and use Conditional Formatting to set the color based on the Weekday(Date())

 

[theDBguy]

Hi. Welcome to AWF!

Can you also show us your form/subform in design view?

 

[LarryE]

You can use VBA code to change form background colors also. Use the subforms On Load Event:

Private Sub Form_Load()
If WeekdayName(Weekday(Now), False, vbSunday) = "Monday" Then
    'Yellow
    Me.FormHeader.BackColor = RGB(255, 255, 0)
    Me.Detail.BackColor = RGB(255, 255, 0)
    Me.FormFooter.BackColor = RGB(255, 255, 0)
Else
    'White
    Me.FormHeader.BackColor = RGB(255, 255, 255)
    Me.Detail.BackColor = RGB(255, 255, 255)
    Me.FormFooter.BackColor = RGB(255, 255, 255)
End If

ACCESS also has its own color codes, so you can use:

If WeekdayName(Weekday(Now), False, vbSunday) = "Monday" Then
    'Yellow
    Me.FormHeader.BackColor = 65535
    Me.Detail.BackColor = 65535
    Me.FormFooter.BackColor = 65535
Else
    'White
    Me.FormHeader.BackColor = 16777215
    Me.Detail.BackColor = 16777215
    Me.FormFooter.BackColor = 16777215
End If

Here is an ACCESS file with the color codes in a table

 

[dconway]

Hi. Welcome to AWF!

Can you also show us your form/subform in design view?

I think becuase I have querries driving the subforms that is why I cant figure out how to vba code it?

 

[LarryE]

I think becuase I have querries driving the subforms that is why I cant figure out how to vba code it?
View attachment 113120

That has nothing to do with formatting the backgrounds or the different sections of the form.

 

[arnelgp]

you can try this:
add code to the Load Event of the Main Form:

Private Sub Form_Load()
Me.TheMondaySubFormNameHere.Form.DatasheetBackColor = 13434879
End Sub

 

[dconway]

This is what I put in the main forms code for on load. But I get an error message.

Private Sub Form_Load()
Me.MONDAYLIST.Form.DatasheetBackColor = 13434879
End Sub

 

[arnelgp]

"ambiguous" meaning you have 2 Load events?

see this demo.

 

[dconway]

"ambiguous" meaning you have 2 Load events?

see this demo.

Yes I had load event to auto maximize the main form. I removed that and your code worked. But If I wanted to convert what you have to an if and statement based on todays date that the Monday subform would be white unless today was actually Monday and the same scenario for all other weekdays would I still have this "ambiguous" issue? Or is there a way around it?

 

[arnelgp]

first i renamed all subforms, starting with monday as Child2... upto Child6.
now on the load event, i determine what Day of Week number is today's date.
then set the color for that day (subform) except Saturday and Sunday (which i don't have a subform).

 

[dconway]

first i renamed all subforms, starting with monday as Child2... upto Child6.
now on the load event, i determine what Day of Week number is today's date.
then set the color for that day (subform) except Saturday and Sunday (which i don't have a subform).

That was an excellent example! Thank you very much!