Attach a subroutine to an unbound Text box? (1 Viewer)

foxxrunning

Member
Local time
Today, 05:19
Joined
Oct 6, 2019
Messages
109
Is there a way to assign a subroutine (which I have written already) to an unbound text box? I need that textbox to display a number of different things as determined by other fields in the report. I know this is not very descriptive but I just need to know I am not trying to do the impossible before I continue. Thanks for any help.
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:19
Joined
Jan 23, 2006
Messages
15,361
"I need that textbox to display a number of different things as determined by other fields in the report."
I think it would be helpful to you and readers to give a little more description and/or example to clarify the requirement. Better to use plain English than quasi-database terms. What would you say to someone you contracted to "design/build" what you want?
 

foxxrunning

Member
Local time
Today, 05:19
Joined
Oct 6, 2019
Messages
109
What I want to happen is that based on the code below, different amounts of money will be displayed. This was originally assigned to the control source of the sub report, and then I tried assigning it to OnFocus event of the subreport. Neither one gave any errors on print preview, nor gave any syntax errors. But none of them changed the [Split1] or [Split2] fields. So I removed the fields and just put text boxes in their place. I can't see any way of assigning this code to either of these textboxes.


Option Compare Database
Option Explicit
Private Sub ServiceDate_GotFocus()
Select Case [CODE1] = "AS"
[SPLIT1] = 0.514 * [Fee]

Case [CODE1] = "AL"
[SPLIT1] = 0.618 * [Fee]

Case [CODE1] = "AS" And [CODE2] = "AF"
[SPLIT2] = 0.486 * [Fee]

Case [CODE1] = "AL" And [CODE2] = "AF"
[SPLIT2] = 0.381 * [Fee]

Case Else
[SPLIT1] = [Fee]
[SPLIT2] = Null

End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:19
Joined
Oct 29, 2018
Messages
21,357
Hi. What you could try to do is create a function that returns a value. You can then assign the funtion call as a Control Source for your unbound textbox. Hope that helps...
 

HiTechCoach

Well-known member
Local time
Today, 07:19
Joined
Mar 6, 2006
Messages
4,357
For reports, I use the Report's section On Format where the control is located to set unbound controls and other things. It works similar to a Form's On Load event and On Current events.

Try moving your code there. I would be very surprised if it does not work because I do it all the time. It Is how I would code it if it was my project.


For example, if the control is in a report or sub report's detail section then use:

Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Select Case [CODE1] = "AS"
      [SPLIT1] = 0.514 * [Fee]
  
       Case [CODE1] = "AL"
      [SPLIT1] = 0.618 * [Fee]
   
        Case [CODE1] = "AS" And [CODE2] = "AF"
      [SPLIT2] = 0.486 * [Fee]
   
       Case [CODE1] = "AL" And [CODE2] = "AF"
      [SPLIT2] = 0.381 * [Fee]
    
        Case Else
      [SPLIT1] = [Fee]
      [SPLIT2] = Null
    
End Select

End Sub
 
Last edited:

HiTechCoach

Well-known member
Local time
Today, 07:19
Joined
Mar 6, 2006
Messages
4,357
I lust looked at the Select Case statement and there may be an issue. Only 1 case is wil be selected.


Code:
       Case [CODE1] = "AS" And [CODE2] = "AF"
      [SPLIT2] = 0.486 * [Fee]
   
       Case [CODE1] = "AL" And [CODE2] = "AF"
      [SPLIT2] = 0.381 * [Fee]

WIll never be true since the previous CASE logic will terminate the SELECT.
 
Last edited:

HiTechCoach

Well-known member
Local time
Today, 07:19
Joined
Mar 6, 2006
Messages
4,357
Try this:


Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


' Initialize tot eh defaults
      Me.[SPLIT1] = Me.[Fee]
      Me.[SPLIT2] = Null

' check for exceptions

Select Case Me.[CODE1] = "AS"
          Me.[SPLIT1] = 0.514 * Me.[Fee]

         IF Me.[CODE2] = "AF"  THEN     Me.[SPLIT2] = 0.486 * Me.[Fee]
 
       Case Me.[CODE1] = "AL"
            Me.[SPLIT1] = 0.618 * Me.[Fee]

           If Me.[CODE2] = "AF" THEN   Me.[SPLIT2] = 0.381 * Me.[Fee]
   
   
End Select

End Sub
 

foxxrunning

Member
Local time
Today, 05:19
Joined
Oct 6, 2019
Messages
109
I lust looked at the Select Case statement and there may be an issue. Only 1 case is wil be selected.


Code:
       Case [CODE1] = "AS" And [CODE2] = "AF"
      [SPLIT2] = 0.486 * [Fee]
  
       Case [CODE1] = "AL" And [CODE2] = "AF"
      [SPLIT2] = 0.381 * [Fee]

WIll never be true since the previous CASE logic will terminate the SELECT.
If Code 1 = AS and Code2 = AF it will not go to the next line is correct. If they are not both true it should look at the next line where Code 1 = Al and code 2 is also AF. It that not true?
 

HiTechCoach

Well-known member
Local time
Today, 07:19
Joined
Mar 6, 2006
Messages
4,357
EDITED by HiTechCoach: add additioanl explinations

If Code 1 = AS and Code2 = AF it will not go to the next line is correct. If they are not both true it should look at the next line where Code 1 = Al and code 2 is also AF. It that not true?

Correct, but the problem is the lines before these with only CODE1 tested.

Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Select Case [CODE1] = "AS"    ' >>>>> CASE 1

      [SPLIT1] = 0.514 * [Fee]

       Case [CODE1] = "AL"  ' >>>>>> CASE 2

      [SPLIT1] = 0.618 * [Fee]

        Case [CODE1] = "AS" And [CODE2] = "AF"  ' If CASE 1 abve  is true then it will never reach here

          [SPLIT2] = 0.486 * [Fee]

       Case [CODE1] = "AL" And [CODE2] = "AF"  ' CASE 2  aboce is true then it will neer reach here

      [SPLIT2] = 0.381 * [Fee]

        Case Else
      [SPLIT1] = [Fee]
      [SPLIT2] = Null

End Select

End Sub


For your original code to work you will have to reorder it like something this:


Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Select Case [CODE1] = "AS" And [CODE2] = "AF"

      [SPLIT1] = 0.514 * [Fee] ' <<<< add this If  [SPLIT1] must always be set
      [SPLIT2] = 0.486 * [Fee]

Case [CODE1] = "AL" And [CODE2] = "AF"

      [SPLIT1] = 0.618 * [Fee]  ' <<<< add this If  [SPLIT1] must always be set
      [SPLIT2] = 0.381 * [Fee]

Case [CODE1] = "AS" ' it only reaches this if the above two cases are false

      [SPLIT1] = 0.514 * [Fee]
      [SPLIT2] = Null   ' <<< added to be sure it gets set

  Case [CODE1] = "AL"

      [SPLIT1] = 0.618 * [Fee]
     [SPLIT2] = Null   ' <<< added to be sure it gets set


   Case Else
      [SPLIT1] = [Fee]
      [SPLIT2] = Null

End Select

End Sub
 
Last edited:

foxxrunning

Member
Local time
Today, 05:19
Joined
Oct 6, 2019
Messages
109
Try this:


Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


' Initialize tot eh defaults
      Me.[SPLIT1] = Me.[Fee]
      Me.[SPLIT2] = Null

' check for exceptions

Select Case Me.[CODE1] = "AS"
          Me.[SPLIT1] = 0.514 * Me.[Fee]

         IF Me.[CODE2] = "AF"  THEN     Me.[SPLIT2] = 0.486 * Me.[Fee]

       Case Me.[CODE1] = "AL"
            Me.[SPLIT1] = 0.618 * Me.[Fee]

           If Me.[CODE2] = "AF" THEN   Me.[SPLIT2] = 0.381 * Me.[Fee]
 
 
End Select

End Sub
Sorry for my ignorance, where should this code be placed? In the control source?
 

foxxrunning

Member
Local time
Today, 05:19
Joined
Oct 6, 2019
Messages
109
Tried the code with me. before all of the [ ] and received a number of syntax and unrecognizable errors. Me. [ ] has worked for me before but somehow it does not seem to be recognized.
Is it possible that there is a better way to do this other than the Select Case syntax?
 

HiTechCoach

Well-known member
Local time
Today, 07:19
Joined
Mar 6, 2006
Messages
4,357
Tried the code with me. before all of the [ ] and received a number of syntax and unrecognizable errors. Me. [ ] has worked for me before but somehow it does not seem to be recognized.
Is it possible that there is a better way to do this other than the Select Case syntax?


Me. refer to control onthe report.


If control name or field name has a space then you must use the [ and ]. You can always use the [ and ], even when thre are no spaces in the name.

For the Me.9Control Name Here] to work, the must be a control on the report with that name

TIP: You can add controls to the report and set the visible property to false to hide them, but still can use then in your VBA code.
 

foxxrunning

Member
Local time
Today, 05:19
Joined
Oct 6, 2019
Messages
109
Me. refer to control onthe report.


If control name or field name has a space then you must use the [ and ]. You can always use the [ and ], even when thre are no spaces in the name.

For the Me.9Control Name Here] to work, the must be a control on the report with that name

TIP: You can add controls to the report and set the visible property to false to hide them, but still can use then in your VBA code.
Every reference to a control in the code does have a control on the report. I do understand the visible property and have used it before. I do remove the labels associated with each control on the report as I don't need them. Should I leave them on the report in invisible mode? Don't understand how that might be needed though. Thanks again for your help.
 

HiTechCoach

Well-known member
Local time
Today, 07:19
Joined
Mar 6, 2006
Messages
4,357
Every reference to a control in the code does have a control on the report.

If you drag a field from the field list to the report, Acess will attempt to name the control the same name as the file name.

If you add the control first, then set the control source, you will need to also set eh control name.

I would verify the control name property is correct if you are getting errors..

If you do not include the Me. before a [Name here] then you are letting Access decide if it is a control name of the report or a field name from the record source. Access may pick the wrong object. That is why I like to be specific and use the Me. to make sure Acess knows it is a control and not a field in the record source.

When writing code, I prefer to type Me.S in the VBA editor to see if the autocomplete can find Me.SPLIT1. If it does, the control name is correct.

I do remove the labels associated with each control on the report as I don't need them. Should I leave them on the report in invisible mode? Don't understand how that might be needed though.

I also delete the label from hidden textboxes so there are not any extra controls on the report that are not required.
 

foxxrunning

Member
Local time
Today, 05:19
Joined
Oct 6, 2019
Messages
109
If you drag a field from the field list to the report, Acess will attempt to name the control the same name as the file name.

If you add the control first, then set the control source, you will need to also set eh control name.

I would verify the control name property is correct if you are getting errors..

If you do not include the Me. before a [Name here] then you are letting Access decide if it is a control name of the report or a field name from the record source. Access may pick the wrong object. That is why I like to be specific and use the Me. to make sure Acess knows it is a control and not a field in the record source.

When writing code, I prefer to type Me.S in the VBA editor to see if the autocomplete can find Me.SPLIT1. If it does, the control name is correct.



I also delete the label from hidden textboxes so there are not any extra controls on the report that are not required.
Thank you for your help with this. I will try to rewrite the code again tomorrow and see if this changes anything. Again, thanks for your help.
 

Users who are viewing this thread

Top Bottom