[AccessD] change control source via vba

Jim Lawrence accessd at shaw.ca
Sun Sep 10 23:18:49 CDT 2006


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




More information about the AccessD mailing list