Solved Using TempVars (1 Viewer)

[ClaraBarton]

DoCmd.OpenReport TempVars!Report, acViewDesign, , , acHidden
  Reports!TempVars!Report.RecordSource = strSql
  DoCmd.Close acReport, TempVars!Report, acSaveYes

The above code recognizes the first line just fine but gives the error "The report name "TempVars" is misspelled or..."
What is the proper syntax for line 2?
Or is there a better way to change the recordsource?

 

[June7]

Reports(TempVars!Report).RecordSource

Why are you changing RecordSource in design view then just saving? I use code in report's Open event to set its RecordSource when report opens for printing/viewing.

 

[ClaraBarton]

Reports(TempVars!Report).RecordSource

Why are you changing RecordSource in design view then just saving? I use code in report's Open event to set its RecordSource when report opens for printing/viewing.

That's what I want to do. How do you set the recordsource on Open? It changes by the form that's calling it

 

[June7]

Example:

Private Sub Report_Open(Cancel As Integer)
'report is called by SampleManagement and zPre2009SamplesView forms
'set RecordSource to pull data from appropriate tables
Me.RecordSource = Me.OpenArgs
End Sub

 

[ClaraBarton]

Example:

Private Sub Report_Open(Cancel As Integer)
'report is called by SampleManagement and zPre2009SamplesView forms
'set RecordSource to pull data from appropriate tables
Me.RecordSource = Me.OpenArgs
End Sub

I need to use an sql string and a filter because my form (which has the filter), in order to be editable does not include all tables and fields. All logical things don't seem to work.

 

[ClaraBarton]

Ok... went back... I used OpenArgs on open and it says you can't set the recordsource on preview. How do I do this?

 

[ClaraBarton]

Ok... went back... I used OpenArgs on open and it says you can't set the recordsource on preview. How do I do this?

Now that I think about it, that's why I was using report design

 

[ClaraBarton]

Now that I think about it, that's why I was using report design

Soooo.... back to my original question?

 

[ClaraBarton]

Soooo.... back to my original question?

Oh wait! I went back and you answered my question! Thank you very much. It works!

 

[ebs17]

acViewDesign

Design changes at runtime are not good style. If you create your application as an ACCDE for distribution, this is no longer possible.

is there a better way to change the recordsource?

Open the report with a WHERE condition. Or what dramatic change in the RecordSource should be made?

 

[ClaraBarton]

acViewDesign

Design changes at runtime are not good style. If you create your application as an ACCDE for distribution, this is no longer possible.

is there a better way to change the recordsource?

Open the report with a WHERE condition. Or what dramatic change in the RecordSource should be made?

Yeah, I'm not happy with it either. Here's the deal: I have a datasheet that's for editing cards for library books. The author is not included in the datasheet because the author is a many to many relationship and it makes it uneditable. However, I need the author on the cards. So I need a sql statement to include the authors. THEN the filter doesn't work because it's based on the datasheet. Fixing that was going to be my next question.

 

[June7]

You need authors as a subdatasheet? Or place the cards datasheet as a subform on another form and sit the authors form next to it. Review https://www.fmsinc.com/MicrosoftAcc...ink Child,based on a control on the main form.

Now to include authors on report to produce compact cards, can use a VBA function to concatenate authors as a single string. Review http://allenbrowne.com/func-concat.html

 

[ClaraBarton]

Oh Duh! Sometimes I'm just too dumb. My report requires sub reports. Easy Peasy! Thank you for your help.