Dynamically changing a report's recordsource property

[Ben]

Hi all,

I have a report set up in Access 2003. However, I need to be able to change
the reports recordsource property during runtime. I have an SQL set up in
its' recordsource property and I need to change one condition in the sql
based on different user input. It's the same report but display varies based
on different criteria.

Can you show me the correct VBA syntax?

Thanks,

Ben
--

 

[Jim Burke in Novi]

In the report's Open event procedure just put:

Me.RecordSource = SQLText

where 'SQLText' would be a variable that has the SQL statement (or just put
the actual SQL if you want, though using a variable is the 'accepted' way of
doing it, e.g. if you just want the SQL SELECT statement itself there then put

Me.RecordSource = "SELECT x FROM y...."

 

[dymondjack]

In the report's Open event procedure just put:

Me.RecordSource = SQLText

In addition, the Recordsource property of a report can only be set in the
Open event, unlike forms, where they can be set wherever.


--
Jack Leach
www.tristatemachine.com

- "First, get your information. Then, you can distort it at your leisure."
- Mark Twain

 

[Marshall Barton]

I have a report set up in Access 2003. However, I need to be able to change
the reports recordsource property during runtime. I have an SQL set up in
its' recordsource property and I need to change one condition in the sql
based on different user input. It's the same report but display varies based
on different criteria.

Please don't multipost questions. (See Netiquette section
at http://www.mvps.org/access/)

This one was ansered in another newsgroup.

 

[Ben]

Marshall,

I didn't tried to cross post. I had initially posted the question from
my newsreader (Eudora) in the wrong forum. I didn't know if there was a
way to recall that message. So I decided to post the same question on
the Access.programming forum directly through MS's newsgroup.

I appreciate your feedback, but please, honest mistake do happen. I am
not perfect...

Ben

 

[Ben]

Jim, Jack -

Thanks for sharing your thoughts. My initial thoughts that I could set
the recordsource property, rather than just in the open event.

Thanks again.

Ben

 

[Marshall Barton]

I didn't tried to cross post. I had initially posted the question from
my newsreader (Eudora) in the wrong forum. I didn't know if there was a
way to recall that message. So I decided to post the same question on
the Access.programming forum directly through MS's newsgroup.

I appreciate your feedback, but please, honest mistake do happen. I am
not perfect...

Sure they do and none of us are. But, multi posting can be
irritating to anyone trying to follow the train of thought
in two different forums so it will be pointed out if for no
other reason than to warn others. This time, only one
person wasted their time so no big deal. I see that you got
essentially the same answer in both places so your issue
should be resolved.

FYI - Cross posting is when you send the same question to
multple newsgroups using a single post (by putting all the
newsgroups in the To: field). Multi posting is when you
post multiple times using a different newsgroup in each
post. Cross posting is acceptable (though rarely needed)
because everyone in the different forums can see all the
replies regardless of which forum they monitor.