Solved Controls source is =[OpenArgs] From Form (1 Viewer)

oxicottin

Learning by pecking away....
Local time
Today, 03:06
Joined
Jun 26, 2007
Messages
856
Hello, I have a form with a few option buttons that use OpenArgs to change the dates in a report. If the date is a Friday #7 then highlight the textbox on the report. I tried using Weekday([OpenArgs])=7 under conditional formatting but it never changes the color. How should I approach this?

Date text box on report I need to change background color if it's a Friday.
OpenArgs.JPG



Code on the form:
Code:
'Print Friday sheet if ticked.
    If Me.optFri = True Then
        If Me.Dirty Then Me.Dirty = False
        DoCmd.OpenReport "rpt_WeeklyAttendanceSheet", , , , , Format(Me.txtWeekEnding, "Long Date")
    End If
 
Last edited:

GaP42

Active member
Local time
Today, 17:06
Joined
Apr 27, 2020
Messages
406
Could it be openargs is a string and your vba wants a date?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:06
Joined
May 21, 2018
Messages
8,685
I would get ride of = [openargs]. Does that actually work?

in the forms report's load event
Code:
if not (me.Openargs & "") = "" then
    me.txtDate = cdate(me.openargs)
end if
 
Last edited:

oxicottin

Learning by pecking away....
Local time
Today, 03:06
Joined
Jun 26, 2007
Messages
856
I would get ride of = [openargs]. Does that actually work?

in the forms load event
Code:
if not (me.Openargs & "") = "" then
    me.txtDate = cdate(me.openargs)
end if

@MajP yes it works well and its in the reports text box that uses the Openargs from the VBA in the forms option. You wrote to use your code in the forms load event, I'm not understanding and I think maybe I didn't explain what I'm trying to do very well. I attached a copy of the DB, what I want to do is if I open frm_WeeklyAttendanceSheet and tick the 5 options and select print then it will print those days based on the date in the forms txtWeekEnding text box. What I want it to do is if Friday is ticked (optFri) then I want the report rpt_WeeklyAttendanceSheet text box txtDate to have a highlighted background color.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:06
Joined
May 21, 2018
Messages
8,685
I will take a look I meant the Reports on load.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:06
Joined
May 21, 2018
Messages
8,685
This works for me in the report on load
Code:
Private Sub Report_Load()
  If Not (Me.OpenArgs & "") = "" Then
     Me.txtDate = CDate(Me.OpenArgs)
    If Weekday(Me.txtDate) = vbFriday Then Me.txtDate.BackColor = vbYellow
  End If
End Sub

However you cannot (or need to) pass a long date. Change the code to an easier to read date format
DoCmd.OpenReport "rpt_WeeklyAttendanceSheet", acPreview, , , , Format(Me.txtWeekEnding - 4, "mm/dd/yyyy")


Open args passes a string, and if you push that string to an unbound text box that has a date format applied it might figure out it is a date, but maybe not. So convert it to a date format in the load event.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:06
Joined
Oct 29, 2018
Messages
21,613
I tried using Weekday([OpenArgs])=7 under conditional formatting but it never changes the color.
What happens if you try?
Code:
Weekday([txtDate])=7
Edit: Based on what @MajP just said, maybe also try:
Code:
Weekday(CDate([txtDate]))=7
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:06
Joined
May 21, 2018
Messages
8,685
If in your form if you would have simply passed a date in open args without formatting to long date it probably would work fine even without the mod I did. VB is really good at implicitly casting anything that looks like a date, but that long date format is probably too complicated

Here is a good demo showing how it casts a string to a date when the string is less ambigous.
Code:
Public Sub DateConvert()
  Dim x As Variant
  Dim y As Date

  x = "1/19/2024"
  y = x
  Debug.Print y

  x = "Friday, January 19, 2024"
  y = x
  Debug.Print y
End Sub

The above works fine when I set a date variable to a string of form "1/19/2024"
It fails with a type mismatch when I try the same thing with "Friday, January 19, 2024"

Your conditional formatting never worked because it could not figure out it was a date.
 
Last edited:

oxicottin

Learning by pecking away....
Local time
Today, 03:06
Joined
Jun 26, 2007
Messages
856
This works for me in the report on load
Code:
Private Sub Report_Load()
  If Not (Me.OpenArgs & "") = "" Then
     Me.txtDate = CDate(Me.OpenArgs)
    If Weekday(Me.txtDate) = vbFriday Then Me.txtDate.BackColor = vbYellow
  End If
End Sub

However you cannot (or need to) pass a long date. Change the code to an easier to read date format
DoCmd.OpenReport "rpt_WeeklyAttendanceSheet", acPreview, , , , Format(Me.txtWeekEnding - 4, "mm/dd/yyyy")


Open args passes a string, and if you push that string to an unbound text box that has a date format applied it might figure out it is a date, but maybe not. So convert it to a date format in the load event.

@MajP I'm sorry I tried your example in my reports load and when printing, it's still prints normal with no yellow background? I see what your saying with your X and Y example I get the error 13 also as you stated BUT I changed the long date to mm/dd/yyyy like you suggested and its outcome is the same.

The reason for the long date is so it shows what day of the week which is needed on the printouts...
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:06
Joined
May 7, 2009
Messages
19,249
here is another.
I remove the Formating on your OpenArgs parameter when you open the report.
next I added a dummy (hidden) textbox, txtOpenArg and set its ControlSource to:
Code:
=[OpenArgs]
txtDate textbox controlsource, I changed to:
Code:
=Format([OpenArgs], "Long Date")
next i changed the Conditional Format to:
Code:
Format([txtOpenArg], "ddd") = "Fri"
 

Attachments

  • OpenArgs Test.accdb
    820 KB · Views: 61

Users who are viewing this thread

Top Bottom