Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Making the report use a query field for the report name

Making the report use a query field for the report name

Making the report use a query field for the report name

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

RE: Making the report use a query field for the report name

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.

RE: Making the report use a query field for the report name


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.

CODE -->

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 

RE: Making the report use a query field for the report name

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.

RE: Making the report use a query field for the report name

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')

CODE -->

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

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.

RE: Making the report use a query field for the report name

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close