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!
  • Students Click Here

*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


Open report via command button

Open report via command button

Open report via command button

I have a report that uses a parameter query to produce results.  The query and report work as expected.  That is when I open the report a box pops up asking me to enter a value.  What I want to do is have a command button on a Form that when click occurs the report will open with the SkillID field equal to the txtSkillID field on the form.

This is the code I have in the query that generates the report.


SELECT Employees.[Last Name], Employees.[First Name], tblSkillsLKU.[Skill Name], Skills.Score, Skills.SkillID

FROM tblSkillsLKU RIGHT JOIN (Employees INNER JOIN Skills ON Employees.[Employee ID] = Skills.[Employee ID]) ON tblSkillsLKU.SkillID = Skills.SkillID

WHERE (((Skills.SkillID)=[Forms]![frmFilteredSkillsListBox]![txtSkillID]))

ORDER BY Skills.Score DESC;{/code]

The where clause shows the criteria I have in the query.  

Here is what I have behind the command button:
[code]Private Sub cmdPreviewSkills_Click()
On Error GoTo cmdPreviewSkills_Click_Err
    Forms![frmEmployee Details].Visible = False
    DoCmd.OpenReport "rptWhoHasSkill", acViewPreview, "", "", acNormal

    Exit Sub

    MsgBox Error$
    Resume cmdPreviewSkills_Click_Exit

End Sub

The question is how can I trigger the report from a command button so that the underlying query perameter (SkillID) is set to the value txtSkillID?

RE: Open report via command button

g'day Dom,

You appear to be on the right tracks and it should work.  Try reversing the docmd.openreport line and the forms!...line although a form being invisible should not stop the query being able to access the value of a control.

Open the form, put a value in txtSkillID

Run the query.  Does it prompt you for a parameter?

If so then I'd suspect it can't evaluate


WHERE (((Skills.SkillID)=[Forms]![frmFilteredSkillsListBox]![txtSkillID]))

and check very carefully for typos in the form and or control name.

Good luck,


RE: Open report via command button

Thanks for the reply.  I reversed the code as you suggested but no go.  I then put a value in txtSkillID as you suggested and the pop up appears asking to enter a parameter value.

So it must be something in the where clause.  I am using a tabbed form and the data I am retrieving is a form on one of the tabs.  As far as I know, you refer to fields on a tabbed form the same as if you were referring to a field on a regular form.  Is that correct?

RE: Open report via command button

you reply is slightly confusing -


...the data I am retrieving is a form on one of the tabs.  As far as I know, you refer to fields on a tabbed form...

If you're referring to a control then the reference above should work.  The tab makes no difference.  If if a control in a *form* on a tab then you need to chuck the subform control name into the mix thus:




RE: Open report via command button

JB, that was it.  I used the BUILD tool in the query to make sure I had the names of the form and subform right it worked just as you said.

Thank you so much for your time and clear explanation.

RE: Open report via command button

Too easy mate, good luck,


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!

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