I have been assigning the control source to my reports in code like this:

If Me!ClientID = "ABC" Then
DoCmd.OpenReport "rptABC", acViewDesign
Reports!rptABC.RecordSource = "qryABC"
DoCmd.Save acReport, "rptABC"
DoCmd.OpenReport "rptABC", acViewPreview
Else...

However, I need to convert the database to an mde file, thus removing the
ability to go into design view. Is there another way to assign the control
source of a report in code? This way will not work with an mde file because
it tries to enter design view.

Thank you in advance.

"hollyylloh" <(E-Mail Removed)> wrote in message
news:35109FF8-CB03-424F-B22F-(E-Mail Removed)...
>I have been assigning the control source to my reports in code like this:
>
> If Me!ClientID = "ABC" Then
> DoCmd.OpenReport "rptABC", acViewDesign
> Reports!rptABC.RecordSource = "qryABC"
> DoCmd.Save acReport, "rptABC"
> DoCmd.OpenReport "rptABC", acViewPreview
> Else...
>
> However, I need to convert the database to an mde file, thus removing the
> ability to go into design view. Is there another way to assign the control
> source of a report in code? This way will not work with an mde file
> because
> it tries to enter design view.
>
> Thank you in advance.

You could change the SQL string in qryABC instead:

With CurrentDb.QueryDefs!qryABC
.SQL = "<whatever>"
End With

then open the report as usual.

"hollyylloh" <(E-Mail Removed)> wrote in message
news:35109FF8-CB03-424F-B22F-(E-Mail Removed)...
>I have been assigning the control source to my reports in code like this:
>
> If Me!ClientID = "ABC" Then
> DoCmd.OpenReport "rptABC", acViewDesign
> Reports!rptABC.RecordSource = "qryABC"
> DoCmd.Save acReport, "rptABC"
> DoCmd.OpenReport "rptABC", acViewPreview
> Else...
>
> However, I need to convert the database to an mde file, thus removing the
> ability to go into design view. Is there another way to assign the control
> source of a report in code? This way will not work with an mde file
> because
> it tries to enter design view.


Pass the desired recordsource to the report via OpenArgs:

DoCmd.OpenReport "rptABC", acViewPreview, _
OpenArgs:="qryABC"

In the report's Open event, get the value of OpenArgs and assign it to the
RecordSource property:

Private Sub Report_Open(Cancel As Integer)

Dim stArgs As string

strArgs = Me.OpenArgs & vbNullString

If Len(strArgs) > 0 Then
Me.RecordSource = strArgs
End If

End Sub


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

Dirk,

Thank you so much! That worked and is so much simpler.

"Dirk Goldgar" wrote:

> "hollyylloh" <(E-Mail Removed)> wrote in message
> news:35109FF8-CB03-424F-B22F-(E-Mail Removed)...
> >I have been assigning the control source to my reports in code like this:
> >
> > If Me!ClientID = "ABC" Then
> > DoCmd.OpenReport "rptABC", acViewDesign
> > Reports!rptABC.RecordSource = "qryABC"
> > DoCmd.Save acReport, "rptABC"
> > DoCmd.OpenReport "rptABC", acViewPreview
> > Else...
> >
> > However, I need to convert the database to an mde file, thus removing the
> > ability to go into design view. Is there another way to assign the control
> > source of a report in code? This way will not work with an mde file
> > because
> > it tries to enter design view.
>
>
> Pass the desired recordsource to the report via OpenArgs:
>
> DoCmd.OpenReport "rptABC", acViewPreview, _
> OpenArgs:="qryABC"
>
> In the report's Open event, get the value of OpenArgs and assign it to the
> RecordSource property:
>
> Private Sub Report_Open(Cancel As Integer)
>
> Dim stArgs As string
>
> strArgs = Me.OpenArgs & vbNullString
>
> If Len(strArgs) > 0 Then
> Me.RecordSource = strArgs
> End If
>
> End Sub
>
>
> --
> Dirk Goldgar, MS Access MVP
> Access tips: www.datagnostics.com/tips.html
>
> (please reply to the newsgroup)
>

Stuart,

Interesting, so in this way I create one query and then store the multiple
SQL strings that I need in code, correct?

I am curious what are the advantages, disadvantages of each of these two
approaches (Stuarts and Dirks).

Thank you!

"Stuart McCall" wrote:

> "hollyylloh" <(E-Mail Removed)> wrote in message
> news:35109FF8-CB03-424F-B22F-(E-Mail Removed)...
> >I have been assigning the control source to my reports in code like this:
> >
> > If Me!ClientID = "ABC" Then
> > DoCmd.OpenReport "rptABC", acViewDesign
> > Reports!rptABC.RecordSource = "qryABC"
> > DoCmd.Save acReport, "rptABC"
> > DoCmd.OpenReport "rptABC", acViewPreview
> > Else...
> >
> > However, I need to convert the database to an mde file, thus removing the
> > ability to go into design view. Is there another way to assign the control
> > source of a report in code? This way will not work with an mde file
> > because
> > it tries to enter design view.
> >
> > Thank you in advance.
>
> You could change the SQL string in qryABC instead:
>
> With CurrentDb.QueryDefs!qryABC
> .SQL = "<whatever>"
> End With
>
> then open the report as usual.
>
>
> .
>

"hollyylloh" <(E-Mail Removed)> wrote in message
news:141A1F59-3E34-4E63-8A24-(E-Mail Removed)...
> Stuart,
>
> Interesting, so in this way I create one query and then store the multiple
> SQL strings that I need in code, correct?

Yep, that's one way to do it. Just knowing of the technique could be useful
to you sometime.

>
> I am curious what are the advantages, disadvantages of each of these two
> approaches (Stuarts and Dirks).

I don't think one or the other has the advantage. Just two ways to achieve
the same goal.

>
> Thank you!
>
> "Stuart McCall" wrote:
>
>> "hollyylloh" <(E-Mail Removed)> wrote in message
>> news:35109FF8-CB03-424F-B22F-(E-Mail Removed)...
>> >I have been assigning the control source to my reports in code like
>> >this:
>> >
>> > If Me!ClientID = "ABC" Then
>> > DoCmd.OpenReport "rptABC", acViewDesign
>> > Reports!rptABC.RecordSource = "qryABC"
>> > DoCmd.Save acReport, "rptABC"
>> > DoCmd.OpenReport "rptABC", acViewPreview
>> > Else...
>> >
>> > However, I need to convert the database to an mde file, thus removing
>> > the
>> > ability to go into design view. Is there another way to assign the
>> > control
>> > source of a report in code? This way will not work with an mde file
>> > because
>> > it tries to enter design view.
>> >
>> > Thank you in advance.
>>
>> You could change the SQL string in qryABC instead:
>>
>> With CurrentDb.QueryDefs!qryABC
>> .SQL = "<whatever>"
>> End With
>>
>> then open the report as usual.
>>
>>
>> .
>>

Thanks Stuart, I will add that to my arsenal. I think the example Dirk gave
me makes better sense with this particular task. I look forward to using your
method in the future.

"Stuart McCall" wrote:

> "hollyylloh" <(E-Mail Removed)> wrote in message
> news:141A1F59-3E34-4E63-8A24-(E-Mail Removed)...
> > Stuart,
> >
> > Interesting, so in this way I create one query and then store the multiple
> > SQL strings that I need in code, correct?
>
> Yep, that's one way to do it. Just knowing of the technique could be useful
> to you sometime.
>
> >
> > I am curious what are the advantages, disadvantages of each of these two
> > approaches (Stuarts and Dirks).
>
> I don't think one or the other has the advantage. Just two ways to achieve
> the same goal.
>
> >
> > Thank you!
> >
> > "Stuart McCall" wrote:
> >
> >> "hollyylloh" <(E-Mail Removed)> wrote in message
> >> news:35109FF8-CB03-424F-B22F-(E-Mail Removed)...
> >> >I have been assigning the control source to my reports in code like
> >> >this:
> >> >
> >> > If Me!ClientID = "ABC" Then
> >> > DoCmd.OpenReport "rptABC", acViewDesign
> >> > Reports!rptABC.RecordSource = "qryABC"
> >> > DoCmd.Save acReport, "rptABC"
> >> > DoCmd.OpenReport "rptABC", acViewPreview
> >> > Else...
> >> >
> >> > However, I need to convert the database to an mde file, thus removing
> >> > the
> >> > ability to go into design view. Is there another way to assign the
> >> > control
> >> > source of a report in code? This way will not work with an mde file
> >> > because
> >> > it tries to enter design view.
> >> >
> >> > Thank you in advance.
> >>
> >> You could change the SQL string in qryABC instead:
> >>
> >> With CurrentDb.QueryDefs!qryABC
> >> .SQL = "<whatever>"
> >> End With
> >>
> >> then open the report as usual.
> >>
> >>
> >> .
> >>
>
>
> .
>