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.


Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

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