Assign control source in code

[hollyylloh]

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.

 

[Stuart McCall]

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.

 

[Dirk Goldgar]

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

 

[hollyylloh]

Dirk,

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

 

[hollyylloh]

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]

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.

 

[hollyylloh]

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.