X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Referencing Another Form In Vba?, Access 2016    
post Jun 25 2019, 07:44 AM

Posts: 49
Joined: 28-October 18

Hi Guys,

How do i reference a different form in VBA?

Im in a report, and i'm trying to get a label to show what was executed on the form parameters.

The parameters are a start and end date.

Once entered, you click a button to open a report, and on Private Sub Report_Load (Cancel as integer)

i want something like:

me.label15.caption = Dates!StartDate.value
me.label17.caption = Dates!Enddate.value

Please bare in mind the Report doesn't have a record source, its executing various queries to return various bits of information basesd on date parameters.

Thanks for the help
Go to the top of the page
post Jun 25 2019, 08:09 AM

Posts: 368
Joined: 2-April 18

This page is very usefull, take that with you. :Refer to Form and Subform properties and controls

Please forgive in advance my horrible English.
Go to the top of the page
post Jun 25 2019, 08:28 AM

UA Admin
Posts: 37,635
Joined: 20-June 02
From: Newcastle, WA

Phil has given you the reference you need to sort out object references.

However, this statement suggests perhaps we could dig a little more into how Access really works.

"Please bare in mind the Report doesn't have a record source, its executing various queries to return various bits of information basesd on date parameters."

Is it true then, that this control is empty in this report?

Attached File  ReportnoRecordSource.jpg ( 58.48K )Number of downloads: 1

If that's the case, you must be running various queries from events in the report and manually populating controls on the report from the recordsets returned in VBA. Is that correct?

My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
post Jun 25 2019, 08:32 AM

Posts: 49
Joined: 28-October 18

Spot on George.

Record Source Blank.

The form that executes the report, has the parameters on it.

Those parameters are then forced through the report, which contains various bits of information and a chart, that are all linked to queries that use those parameters on the controlling form.

what i would like to know as well is, how to "Store" the values entered on the form, so that they can be referenced on the form when it loads?

Example, a Labels caption to show the start date value that was entered into the parameter on the form?

Anyway to store the values?
Go to the top of the page
post Jun 25 2019, 08:33 AM

Posts: 49
Joined: 28-October 18

Thanks Phil, thats very helpful :-)
Go to the top of the page
post Jun 25 2019, 08:42 AM

Posts: 3,095
Joined: 4-February 07
From: USA, Florida, Delray Beach

  1. This is a lot trickier then it initially seems. You have a Parameter Query that is pulling it's 2 Parameters from Form Fields. Before these Parameters can be assigned to Label Controls on your Report, they must be 'Resolved' via the Eval() Function.
  2. Let's assume your Query is named _qryDates and in the [Start Date] and [End Date] Fields the Criteria for the Parameters look something like this:
  3. The following Code will Execute the Query, Evaluate the Parameters, and assign these Value to Report Labels. The Code has been tested and is fully operational.
    Private Sub Report_Load()
    Dim qdf As DAO.QueryDef
    Dim prm As Parameter

    Set qdf = CurrentDb.QueryDefs("_qryDates")

    With qdf
      Me![Label15].Caption = Eval(.Parameters(0).Name)      'Start Date
      Me![Label17].Caption = Eval(.Parameters(1).Name)      'End Date
    End With
    End Sub
  4. One important thing to remember is that, for the sake of brevity and clarity, I have included no Validation whatsoever (is the Form Loaded, is either 1 or both of the Parameters NULL, are the Parameters Dates, only 1 Record returned, etc.).
  5. Good Luck with your Project.

P.S. - Once the Parameters have been resolved their Values can be strored in Variables and the Form closed.
This post has been edited by ADezii: Jun 25 2019, 09:05 AM
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    6th August 2020 - 05:26 PM