Set Control Source of a Report Field on Opening 1

[Bry12345]

I am trying to set the Control Source of a particular field of a report as the report is opening. I suspect that there is a way to do it as part of the DoCmd.OpenReport argument, but can't find any syntax examples.

The report is printed from a set of Command Buttons on a form, each of which will print the same report, with the 'Barcode' field on the Report having it's Control Source set to Barcodex, depending on which Command Button calls the report. There are 10 Barcode fields on the form, and it would be sloppy to replicate the report 10 times. The code also picks up the current record from the 'ID' field.

I have tried setting the Control Source in a separate line of code, but get errors because the report is either not open or has already opened (see below). This report will eventually print directly to a label printer (it is only temporarily set to acPrintPreview), so I'm hesitant to try opening the report hidden, then setting the control source and refreshing it, because I'm thinking that this may cause unwanted labels to be spit out.

Here is the code:
- - -
Private Sub cmdPrintBarcode1_Click()

Dim strReportName As String
Dim varCriteria As Variant

strReportName = "rptPrintLabel"
'Set variable to pick current record
varCriteria = "[ID]=" & Me![ID] & ""

'Next line causes 'Report not open error'
Reports!rptPrintLabel!Barcode.ControlSource = Barcode1

DoCmd.OpenReport strReportName, acViewPreview, , varCriteria

'Next line causes 'Can't change after report open error'
Reports!rptPrintLabel!Barcode.ControlSource = Barcode1

End Sub
- - -
Using Access 2000

Thanks! - - - -

Bryan

 

[BSman]

You could try a different approach: set up a query for each data source for the report where the field for the barcode (the data you want to change) being defined with the same name in each query (i.e., TheBarcode: [Barcode1], for the next query it would be TheBarcode: [Barcode2]).

Then when the report opens you can set the source for the report to the correct query.

 

[Bry12345]

Thanks for the response. I've got the general idea, but I need to refine it.

I was able to design a query selecting the 3 needed fields (and with Barcodex As Barcode) and get the report to print properly with the Record Source of the report hard coded to the proper query.

What I need to do, I think, is to define the a SQL string in the On Click event of the button that will print the label (i.e. "Dim strSQL' and then do a 'strSQL =' ), and then assign the Record Source of the report to 'strSQL'.

So now the On Click Event code looks like this:

- - -
Dim strReportName As String

Dim strSQL

strReportName = "rptPrintLabel"

strSQL = "SELECT tblMain.ID, tblMain.FileNumber, tblMain.Barcode2 as Barcode FROM tblMain" & _
" WHERE (((tblMain.ID)=[Forms]![frmBeta].[ID]))"

DoCmd.OpenReport strReportName, acViewPreview
- - -

What I'm having a problem with now is how do I dynamically set the Record Source of the report to the strSQL.

Thanks!
- - - -

Bryan

 

[BSman]

You can't set strSQL as the source. You need an actual query (or table).

Rather than dynamically defining a query, set up 10 queries, each of which uses the right data column for your barcode.

For the onOpen property of the report, set the report's record source to the appropriate query. One way to do this is to define a module that contains the following variable definition:

Public strReportSource as String

Then before you actually open the report, set strReportSource to the name of the query to use for the report (ex: Query2). For the OnLoad or OnOpen property of the report, do the following:

me.RecordSource = strReportSource

 

[Bry12345]

Thanks for your patience . .this is killing me . .

I was trying to get around defining individual queries, but I understand that the report has to be bound to a table or query. I'm tempted just to define 10 separate reports, which is parallel to defining 10 queries. I'm trying to understand your logic, though, so let me ask one more question.

I've defined the queries and have set the Record Source of the report to the variable (strReportSource) in the OnOpen Event. However, I'm having trouble following your logic in where you are assigning the specific query to the variable.

I understand the assignment, i.e.
strReportSource = "qryPrintBarcodex"
but I'm not following your suggestion regarding using the Public module. I must be missing something, but it seems to me that I need to assign the value to strReportSource as part of the OnClick event (Form Module) of each of the buttons that will print the report. I tried making this Sub Public, however, the report is printing without being bound to a Record Source.

Your time is appreciated! - - - -

Bryan

 

[Bry12345]

I've got it.

Defining the variable as Global did the trick.

Thanks for your help! - - - -

Bryan

 

[lameid]

Check out
thread703-478606
For correct syntax.

But honestly I don't see why you couldn't just set the control source for the control on the on open event...

Me!Control.Properties("Controlsource&quot = Field1

This might error out but I think it would work. If not, you could just in the on open event set your recordsource to a literal SQL string and concatenate the correct field into the SQL statement... I hope I didn't lose you. Let me know if you need more help.

 

[Bry12345]

Thanks. I postrf to you on the thread you referenced.

Thread703-478606

I am trying to do what you suggested regarding setting the recordsource using SQL, but I was unsucessful in doing it.

I know how to define the sql to a variable . .
i.e. strSQL = "SELECT blah"

but I don't know how to set the recordsource for the report to that variable.

I'll stay on this thread for followup.
- - - -

Bryan

 

[Bry12345]

Just saw your reply in Thread703-478606.

Disregard answering my question here . I'll go to the other thread. - - - -

Bryan

 

[lameid]

Same way as
Thread703-478606

On open event:

Me.recordsource = strSQL
or use the global variable and store the SQL in there on the form.

If this is all the same problem, I would really lean towards using a global variable to determine the correct control source like the recordsource in the other thread and set it like I mentioned above in this thread. Only if that doesn't work would I full with concatenating SQL. Sure you'll learn SQL trial by fire but it is a pain.

 

[lameid]

Oops stupid homonyms... I used full instead of fool. And in saying fool I mean put forth unnecessary effort.