Solved

Referring to a form in sql but NOT by name

Posted on 2014-04-16
4
336 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 250 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 100 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 36

Assisted Solution

by:PatHartman
PatHartman earned 150 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

679 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