INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

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

Jobs from Indeed

Open report via command button

Open report via command button

(OP)
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.

CODE

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

cmdPreviewSkills_Click_Exit:
    Exit Sub

cmdPreviewSkills_Click_Err:
    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

CODE

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

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

Good luck,

JB

RE: Open report via command button

(OP)
JB,
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 -

Quote:


...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:

CODE

=[Forms]![frmFilteredSkillsListBox]!MySubFormControlName.form![txtSkillID]))

JB
 

RE: Open report via command button

(OP)
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.
Dom

RE: Open report via command button

Too easy mate, good luck,

JB

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!

Resources

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