Making the report use a query field for the report name 1

[cimoli]

I am trying to tell my OpenReport what report name to use.
The answer lies in the underlying query which has a field called ReportName.
Here is what i have so far. The error says it cannot find the field noted in my expression.
It must mean my query field ReportName.

FYI, for a particular record, the answer inside the query reportname is rptTicketInd.
For another record, it could be rptTicketSplinter. In other words, the query field reportName
could have a different rpt to use.

Thanks for looking at this.

Private Sub cmd1102_Click()

Dim sReportName As String
sReportName = [ReportName]


DoCmd.OpenReport sReportName, acViewPreview

End Sub

 

[cimoli]

Probably not possible. I can't figure out how to get the answer in the query field called reportname, into the form for the report button.
so never mind.

 

[MajP]

Probably not possible

Should be trivial not impossible. In fact I would think that code should work.
May just need to debug to see what is happening.

Private Sub cmd1102_Click()
  'No need for the variable sReportName, you only use it once. Not wrong but wasted code
  ' Put a debug in to check that you are getting the correct value.
  debug.print me.ReportName
  'does that show rptTicketSplinter in the immediated window?
  DoCmd.OpenReport Me.ReportName, acViewPreview
End Sub

 

[cimoli]

Hi MajP: Thanks for offering your help.

I tried the code but i got an error on ReportName.

I see the ME. Are you assuming that i have the query's field ReportName answer on the
same form as my button?

I don't have the field on the form.
I was hoping, and i am probably wrong, that the button would look to the query which
has just 1 record each time and the ReportName is in a field in that query.
I bet i am wrong on trying this?

maybe i need the Me.ReportName to be instead some link to the qryTicketInd ??


FYI, I am using a method where i store in tblReport my desired reportname which depends
upon 3 number fields. BusnID, ResortId and ReportNumID. The query qryTicketInd draws in the needed reportname
that i want to use for the report button. I bet that i am wrong in asking a report button vba to look
to a qry.

i can't get the reportname into the form where the report button sits.

If you think this is hard or not worthwhile, that is okay.

 

[MajP]

So if you have a query with just a single record in it, you can use a dlookup function to get that value or a recordset to get that value from the query.

Something like this. (This assumes your field is 'ReportName', and your query is 'qyrticketInd')

  dim rptName as string
  rptName = dlookup("ReportName", "qryTicketInd")
  DoCmd.OpenReport ReportName, acViewPreview

http://www.techonthenet.com/access/functions/domain/dlookup.php

If you use a dlookup without a criteria it returns the value of the first record. Since you only have one record, it will return the correct value.

 

[cimoli]

You did it MjP. Both ways. Before i saw the last message from you, i was able to finally get ReportNum onto the Form.
I had to use 2 queries to get it to go. I used your first approach for the button.

But now your second idea intregues me. It is more like i wanted. I figured that once i said that the query always
gives 1 record only, that you would cook something up. the dlookup i mean.

I will review tomorrow which one to go with.
the 2nd way would allow me to get my query back to 1 qry instead of 2. more speed for the Lan.

But either way works. You are a god send. I've been struggling on this since last Weds.
I was close but no cigar. thanks so much.