Getting value out of VBA and into Form field

[Monterey_Manzer]

Hey All,

I am teaching myself VBA as I go so please forgive me if this is something obvious.



I have a calculated value that shows up properly when I send it to a message box but I can't seem to get it to populate the field in the form, and hence the record. First some background.

I have a form in which coworkers will enter start and end dates for internships. I've already borrowed a nice VBA function that calculates the number of workdays (M-F) between two dates. I then wrote a subroutine that calculates the total cost of the internship (ISP or Internship Support Payment). There are two different rates that are used based on another criteria. I have my code set up such that this will only run if the "Eligible for Funding" checkbox value = True. My code is as follows:

Function CalcTC(days, Rate) As Currency     'Calculates Total Cost (TC) for Internship
     CalcTC = days * Rate
End Function

Function CalcWorkdays(StartDate, EndDate) As Integer

    Dim LTotalDays As Integer
    Dim LSaturdays As Integer
    Dim LSundays As Integer

    On Error GoTo Err_Execute

    CalcWorkdays = 0

    If IsDate(StartDate) And IsDate(EndDate) Then
        If EndDate <= StartDate Then
            CalcWorkdays = 0
        Else
            LTotalDays = DateDiff("d", StartDate - 1, EndDate)
            LSaturdays = DateDiff("ww", StartDate - 1, EndDate, 7)
            LSundays = DateDiff("ww", StartDate - 1, EndDate, 1)

            'Workdays is the elapsed days excluding Saturdays and Sundays
            CalcWorkdays = LTotalDays - LSaturdays - LSundays

        End If
    End If

    Exit Function

Err_Execute:
    'If error occurs, return 0
    CalcWorkdays = 0

End Function

Public Sub ISP_TotalCost()
     Dim FirstDay As Date, LastDay As Date, DayCount As Integer, Cohort As Integer, Rate As Integer, ISP As Currency
     FirstDay = Me.First_Day.Value
     LastDay = Me.Last_Day.Value
     DayCount = CalcWorkdays(FirstDay, LastDay)
     Cohort = Me.Cohort.Value
     
     If Cohort = 2012 Then
     ISP = CalcTC(DayCount, 200)
     ElseIf Cohort <> 2012 Then
     ISP = CalcTC(DayCount, 240)
     End If
  
End Sub

'Making Total Funding field only visible when Participant is eligible for funding and inserting calculated ISP value into [Total Funding]
Private Sub Eligible_Click()
     Call ISP_TotalCost
     If Eligible.Value = True Then
     Me.Total_Funding.Value = ISP
     Me.Total_Funding.Visible = True
     ElseIf Eligible.Value = False Then
     Me.Total_Funding.Value = 0
     Me.Total_Funding.Visible = False
     End If
End Sub

As the last subroutine should suggest, I am trying to get the calculated value for ISP to populate the Total_Funding field in my form when the Eligible.Value = True. As I stated above, when I drop in a message box with the ISP value, it displays the correct value for each record whenever the Eligible check box is clicked but I can't seem to get the value into my field. I would greatly appreciate any suggestions and I apologize if this is something I will probably figure out in the next chapter of my VBA coding book.

Thanks,
Monterey_Manzer

[June7]

Saving calculated data (data dependent on other data) is often bad idea. Risk is the data gets 'out of sync'. If it can be calculated once, it can be calculated whenever needed.

Functions can be called from the ControlSource property of textbox or in a query, even in an UPDATE action in the UpdateTo row.

Saving the calculated value will involve code, I use only VBA. The trick is figuring out what event to use. Perhaps the Click event of a button or the Close event of form? If you want to save this for only the current record and the Total field is included in the form RecordSource, simply:
Me!Total = Me.textbox
or
Me!Total = CalcTC(days, Rate)

If you want to save to multiple records then need to run UPDATE action.

[Monterey_Manzer]

You are right, I should not be trying to save calculated data to my tables. All I really want is to be able to show the calculated total in the form and later in the report I generate from those records. As I stated, I've got the number to calculate correctly but the debugger gets mad at me when I try to assign values to my text box field as in the last subroutine in my code. I'll skip the saving to records part. my question then is whether or not this code will still work in a Report format?

[June7]

The last subs reference variable ISP - you don't show it declared anywhere, is this a global declared variable?

Have you tried changing Sub ISP_TotalCost() to a Function and calling function from textbox ControlSource?

Is the form in Single or Continuous/Datasheet view? If the latter then setting the Visible property will affect ALL instances of the control (in every record).

[Missinglinq]

ISP_TotalCost is a Sub, not a Function, and cannot return a value! Also, the variable ISP is local to the ISP_TotalCost sub, and not available to Eligible_Click event, where you are trying to use it!

Omitting the line

Call ISP_TotalCost

I would simply move the rest of the Eligible_Click code into the end of the ISP_TotalCost sub.

You could rework ISP_TotalCost as a Function, but I really see no need of a separate Function for this small task since the task is unlikely to be needed elsewhere.

Linq ;0)>

[Missinglinq]

ISP_TotalCost is a Sub, not a Function, and cannot return a value! Also, the variable ISP is local to the ISP_TotalCost sub, and not available to Eligible_Click event, where you are trying to use it!

Omitting the line

Call ISP_TotalCost

I would simply move the rest of the Eligible_Click code into the end of the ISP_TotalCost sub.

You could rework ISP_TotalCost as a Function, but I really see no need of a separate Function for this small task since the task is unlikely to be needed elsewhere.

Linq ;0)>

Addendum: Sorry, got distracted in the middle of this and didn't see June7's last post!

[June7]

Is okay, you definitely added more guidance for OP to consider.

[Monterey_Manzer]

Thanks both of you! I've been learning VBA and Access on an as needed basis and I really need some time to iron out my fundamentals. I'll work in your guidance and let you know how it comes out.

[June7]

Glad we could offer you some insight. Certainly appreciate a well-phrased question with code for analysis. Kudos for accomplishments so far, predict you will do well.

Do you need to account for holidays in your workdays calculation? This requires a table of recognized holidays and must be regularly updated as many holiday dates shift for each year.

[Monterey_Manzer]

We already have to account for various weird irregularities (such as someone doing an internship for 2 days out of every week over a period of 6 months). In cases like that we do still want to capture the start and end dates of the internship. We also have more normal situations like someone taking a few days off and to account for these I was simply going to include an "adjustment" field that would be subtracted from the total derived by the CalcWorkdays function prior to the CalcTC function. There are many more procedure trackers that I have to build in a relatively short amount of time so I won't be doing any really elegant coding for a little while. I look forward to having the time to build some truly snazzy code though.