[AccessD] change control source via vba

Hello Jim,
I may have not been clear on this.
I needed to set the source for a certain unbound control, not the report
itself which has a recordsource.
I think I got it now.

Thanks for taking the time to respond.

bob



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Lawrence
Sent: Sunday, September 10, 2006 9:19 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] change control source via vba


Hi Bob:

It is very simple:

1. In the Modules section create a public function that say calls
a Form and/or sets a variable. In a form multiple option would be
available.  What ever way decided select an appropriate data sources.


Public Sub SetTheReportDataSource(rptCallerReport as Report)

	'Note: Public Variable
	strSelectedReprtTableSourceName = ""

	' You could do multiple menu forms and select your own source
      ' or just one allow record source to be selected....
	Select Case rptCallerReport.Name
	  Case "ReportName1"
	    ...
          DoCmd.OpenForm "ReportSourceSelectorMenu"

		Or

	    strSelectedReprtTableSourceName = "MyTableSource"
	    ...

	  Case "ReportName2"
	    ...

	End Select

	'if change report data source name
	If strSelectedReprtTableSourceName <> "" Then
		rptCallerReport.RecordSource =
strSelectedReprtTableSourceName
	End if

End Sub

2. Go into design mode of the report and select the event tab, of the report
properties. Right-mouse-click the 'On Open' event and select 'Build'.

Private Sub Report_Open(Cancel As Integer)

	'From the Report...
	SetTheReportDataSource [Reports]![ReportName1]

End Sub

This was done quickly from memory but it should and does work.

HTH
Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bob Heygood
Sent: Sunday, September 10, 2006 4:37 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] change control source via vba

Does anyone have a code snip that will show me how to change a report
control from being unbound to one of the fields in the record source of the
report??
This needs to be done either when opening the report or from code I run
before opening.


TIA

bob



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com