?
Solved

Referring to a form in sql but NOT by name

Posted on 2014-04-16
4
Medium Priority
?
343 Views
Last Modified: 2014-04-18
Hi,

I have a report called "rptShowCustomers" which is based on qry2014.

This report is called by TWO forms - "job1" and "job2"

See the SQL link below where I have hardcoded "job1" into my query.

http://screencast.com/t/N5xeKrwgUtf

Question: How do I remove the "job1" from the query so that it will work for both "job1" AND "job2".


(PS: I raised a query like this earlier but did not resolve it due to my mis-understanding)
0
Comment
Question by:Patrick O'Dea
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 1000 total points
ID: 40006129
Remove the criteria from your query, and pass the criteria through code when opening the report:


Dim strCrit as string
strCrit = "CustomerID = " & me.CustomerID

Docmd.OpenReport "rptShowCustomers", View := acViewPreview, WhereCondition := strCrit

Open in new window



(This code should work from either form, assuming they both have a field named "CustomerID", which is numeric)
0
 
LVL 19

Assisted Solution

by:Eric Sherman
Eric Sherman earned 400 total points
ID: 40006652
The solution provided by mbizup should work ... as mentioned each form has a field named "CustomerID".

Another approach to your original question ... You would have to modify the SQL of qry2014 based on your Command Button on each Form that runs your "rptShowCustomers".  See code posted below:

Private Sub CmdPrintReportJob1_Click()
    Dim qdf As QueryDef
    Dim strSQL As String
    Set qdf = CurrentDb.QueryDefs("qry2014")
    strSQL = "SELECT CustomerID, CustomerName, FullAddress FROM tblCustomers WHERE CustomerID = [Forms]![job1]![CustomerID];"
    qdf.SQL = strSQL
    qdf.Close
    DoCmd.OpenReport "rptShowCustomers", acNormal
End Sub
Private Sub CmdPrintReportJob2_Click()
    Dim qdf As QueryDef
    Dim strSQL As String
    Set qdf = CurrentDb.QueryDefs("qry2014")
    strSQL = "SELECT CustomerID, CustomerName, FullAddress FROM tblCustomers WHERE CustomerID = [Forms]![job2]![CustomerID];"
    qdf.SQL = strSQL
    qdf.Close
    DoCmd.OpenReport "rptShowCustomers", acNormal
End Sub

Open in new window


ET
0
 
LVL 40

Assisted Solution

by:PatHartman
PatHartman earned 600 total points
ID: 40007471
There are situations where you need to have the query refer to a form to get the criteria and so can't pass in arguments using the OpenReport/OpenForm methods.  To provide flexibility in my applications and so that I can also use OutputTo and the Transfer... Methods which don't support criteria arguments, I always have one single form (hidden) that is used to provide criteria for every other form/report/export.  You'd be amazed at how few fields you actually need on this form even in a fair sized application.  When I run a report/open a form from a button click, the first thing I do is to populate the hidden form controls that the query will need with the current values.  Then run the action.

This is a little more cumbersome to set up initially plus you need to be disciplined in its use and don't want to make the mistake of "I don't need that this time, I'm never going to need to run this from anywhere else" but it works smoothly plus has the advantage of allowing you to make the form visible during testing so that it is easy to verify the arguments or even change them on the fly.
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 40008906
Thanks for great answer.

Plenty to think about from Pat!

For the moment I opted for the neat solution from mbizup!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question