Open report via command button 1

[Dom606]

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

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?

 

[JBinQLD]

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,

JB

 

[Dom606]

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?

 

[JBinQLD]

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:

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

JB

 

[Dom606]

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

 

[JBinQLD]

Too easy mate, good luck,

JB