By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

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

P: n/a
(this is way easier in VB.Net -- but here is how to do it from Access)

'------------------------------------------------
Public Declare Function apiShellExecute Lib "shell32.dll" _
Alias "ShellExecuteA" _
(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_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

Sub RunReport()
Dim url As String, lRet As Long
url =
"http://yourServer/ReportServer?/rptDir/reportName&rs:Format=excel&Recor
dID=10007&UserID=steve"

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

after ...rs:Format=excel... 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 ***
Aug 5 '08 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 6 '08 #2

P: n/a
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:Format=excel&Recor
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 ***
Aug 6 '08 #3

P: n/a
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/1...haccesszr8.jpg
Thoughts?
Am I close?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 6 '08 #4

P: n/a
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&Recordid=" _
& 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=excel&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 ***
Aug 6 '08 #5

P: n/a
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.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 7 '08 #6

P: n/a
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/2...rtpull2yp1.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.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 7 '08 #7

P: n/a
"simpleton via AccessMonster.com" <u45284@uwewrote in message
news:8850d1ba82a79@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/2...rtpull2yp1.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.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1
You need to define lRet:

Dim lRet As Long

inside the procedure.
Aug 7 '08 #8

P: n/a
Aah, Perfect.

Thanks!! Now I'm working again.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200808/1

Aug 7 '08 #9

This discussion thread is closed

Replies have been disabled for this discussion.