Change Record Source

[Tara]

I'm trying to change the RecordSource of a report based on the value given in
a combobox, but it's not working. Any advice to get me pointed in the right
direction would be greatly appreciated!

Here's the code I have now:

Private Sub Report_Open(Cancel As Integer)

If Forms!frmReportType!cboReport = "Original" Then
Me.RecordSource = qryBilling

If Forms!frmReportType!cboReport = "Amended" Then
Me.RecordSource = qryBillingAMENDED

End If

End If

End Sub

 

[Jeff Boyce]

Tara

When you use:

Me.xxxxxx

you are referring to the object in which the code runs. If the name of your
code (Report_Open) is accurate, you are attempting to do this in a report.

But I believe you need to use quotes around the name of the source -- e.g.,
"qryBilling".

You don't mention what isn't working ... and "it's not working" doesn't
really give us much to go on for diagnostics...

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Tara]

Thanks for looking into this Jeff. Yes, I am attempting to do this as the
report opens. Is that the wrong time to run the code?

By not working, I mean that the report record source is not changing. It
was originally set up with one of the sets of data that I refer to in the
combo box as it's record source. We decided we needed more flexibility, so
that's why I'm trying to change the record source on demand. But no matter
which option is chosen in the combo box, the report still loads with the
original record source.

 

[Steve]

Tara,

Try the following code and post what result you get:

Select Case Forms!frmReportType!cboReport
Case "Original"
Me.RecordSource = "qryBilling"
Case "Amended"
Me.RecordSource = "qryBillingAMENDED"
Case Else
MsgBox "Have A Good Day, Tara"
End Select

Steve
(e-mail address removed)

 

[Duane Hookom]

Jeff suggested you place quotes around your query names. Did you try this? If
not, give it a whirl and let us know the results. If it still doesn't work,
come back with your code.

 

[Tara]

I put the quotes around the query names, but it didn't make a difference.
The code is the same as I first posted, except now I have the quotes in
place.

 

[Tara]

It worked perfectly...Thank you!

Would you mind explaining why your solution worked while my attempt didn't?
I'm really trying to increase my knowledge about this type of thing.

 

[Steve]

First of all, no matter what, when you assign a value to the recordsource
property, the value must be expressed as a string. You did not so whatever
your code was, it could not work.

Second the logic is wrong in your code. In an If statement, what follows the
keyword "Then" only executes if what follows the "If" keyword is true. If it
is false, execution IMMEDIATELY jumps to "End If". Now let's take a look at
your code.

If you choose "Original" in the combobox, the statement is True. So
Me.RecordSource = qryBilling executes. Also since the "Amended" If statement
immediately follows before the "End If" of the "Original" if statement, the
"Amended" if statement also executes. The "Amended" if statement has to be
False because you chose "Original" so the "Amended" if statement immediately
jumps to the upper "End If". Executes then proceeds to the bottom "End If".
At the conclusion of execution of code, the record source of your report is
qryBilling.

If you choose "Amended" in the combobox, the statement is False. So the
"Original" if statement immediately jumps to the lower "End If". The
"Amended" If statement is totally bypassed and is not executed. At the
conclusion of execution of code, the record source of your report is
whatever is set in the properties of the report.


If you wanted to use the If/Then construct to set the recordsource, your
code needs to look like:

If Forms!frmReportType!cboReport = "Original" Then
Me.RecordSource = "qryBilling"
ElseIf If Forms!frmReportType!cboReport = "Amended" Then
Me.RecordSource = "qryBillingAMENDED"
Else
MsgBox "The Value Of The ComboBox Is Not Original Or Amended"
End If

Steve
(e-mail address removed)

Tara, I provide help with Access, Excel and Word applications for a small
fee. I specialize in fixing problems in existing applications, modifying
existing applications and adding new functionality to existing applications.
If ever you need immediate help, contact me.

 

[John... Visio MVP]

Steve

Tara, I provide help with Access, Excel and Word applications for a small
fee. I specialize in fixing problems in existing applications, modifying
existing applications and adding new functionality to existing
applications. If ever you need immediate help, contact me.

Stevie, you are still a loser.

Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

Please do not feed the trolls.

John... Visio MVP

 

[John... Visio MVP]

It worked perfectly...Thank you!

Would you mind explaining why your solution worked while my attempt
didn't?
I'm really trying to increase my knowledge about this type of thing.

"Steve" wrote:

You had the second condition inside the first. I have indented your code so
that it is more obvious.

If Forms!frmReportType!cboReport = "Original" Then
Me.RecordSource = qryBilling
If Forms!frmReportType!cboReport = "Amended" Then
Me.RecordSource = qryBillingAMENDED
End If
End If

Be careful of steve he is our local troll who preys on unsuspecting posters.
These neswgroups are provided by MS for FREE help. stevie does not
understand that and plys his questionable skills for unreasonable prices.

John... Visio MVP