How to change controlsource of multiple textboxes with public function (1 Viewer)

G

galaxy

Registered User.
Local time
Today, 02:57
Joined
Feb 26, 2013
Messages
16
I have multiple reports that use similar IIF statements as the controlsource for four textboxes. Naturally, I don't want to have to update twelve controlsources if any of the calculations change, so I thought I'd make this a public function. However, I don't know how to pass along multiple textboxes as variables. Here's what I have so far:
Code: 
Option Compare Database
Public Function txtColor(ByRef textbox As Control)
Dim str1, str2, str3, str4 As String
'The IIf statement is simplified for this example. It's not important.
str1 = "=IIf(IsNull([Inquiry start date]),'W',IIf([txtInquiry]<1 And IsNull([Inquiry end date]),'R'))"
str2 = Replace(str1, "Inquiry", "Design")
str3 = Replace(str1, "Inquiry", "Enable")
str4 = Replace(str1, "Inquiry", "Assure")

txtInquiryColor.ControlSource = str1
txtDesignColor.ControlSource = str2
txtEnableColor.ControlSource = str3
txtAssureColor.ControlSource = str4
End Function

And this is in the report (where ??? is what I'm asking about)
Code: 
Private Sub Report_Load()

Call txtColor(???)


End Sub

The error I get is "Runtime 424 Object Required"
 
B

billmeye

Access Aficionado
Local time
Today, 03:57
Joined
Feb 20, 2010
Messages
542
It looks like you are calling a function from a report and within the function you are referencing controls without specifying where the controls reside.

txtInquiryColor.ControlSource = str1
Click to expand...

You need to say where they reside:

[Reports]![rptYourReport]![txtInquiryColor].ControlSource = str1

But even before, why the function? Are you trying to assign a value or change the controlsource?
 
G

galaxy

Registered User.
Local time
Today, 02:57
Joined
Feb 26, 2013
Messages
16
I'm trying to change the controlsource of the textbox. I have reports A, B, and C, and I have the same four textboxes in each report with almost the same controlsource. Right now, if my manager says "You know what? I changed my mind, I want it to say X instead of W" I have to change it in twelve places. Therefore I want to call a function so I only have to change it in one place. Referencing the specific report in the function does not help because multiple reports are going to call it. That's my question, how do I tell the function which controls (plural!) I need to change?


billmeye said:
It looks like you are calling a function from a report and within the function you are referencing controls without specifying where the controls reside.



You need to say where they reside:

[Reports]![rptYourReport]![txtInquiryColor].ControlSource = str1

But even before, why the function? Are you trying to assign a value or change the controlsource?
Click to expand...
 
B

billmeye

Access Aficionado
Local time
Today, 03:57
Joined
Feb 20, 2010
Messages
542
Are you saying Report A has several fields (say 30) and depending on what needs to be printed, you want to show only specific fields (say just 5 at a time)? And you use something from a form to determine what to show? Or, are you saying the same report can have different record sources with different controls?
 
G

galaxy

Registered User.
Local time
Today, 02:57
Joined
Feb 26, 2013
Messages
16
No, I'm saying I have three reports that have the same textboxes on each report. I want to show all of them always when I open up a report. Nothing is ever being selected on a form or hidden.

Report A has txtInquiryColor, txtDesignColor, etc.
Report B has txtInquiryColor, txtDesignColor, etc.
Report C has txtInquiryColor, txtDesignColor, etc.

txtInquiryColor has the exact same controlsource wherever it is used, txtDesignColor has the same controlsource wherever it is used, etc. If I open up Report A, the references passed to the function should be [Reports]![ReportA]![txtInquiryColor].ControlSource, [Reports]![ReportA]![txtDesignColor].ControlSource, etc. But if I close Report A and open up Report B, those references need to change. If I only had one report, I wouldn't need to use the function.
 
B

billmeye

Access Aficionado
Local time
Today, 03:57
Joined
Feb 20, 2010
Messages
542
Sorry for my questions but I'm trying to better understand what is changing with the function.

Are the controls on the reports Unbound and you are setting the controlsources with your function and you have Unbound controls named txtInquiryColor, txtDesignColor, txtEnableColor, txtEnableColor on each report but each report is different in some other way?
 
G

galaxy

Registered User.
Local time
Today, 02:57
Joined
Feb 26, 2013
Messages
16
Yes that's exactly it. The reports are different from each other in ways that are not important for this question.
 
G

Galaxiom

Super Moderator
Staff member
Local time
Today, 17:57
Joined
Jan 20, 2009
Messages
12,852
Provided the controls have the same name, pass the report rather than the controls.

Code: 
Public Function txtColor(ByRef rpt as Report)

Dim str1 As String, str2 As String, str3 As String, str4 As String

str1 = "=IIf(IsNull([Inquiry start date]),'W',IIf([txtInquiry]<1 And IsNull([Inquiry end date]),'R'))"
str2 = Replace(str1, "Inquiry", "Design")
str3 = Replace(str1, "Inquiry", "Enable")
str4 = Replace(str1, "Inquiry", "Assure")

With rpt
   .txtInquiryColor.ControlSource = str1
   .txtDesignColor.ControlSource = str2
   .txtEnableColor.ControlSource = str3
   .txtAssureColor.ControlSource = str4
End With

End Function

Code: 
Private Sub Report_Load()

  Call txtColor(Me)

End Sub

BTW In VBA each variable type must be declare individually. Your original Dim would have instantiated most of the variables as Variant.

Also I note you showed Option Compare Database but not Option Explicit in your code. You really should include Option Explicit as it can save a lot of bother.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom