Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

Join Tek-Tips
*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.

Dom606 (IS/IT--Management) (OP)
28 Oct 08 16:45
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?
Helpful Member!  JBinQLD (Programmer)
28 Oct 08 20:16
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
Dom606 (IS/IT--Management) (OP)
28 Oct 08 20:52
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 (Programmer)
28 Oct 08 20:56
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
 
Dom606 (IS/IT--Management) (OP)
29 Oct 08 8:27
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 (Programmer)
29 Oct 08 9:58
Too easy mate, good luck,

JB

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