Solved

Referring to a form in sql but NOT by name

Posted on 2014-04-16
4
333 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 34

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now