Re: Pass an Access field as a parameter in link to SSRS or ASP?

[Rich P]

(this is way easier in VB.Net -- but here is how to do it from Access)

'------------------------------------------------
Public Declare Function apiShellExecute Lib "shell32.dl l" _
Alias "ShellExecu teA" _
(ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) _
As Long

'***App Window Constants***
Public Const WIN_NORMAL = 1 'Open Normal
Public Const WIN_MAX = 3 'Open Maximized
Public Const WIN_MIN = 2 'Open Minimized

'***Error Codes***
Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_ME M = 0&
Private Const ERROR_FILE_NOT_ FOUND = 2&
Private Const ERROR_PATH_NOT_ FOUND = 3&
Private Const ERROR_BAD_FORMA T = 11&

Sub RunReport()
Dim url As String, lRet As Long
url =
"http://yourServer/ReportServer?/rptDir/reportName&rs:F ormat=excel&Rec or
dID=10007&UserI D=steve"

lRet = apiShellExecute (hWndAccessApp, vbNullString, _
url, vbNullString, vbNullString, WIN_NORMAL)
End Sub

after ...rs:Format=ex cel... I have 2 parameters that I pass to my report
-- a recordID and a UserName. That is how you pass the parameters to
sql server reporting services from Access. It is actually the same in
VB.Net except you don't have to deal with the API function. I just use
a .Net web browser control in place of the API.
Rich

*** Sent via Developersdex http://www.developersdex.com ***

[simpleton via AccessMonster.com]

Re: Pass an Access field as a parameter in link to SSRS or ASP?

Awesome!! Thanks!

I'm getting pretty good with Access... But I'm very new to VB.
Do I need to save this as a "Module"?
Also, how to I call up this link? Set it as a button from my form?
How do I get my parameter to pass into that link?

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200808/1

[Rich P]

Re: Pass an Access field as a parameter in link to SSRS or ASP?

I would place the API declaration in a Standard Module. This way you
can use the API function throughout the mdb. The rest of the code you
could put in a button on a form.

As for the URL - this is just a string. You build the url string. The
number of parameters is based on the query used to run the Sql Server
Reporting Services Report (rdl). You can have a textbox on your form in
Access for each parameter. The user fills in the textboxes then when
the User presses the button -- you build the url string like this:

'------------------------------------
Private Sub cmd1_click()
dim url as string

url =
"http://yourServer/ReportServer?/rptDir/reportName&rs:F ormat=excel&Rec or
did=" & txtRecordID & "&userid=" & txtUserID

lRet = apiShellExecute (hWndAccessApp, vbNullString, _
url, vbNullString, vbNullString, WIN_NORMAL)
End Sub
'------------------------------------

be careful when creating the url because you will notice several "&"
symbols. The "&" inside the quotes belongs to the url. the other &
symbols are string concatenators for txtRecordID and txtUserID


Rich

*** Sent via Developersdex http://www.developersdex.com ***

[simpleton via AccessMonster.com]

Re: Pass an Access field as a parameter in link to SSRS or ASP?

Rich,
I really appreciate your help.
I setup the first one as a module, and then made the second one for my button.


I keep getting this "Compile Syntax" error though.
I'm so out of my league..

I created another "test" db to see if i could get the button to work..
But it keeps giving me the error.

http://img171.imageshack.us/img171/1299/linkingthroughaccesszr8.jpg

Thoughts?
Am I close?

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200808/1

[Rich P]

Re: Pass an Access field as a parameter in link to SSRS or ASP?

Yes. Thanks for the pic - problem with the url string. I broke the
string apart so this should compile.

'------------------------------------
Private Sub cmd1_click()
dim url as string

url = "http://yourServer/ReportServer?/rptDir/" _
& "reportName&rs: Format=excel&Re cordid=" _
& txtRecordID & "&userid=" & txtUserID

lRet = apiShellExecute (hWndAccessApp, vbNullString, _
url, vbNullString, vbNullString, WIN_NORMAL)
End Sub
'------------------------------------

Note: replace yourServer with the name of your actual server. And
replace rptDir with the name of the actual report directory that the rdl
gets published to. From the Reporting Services designer you can go to
Project properties and see where the rdl gets published to. From
Project/Properties copy the TargetServerURL text and also copy the
TargetFolder text

http://yourServer/ReportServer?/rptDir/

separate the TargetServerURL From the Target folder with "/" forward
slashes. Then copy the name of your report (rdl)

http://yourServer/ReportServer?/rptDir/yourRpt

then add
"http://yourServer/ReportServer?/rptDir/yourRpt
&rs:Format=exce l&param1=12345& param2=somthing "

I wouldn't even bother using textboxes right now. Just hardcode some
known parameters and see if it works.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[simpleton via AccessMonster.com]

Re: Pass an Access field as a parameter in link to SSRS or ASP?

Rich,
It worked!! You are awesome.

I got the parameter pulling from a text box too.
Man, I worked for hours (crazy right?) on this yesterday.. and got nowhere.

Thank you soooo much!!


Know any good resources to learn about this kind of programming?

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200808/1

[simpleton via AccessMonster.com]

Re: Pass an Access field as a parameter in link to SSRS or ASP?

Grrrr!!!

So now, If I try to put a button like this into my live Access DB.. It keeps
giving me "Compile Error: Variable not Defined."

http://img529.imageshack.us/img529/2892/reportpull2yp1.jpg

However, If i create a NEW form.. and put the same info in there.. the
report/link opens just fine.

I gotta be doing something wrong here. :(

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200808/1

[Stuart McCall]

Re: Pass an Access field as a parameter in link to SSRS or ASP?

"simpleton via AccessMonster.c om" <u45284@uwewrot e in message
news:8850d1ba82 a79@uwe...

Grrrr!!!
>
So now, If I try to put a button like this into my live Access DB.. It
keeps
giving me "Compile Error: Variable not Defined."

http://img529.imageshack.us/img529/2892/reportpull2yp1.jpg

>
However, If i create a NEW form.. and put the same info in there.. the
report/link opens just fine.
>
I gotta be doing something wrong here. :(
>
--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200808/1

>

You need to define lRet:

Dim lRet As Long

inside the procedure.

[simpleton via AccessMonster.com]

Re: Pass an Access field as a parameter in link to SSRS or ASP?

Aah, Perfect.

Thanks!! Now I'm working again.

--
Message posted via AccessMonster.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200808/1