MS ACCESS 2003 - Question about passing information from an unbound form to a report

Question

I use something like below to pass information from one unbound form to another (excuse the no error checking):

Sub Button_Click()
Dim db as DAO.database
Dim rs as DAO.recordset
Dim sql as string

docmd.openform "NextFormIamAbouttoUse"

sql = "SELECT * FROM tblMain WHERE MainID = " & Me.As_MainID & ";"

' usually debug here

set db = currentdb
set rs = db.openrecordset(sql)

if rs.eof then
   msgbox "oops message"
else
     rs.movefirst
          [Forms]![NextFormIamAbouttoUse].as_mainID = rs![MainID]
          [forms]![NextFormIamAbouttoUse].value1 = rs![value1]
          ' etc

     rs.close
     set db = nothing
     set rs = nothing
     sql = ""
 end if

 'error stuff
  end sub

ok so i can use this kind of thing for transferring information from forms to reports as well? see i use a lot of forms full of text boxes as dashboards, where the text boxes serve no entry purpose, rather display quick information rather.

user navigate through different forms to build the "dashboard-like" forms to their liking then at some point want to print. so i wanted to build look-a-like (in terms of the data offered) reports at each point so they can print the page of information they have built through these forms.

so i tried to do this with a report:

Sub Button_Click()
Dim db as DAO.database
Dim rs as DAO.recordset
Dim sql as string

docmd.openreport "ReportTester", acViewPreview

sql = "SELECT * FROM tblMain WHERE MainID = " & Me.As_MainID & ";"

' usually debug here

set db = currentdb
set rs = db.openrecordset(sql)

if rs.eof then
   msgbox "oops message"
else
     rs.movefirst
          [reports]![ReportTester].as_mainID = rs![MainID]
          [reports]![ReportTester].value1 = rs![value1]
          ' etc

     rs.close
     set db = nothing
     set rs = nothing
     sql = ""
 end if

 'error stuff
  end sub

just as a guess, and it didn't work. no errors, or anything. just will not display any information this way. won't even pass the ID number.

So I am thinking that opening the report in AcViewPreview means that I cannot add information to those text boxes after the fact, but I am jsut not certain. So any advice on this is much appreciated. thanks!

Answer 1

You have a few options. You can either create a query, and pass the form control values into the values there, thus creating a 1 record query, which you can base your report on.

Each Column will look like this: Data: Forms![MyForm]![MyControl]

Or you can just bind the controls from the form directly to the report controls.

You will need to leave the form open containing the values while the report is opened, if you opt for either of these routes. No complex code needed.

Answer 2

I think I'm missing something. Why wouldn't you just have the report's Recordsource be the same as the form's Recordsource, and then filter at runtime to a particular MainID in the DoCmd.OpenReport's WhereCondition argument? Then you can run the report without needing to have the form open.

Unless the form is unbound, or has fields that are expensive to calculate on the fly, I can't see any advantage to binding the report to the form at all.