• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 347
  • Last Modified:

Referring to a form in sql but NOT by name

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
Patrick O'Dea
Asked:
Patrick O'Dea
3 Solutions
 
mbizupCommented:
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
 
Eric ShermanAccountant/DeveloperCommented:
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
 
PatHartmanCommented:
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
 
Patrick O'DeaAuthor Commented:
Thanks for great answer.

Plenty to think about from Pat!

For the moment I opted for the neat solution from mbizup!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now