Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Assign the recordsource to report at runtime

607 views
Skip to first unread message

Tim

unread,
Mar 30, 2010, 4:33:01 PM3/30/10
to
Here is the code ...
strSQL = "SELECT tblStudents.StudID, tblStudents.SName WHERE
tblStudents.StudID= '" & glbStudID & "'
Set rst = MyDatabase.OpenRecordset(strSQL)

How can I assign a recordsource to my report, rptStudents at runtime? Note:
I cannot use Me!ReportName because this is a sub procedure. It's called by
another sub procedure. Thank you

ruralguy via AccessMonster.com

unread,
Mar 30, 2010, 5:24:43 PM3/30/10
to
You can assign the RecordSource of a report in the Open event of the Report.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201003/1

Tim

unread,
Mar 31, 2010, 9:14:04 AM3/31/10
to
Thanks for response. What I try to do is I want to capture the global
gblStudId and then pass it to a sub procedure. From this sub procedure, I
pull out a record source that matches gblStudId. And yes, I did use the code
as you mentioned as following.

DoCmd.OpenReport rptStudents, acViewPreview,,,,rst

And then, in the Open event of the report, I put this following code.

Private Sub Report_Open(Cancel As Integer)
Me.RecordSource = rst.NAME
End Sub

STILL DOESN'T WORK! Do you know why? Thank you!
---------

"ruralguy via AccessMonster.com" wrote:

> .
>

Douglas J. Steele

unread,
Mar 31, 2010, 10:41:02 AM3/31/10
to
What version of Access? You could try

Private Sub Report_Open(Cancel As Integer)

Set Me.RecordSet = rst
End Sub


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Tim" <T...@discussions.microsoft.com> wrote in message
news:569992EC-FCAF-4332...@microsoft.com...

Tim

unread,
Mar 31, 2010, 12:06:01 PM3/31/10
to
Thanks! I use Access 2007. I change to your below code, but it pops up an
error saying "An expression you entered is the wrong data type for one of the
arguments. 2498". HELP!

"Douglas J. Steele" wrote:

> .
>

Douglas J. Steele

unread,
Mar 31, 2010, 1:39:31 PM3/31/10
to
How have you declared rst? How are you instantiating it?

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Tim" <T...@discussions.microsoft.com> wrote in message

news:DA3B438B-EA53-49C6...@microsoft.com...

Tim

unread,
Apr 1, 2010, 8:42:01 AM4/1/10
to
Hi Douglas,
I declare it as a global variable.
Dim strSQL as string
Dim rst As DAO.Recordset
Dim myworkspace as DAO.Workspace
Dim DB as DAO.database

Set my workspace = DBEngine.Workspace(0)
Set DB = CurrentDb

strSQL = "SELECT tblStudents.StudID, tblStudents.SName WHERE
tblStudents.StudID= '" & glbStudID & "'
Set rst = MyDatabase.OpenRecordset(strSQL)

Do you see any thing wrong with it? Thanks


"Douglas J. Steele" wrote:

> .
>

Douglas J. Steele

unread,
Apr 1, 2010, 8:55:40 AM4/1/10
to
The only thing I see that looks incorrect is

strSQL = "SELECT tblStudents.StudID, tblStudents.SName WHERE
tblStudents.StudID= '" & glbStudID & "'

Your quotes are wrong at the end

strSQL = "SELECT tblStudents.StudID, tblStudents.SName WHERE
tblStudents.StudID= '" & glbStudID & "'"

However, you'd get an error trying to run what you posted, so that can't be
the issue.

I suspect the issue is

DoCmd.OpenReport rptStudents, acViewPreview,,,,rst

You're trying to pass rst as the OpenArgs parameter: I believe OpenArgs must
be a text value. However, since rst is a global variable, you shouldn't need
to be passing it anyhow. Try simply using

DoCmd.OpenReport rptStudents, acViewPreview

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Tim" <T...@discussions.microsoft.com> wrote in message

news:C7247712-A814-4897...@microsoft.com...

Tim

unread,
Apr 1, 2010, 10:23:02 AM4/1/10
to
I removed rst and simply put
DoCmd.OpenReport rptStudents, acViewPreview

and it does show up the report. However, at the report's Open event, the
error message, "run-time error '32585': This feature is only available in
ADP".

Private Sub Report_Open(Cancel As Integer)
Set Me.RecordSet = rst
End Sub

I did comment out these codes, then there is no error message anymore, but
the report is blank :=(. Maybe I should write a different code for the report
open event?

Douglas J. Steele

unread,
Apr 1, 2010, 1:45:50 PM4/1/10
to
Hmm. I wonder when they changed the behaviour.

Since I wasn't involved with this thread from the beginning, what's the
reason for not just setting the report's RecordSource property to the SQL
that generates the recordset?

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele
(no e-mails, please!)

"Tim" <T...@discussions.microsoft.com> wrote in message

news:131AA2E6-E2A5-4CD9...@microsoft.com...

Tim

unread,
Apr 1, 2010, 3:42:01 PM4/1/10
to
Good question. Well, my user wants to have a button on a first form. When
clicking, another form will be sent automatically to his teacher (I converted
this form to a report). The StudID from the first form will be passed to the
second form (or rpt). This StudId is the key to pull out the recordset for
the report at run-time. The process will continue to the next StudId. I hope
it makes sense.

Douglas J. Steele

unread,
Apr 1, 2010, 5:31:38 PM4/1/10
to
The OpenReport method lets you specify a filter. Try that route instead.

--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele
(no private e-mails, please)


"Tim" <T...@discussions.microsoft.com> wrote in message

news:297E1C05-B7F6-4304...@microsoft.com...

Tim

unread,
Apr 5, 2010, 3:27:17 PM4/5/10
to
I tried "where condition" with the global variable and look likes it works!
Thanks for help! I appreciate it! Here comes another error message, "The
operation failed because of network or other communicatioin problems. Verify
your connections and try again". And then the program closed itself after
making a backup automatically. Why does it happen?


"Douglas J. Steele" wrote:

> .
>

Douglas J. Steele

unread,
Apr 5, 2010, 5:13:56 PM4/5/10
to
Flaky NIC?

(Hopefully you're not trying to use wireless...)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Tim" <T...@discussions.microsoft.com> wrote in message

news:CC9B3286-6CDD-43CD...@microsoft.com...

Tim

unread,
Apr 6, 2010, 8:46:02 AM4/6/10
to
No idea what you're talking about. No, I use LAN. I guess maybe the network
problem, i.e., the way the network guy configured the outlook for the
security reason, so can't email from the other office application like
Accesss ???

"Douglas J. Steele" wrote:

> .
>

ruralguy via AccessMonster.com

unread,
Apr 6, 2010, 9:47:14 AM4/6/10
to
NIC = Network Interface Card

Tim wrote:
>No idea what you're talking about. No, I use LAN. I guess maybe the network
>problem, i.e., the way the network guy configured the outlook for the
>security reason, so can't email from the other office application like
>Accesss ???
>

>> Flaky NIC?
>>
>[quoted text clipped - 53 lines]
>>
>> .

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201004/1

0 new messages