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
> Recordset As The Source For A Report., Access 2016    
 
   
InfoHound
post Oct 11 2018, 05:35 PM
Post#1



Posts: 1,097
Joined: 1-December 12



I have a recordset that was created based on a number of user selections in combo boxes on a form; in VBA. The recordset is named "rs" how would I use this recordset as the source for a report?

Thanks
Go to the top of the page
 
GroverParkGeorge
post Oct 11 2018, 06:03 PM
Post#2


UA Admin
Posts: 33,802
Joined: 20-June 02
From: Newcastle, WA


See if this code does the trick for you.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
InfoHound
post Oct 11 2018, 09:55 PM
Post#3



Posts: 1,097
Joined: 1-December 12



Thank you, it does!

Go to the top of the page
 
GroverParkGeorge
post Oct 12 2018, 08:27 AM
Post#4


UA Admin
Posts: 33,802
Joined: 20-June 02
From: Newcastle, WA


Congratulations on solving the problem.

Continued success with your project.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
InfoHound
post Oct 14 2018, 03:52 PM
Post#5



Posts: 1,097
Joined: 1-December 12



I've run into a snag. if the SQL that I pass is greater than 255 characters then the operation fails.
I've seen where people are recommending that the SQL statement be broken up into parts less than 255 and then reassembled using concatenation, however, I'm not sure of how that would work because my string segments vary in length.
Is there a way of passing a SQL (string) that is greater than 255 to the module in Access 2016?

Thanks
Go to the top of the page
 
InfoHound
post Oct 14 2018, 05:58 PM
Post#6



Posts: 1,097
Joined: 1-December 12



The string I'm passing is 672 characters.
I have no problem passing the full 672 characters to this Mod:

CODE
Public Sub testreport(strReportData)
Set grst = CurrentDb.OpenRecordset(strReportData)
    DoCmd.OpenReport "rptDailyTransFiltered", acViewReport
  grst.Close
  Set grst = Nothing
End Sub


However , I won't pass on to the report:

CODE
Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = grst.Name
End Sub

because it will only except the first 255 characters.

I tried running this code but it still only took the first 255 characters.
CODE
Public grst As DAo.Recordset

Public Sub testreport(strReportData)
Dim intSqlLen As Integer
Dim sqlA As String
Dim sqlB As String
Dim sqlC As String
Dim sqlD As String
Dim curTimes As Currency
intSqlLen = Len(strReportData)
curTimes = -Int(-(intSqlLen / 255))
Select Case curTimes
    Case Is = 1
        sqlA = (strReportData)
    Case Is = 2
        sqlA = Left(strReportData, 255)
        sqlB = Mid(strReportData, 256, 255)
    Case Is = 3
        sqlA = Left(strReportData, 255)
        sqlB = Mid(strReportData, 256, 255)
        sqlC = Mid(strReportData, 512, 255)
    Case Is = 4
        sqlA = Left(strReportData, 255)
        sqlB = Mid(strReportData, 256, 255)
        sqlC = Mid(strReportData, 512, 255)
        sqlD = Mid(strReportData, 768, 255)
    Case Else
        MsgBox "The current SQL statement exceeds the maximum number of characters of 1,023 characters."
End Select
Set grst = CurrentDb.OpenRecordset(sqlA & sqlB & sqlC)
    DoCmd.OpenReport "rptDailyTransFiltered", acViewReport
  grst.Close
  Set grst = Nothing
End Sub

This post has been edited by InfoHound: Oct 14 2018, 06:01 PM
Go to the top of the page
 
projecttoday
post Oct 15 2018, 03:17 AM
Post#7


UtterAccess VIP
Posts: 10,186
Joined: 10-February 04
From: South Charleston, WV


Why does your report have to have a variable record source? Why can't it just have a variable where condition?

You're saying

Me.RecordSource = grst.Name

only delivers 255 characters into Me.RecordSource? I'm pretty sure a recordsource can be more than 255 characters so the problem must be with grst.Name. If you put a stop on the code and put the cursor on grst.Name what does it say?

Otherwise, a workaround.

--------------------
Robert Crouser
Go to the top of the page
 
InfoHound
post Oct 15 2018, 06:02 AM
Post#8



Posts: 1,097
Joined: 1-December 12



Thanks projecttoday, that's is what I have done and it seems to be working fine.

Go to the top of the page
 
projecttoday
post Oct 15 2018, 06:33 AM
Post#9


UtterAccess VIP
Posts: 10,186
Joined: 10-February 04
From: South Charleston, WV


yw.gif

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Oct 15 2018, 06:41 AM
Post#10


UtterAccess VIP
Posts: 10,470
Joined: 6-December 03
From: Telegraph Hill


Does your original grst use an SQL statement that you create?

If so, why not just pass that original string to the DoCmd.OpenReport as an OpenArg, and then set the report's RecordSource using that in its Load event?

That way you don't even need a module-level Recordset variable, nor the hacky code to split up the SQL in to chunks.

From the code you posted I'm not sure why you are even bothering with a Recordset at all - you just open it and then close it straight away??!!!

d

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Oct 15 2018, 06:44 AM
Post#11


UtterAccess VIP
Posts: 10,186
Joined: 10-February 04
From: South Charleston, WV


I think infohound is using variable where conditions now.

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Oct 15 2018, 07:44 AM
Post#12


UtterAccess VIP
Posts: 10,470
Joined: 6-December 03
From: Telegraph Hill


Fair enough! It wasn't completely clear to me.

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    24th October 2018 - 12:15 AM