Assign control source in code

  • Thread starter Thread starter hollyylloh
  • Start date Start date
H

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.
 
hollyylloh said:
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.
Click to expand...

You could change the SQL string in qryABC instead:

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

then open the report as usual.
 
hollyylloh said:
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.
Click to expand...


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
 
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!
 
hollyylloh said:
Stuart,

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

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).
Click to expand...

I don't think one or the other has the advantage. Just two ways to achieve
the same goal.
 
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.
 
Back
Top