Referring to a form in sql but NOT by name

Posted on 2014-04-16
Last Modified: 2014-04-18

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.

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)
Question by:Patrick O'Dea
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 61

Accepted Solution

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)
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
    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
    DoCmd.OpenReport "rptShowCustomers", acNormal
End Sub

Open in new window

LVL 37

Assisted Solution

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.

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!

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

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…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

705 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