UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Strsql Does Not Control Report Output, Access 2016    
 
   
brastedhouse
post Mar 7 2018, 07:20 PM
Post#1



Posts: 52
Joined: 16-March 15
From: Chautauqua, NY


Greetings,

This is the very first piece of code I have written entirely on my on own. Unfortunately, it does not seem to produce the result I was hoping for.

I have an Option Group with two choices. Data/Option Value(s): 1 and 2. I would like to have Option 1 use Case 1 strSQL and Option 2 to use Case 2 strSQL. I have a report that uses a query. I think I have a problem with the query. I have tried several iterations of SQL in the supporting query, but I cannot get the strSQL to override the supporting query. I must have a hole in my knowledge of how to do this.

Can anyone enlighten me?

Many thanks, Scott


CODE
Private Sub cmdTop510_Click()
On Error GoTo cmdTop510_Click_Err

     Dim strSQL As String

     Select Case fraTop5or10
          Case 1
          strSQL = "SELECT TOP 5 Count(tblCalls.Location) " & _
               "AS CountID, tblCallsLocationsLU.callLocation " & _
               "FROM tblCallsLocationsLU INNER JOIN tblCalls ON tblCallsLocationsLU.callLocationID = tblCalls.Location " & _
               "GROUP BY tblCallsLocationsLU.callLocation, tblCalls.FA, Year([Run Date])=[Choose a Year] " & _
               "HAVING (((tblCalls.FA) = Yes)) " & _
               "ORDER BY Count(tblCalls.Location) DESC;"
          Case 2
          strSQL = "SELECT TOP 10 Count(tblCalls.Location) " & _
               "AS CountID, tblCallsLocationsLU.callLocation " & _
               "FROM tblCallsLocationsLU INNER JOIN tblCalls ON tblCallsLocationsLU.callLocationID = tblCalls.Location " & _
               "GROUP BY tblCallsLocationsLU.callLocation, tblCalls.FA, Year([Run Date])=[Choose a Year] " & _
               "HAVING (((tblCalls.FA) = Yes)) " & _
               "ORDER BY Count(tblCalls.Location) DESC;"
     End Select

     DoCmd.OpenReport "rptCallsFATop5or10", acViewPreview, , , , strSQL
    
     DoCmd.Close acForm, "frmCallsFATopPUM"

cmdTop510_Click_Exit:
    Exit Sub

cmdTop510_Click_Err:
    MsgBox Error$
    Resume cmdTop510_Click_Exit

End Sub
Go to the top of the page
 
DanielPineault
post Mar 7 2018, 07:40 PM
Post#2


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



You are passing the SQL statement to the form's OpenArgs, are you then using it in your report code to change the default Record Source?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
theDBguy
post Mar 7 2018, 07:43 PM
Post#3


Access Wiki and Forums Moderator
Posts: 72,439
Joined: 19-June 07
From: SunnySandyEggo


Hi,

If you’re trying to use the OpenArgs parameter to pass the SQL statement to your report, you must include code in your report to handle it. However, I am not sure a report’s Record Source can be changed while opening it.

You might also try creating two different reports and just open the appropriate one based on the Select Case result.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
brastedhouse
post Mar 7 2018, 07:57 PM
Post#4



Posts: 52
Joined: 16-March 15
From: Chautauqua, NY


Thank you so much. Duh! I need to put the openarg in the Report On Open event of the report, any advice on how word it? Something like rptName.RowSource = Me.OpenArgs
Go to the top of the page
 
brastedhouse
post Mar 7 2018, 08:06 PM
Post#5



Posts: 52
Joined: 16-March 15
From: Chautauqua, NY


HAH! Got it!

Me.RecordSource = Me.OpenArgs in the On Open Event of the report.

Thanks to you both. I would not have gotten without your help.

Best, Scott
Go to the top of the page
 
theDBguy
post Mar 7 2018, 08:55 PM
Post#6


Access Wiki and Forums Moderator
Posts: 72,439
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Glad to hear you got it sorted out. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd June 2018 - 09:20 AM