Putting Report Name in Function

[isorders]

I have a function that needs to get the report name of the report that its being used in and I can't figure out how to do this.
Here is the function. I have commented out the parts that I can't get to run, but might show what I want to do. I want to be able to use this function in multiple reports.

The function is called from a text box in Report XXXX.
I want the function to be able to fill in the XXXX for any report it runs in.

Thanks

Function MHList()

Dim oRS As Recordset
Dim sDistinctSQL As String
Dim sGeneralSQL As String
Dim sRecord
Dim sWONum() As String
Dim i As Integer
Dim strCSVals As String
Dim rptWO As String
'Dim rptName As String
'rptName = Screen.ActiveReport.Name
'MsgBox rptName
'rptWO = [Reports]![ & rptName & ]![txtWorkOrderNo]
rptWO = [Reports]![rptWObyProblem]![txtWorkOrderNo]

'Get the Distinct Work Order Numbers

sDistinctSQL = "SELECT DISTINCT WorkOrderNo FROM tblWONodes WHERE WorkOrderNo = '" & rptWO & "';"

Set oRS = CurrentDb.OpenRecordset(sDistinctSQL, dbOpenDynaset)

'Initialize i to 0

i = 0
If oRS.EOF <> True Then

Do Until oRS.EOF
'Re-dimension the array to make it bigger
ReDim Preserve sWONum(i) As String

'Store each number in the array
sWONum(i) = oRS.Fields(0).Value

i = i + 1

oRS.MoveNext
Loop



For i = LBound(sWONum) To UBound(sWONum)

sGeneralSQL = &quot;SELECT tblWONodes.WorkOrderNo, tblWONodes.NodeID FROM tblWONodes WHERE WorkOrderNo = '&quot; & rptWO & &quot;' ORDER BY tblWONodes.WorkOrderNo;&quot;
Set oRS = CurrentDb.OpenRecordset(sGeneralSQL, dbOpenDynaset)

Do Until oRS.EOF
sRecord = sRecord & oRS.Fields(1) & &quot;,&quot;

oRS.MoveNext
Loop

If sRecord <> &quot;&quot; Then
sRecord = Left(sRecord, Len(sRecord) - 2) 'This will remove the last 2 characters from the string
MHList = sRecord
End If


Next
End If

oRS.Close

End Function

 

[jebry]

Hi!

As long as the code in running in the report you want to get the name for you can use Me.Name

hth


Jeff Bridgham
bridgham@purdue.edu

 

[isorders]

I tried this
rptName = Me.Name and it says invalid use of Me.
I am calling the function from a text box on the report so the Control Source =MHList() for text box txtManhole List.

 

[jebry]

Hi!

Sorry, I didn't catch all you were saying. Try this:

Dim rpt As Report
Dim rptName As String
Set rpt = Screen.ActiveReport
rptName = rpt.Name
MsgBox rptName

hth


Jeff Bridgham
bridgham@purdue.edu

 

[isorders]

I have tried variations of this but it keeps giving that you have entereed a report that requires a report to be the active window.
The only way I can get it to run is to go to design view and then back to the preview and then it will work, but it won't work from the onclick command button request to open the report.
I am missing a step as the report is opening that is hanging this up.

 

[isorders]

I am close to getting this working.
I can pass a parameter from the button click which opens the report.

Function MHList(rept As String)

Dim oRS As Recordset
Dim sDistinctSQL As String
Dim sGeneralSQL As String
Dim sRecord
Dim sWONum() As String
Dim i As Integer
Dim strCSVals As String
Dim rptWO As String
MsgBox rept
rptWO = [Reports]![& rept &]![txtWorkOrderNo]

The rept value though in the last line keeps failing with a 2451 The report name '& rept &' you entered is misspelled or refers to a report that isn't open or doesn't exist. If mouse over the rept in debug mode it show &quot;thereport&quot; so it is recognizing the name correctly, but the formating or something is off.
Thanks in advance!!