Solved Can't Pass On Form Value to Report (1 Viewer)

diannebcgray

New member
Local time
Today, 06:40
Joined
Aug 9, 2022
Messages
13
Hi,

I am stumped out of my mind. I am trying to use a form control value in the simplest way possible in a report. I have a form as shown below, where a date range determines query values that are displayed in a report as pie and bar charts.
1660003073466.png

When you click Generate Report, this is the code:
Code:
Private Sub btnGenerateReport_Click()
DoCmd.OpenReport "Custom Report", acViewPreview
End Sub

I would like to pass on the above text boxes textStartDate and txtEndDate box values into labels in my report (for example, into chart titles and labels for report titles). I'm not having much luck. What's the easiest way to do this?
 

June7

AWF VIP
Local time
Today, 05:40
Joined
Mar 9, 2014
Messages
5,425
One way is to use textboxes as 'labels'. Expressions in textboxes can reference the form controls. Textbox can overlay the chart to act as title.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:40
Joined
May 21, 2018
Messages
8,463
One way to do it is using the openargs. You have to pass a delimited string since openargs is a single string.

Code:
Public Sub test()
  DoCmd.OpenReport "Custom Report", acViewPreview, , , , Format(me.StartDate, "mm/dd/yyyy") & ";" & Format(me.EndDate, "mm/dd/yyyy")
End Sub

Then in the report you split the string
Code:
Private Sub Report_Open(Cancel As Integer)
  If Not Me.OpenArgs & "" = "" Then
    Me.lblStart.Caption = Split(Me.OpenArgs, ";")(0)
    Me.lblEnd.Caption = Split(Me.OpenArgs, ";")(1)
  End If
End Sub
 

June7

AWF VIP
Local time
Today, 05:40
Joined
Mar 9, 2014
Messages
5,425
As majP suggests, OpenArgs is another way. If you want to avoid VBA, expression in textbox can parse the string with string manipulation functions.

Directly referencing form controls requires form to remain open until report renders or is printed. Passing with OpenArgs eliminates that requirement
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 09:40
Joined
May 21, 2018
Messages
8,463
You can do it in code too
Code:
Private Sub Report_Open(Cancel As Integer)
   If CurrentProject.AllForms("form1").IsLoaded Then
      Me.lblStart.Caption = Forms("form1").txtstart
      Me.lblEnd.Caption = Forms("form1").txtEnd
    End If
End Sub
 

diannebcgray

New member
Local time
Today, 06:40
Joined
Aug 9, 2022
Messages
13
Interesting. Ok, makes sense. The text box method is working for me, though I want to better understand the code methods. If I try using OpenArgs or the code method mentioned immediately above, how then do I apply that to a report or chart title? What would the reference look like for the final product? (I am not a VBA expert by any means).
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:40
Joined
Sep 21, 2011
Messages
14,052
Then again there is always TempVars?
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:40
Joined
Jul 9, 2003
Messages
16,245
how then do I apply that to a report or chart title? What would the reference look like

You should find my blog on this useful:-

Pass Info in to a Report
 
Last edited:

June7

AWF VIP
Local time
Today, 05:40
Joined
Mar 9, 2014
Messages
5,425
Data can be pulled by direct reference to form controls or passed by OpenArgs. Using VBA to set report controls with that data is an option. You've already been given example of VBA setting report label controls.

As for chart elements, simplest is to populate textboxes that overlay chart.
VBA to modify chart elements would be complicated. Example from my db.
Code:
Sub FormatVibGraph(strObject As String, strLabNum As String, booMetric As Boolean)
'format Vibratory graph form and report
Dim obj As Object
Dim gc As Object
Dim MinDD As Double
Dim MaxDD As Double
MinDD = Nz(DMin("Den", "GraphVibratory"), 0)
MaxDD = Nz(DMax("Den", "GraphVibratory"), 0)
If strObject Like "Lab*" Then
    Set obj = Reports(strObject)
Else
    Set obj = Forms(strObject).Controls("ctrVibratory").Form
End If
Set gc = obj("gphDensity")
gc.Activate
If MinDD > 0 Then
    With gc
    .Axes(xlValue).MinimumScale = MinDD
    If booMetric = True Then
        MaxDD = Int(MaxDD / 100) * 100 + 100
        MinDD = MaxDD - 1000
        .Axes(xlValue).MaximumScale = MaxDD
        .Axes(xlValue).MinimumScale = MinDD
        .Axes(xlValue).MajorUnit = 200
        .Axes(xlValue).MinorUnit = 40
    Else
        MaxDD = Int(MaxDD / 5) * 5 + 5
        MinDD = MaxDD - 50
        .Axes(xlValue).MaximumScale = MaxDD
        .Axes(xlValue).MinimumScale = MinDD
        .Axes(xlValue).MajorUnit = 10
        .Axes(xlValue).MinorUnit = 2
    End If
    .Axes(xlValue, xlPrimary).HasTitle = True
    If booMetric = True Then
        .Axes(xlValue, xlPrimary).AxisTitle.Text = "Max. Dry Density, Kg/cu.m"
    End If
    .Axes(xlCategory, xlPrimary).HasTitle = True
    If booMetric = True Then
        .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Percent Passing 4.75 mm Sieve"
    End If
    End With
End If
End Sub
 
Last edited:

Users who are viewing this thread

Top Bottom