Attach a subroutine to an unbound Text box?

F

foxxrunning

Member
Local time
Yesterday, 17:36
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.
 
"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?
 
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
 
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...
 
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:
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:
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
 
HiTechCoach said:
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.
Click to expand...
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?
 
EDITED by HiTechCoach: add additioanl explinations

foxxrunning said:
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?
Click to expand...

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:
HiTechCoach said:
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
Click to expand...
Sorry for my ignorance, where should this code be placed? In the control source?
 
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?
 
foxxrunning said:
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?
Click to expand...


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.
 
HiTechCoach said:
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.
Click to expand...
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.
 
foxxrunning said:
Every reference to a control in the code does have a control on the report.
Click to expand...

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.

foxxrunning said:
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.
Click to expand...

I also delete the label from hidden textboxes so there are not any extra controls on the report that are not required.
 
HiTechCoach said:
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.
Click to expand...
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.
 
You must log in or register to reply here.

Similar threads

J
Report and Unbound Text Box
Replies
8
Views
428
JMZ
J
E
Dlookup on unbound text
Replies
10
Views
487
GPGeorge
GPGeorge
D
Remember where I was in a table and return to same record
Replies
7
Views
379
Pat Hartman
P
M
Open Unbound form and filter subform
Replies
7
Views
447
Gasman
Gasman
S
Should my code embrace more of the "Single Responsibility Principle"?
Replies
6
Views
668
jwcolby54
jwcolby54

Users who are viewing this thread

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