Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Apr 2002
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,
    I am wondering if the following is possible and if so, how do I achieve it? I have a series of fields (either text box for entry or label for displaying calculated results) that constitute a "Table" on an Access 2007 form. When one of the fields changes (obviously, a text field), I need to recalculate the various values and paste them into appropriate labels in the same "Row". Currently, I've duplicate the code for each controls "After Update" and changed the control names accordingly (sample below)

    Code:
        ' If we have values in both text and Combo box
        If Not IsNull(cbxEmpPMTue.Value) And Not IsNull(txtDurPMTue.Value) Then
            ' Convert h:mm to a decimal value
            gintDuration = (CDate(txtDurPMTue.Value) * 24)
        
            ' Determine our staff type and get appropriate rate
            If cbxEmpPMTue.Value = "AIN" Then
                gintRate = txtAINRate.Value
            Else
                gintRate = txtRNRate.Value
            End If
            
            ' Calculate the cost of the visit (Duration * Hourly rate)
            gintCost = gintRate * gintDuration
            ' Update the Field Cost label
            lblFieldPMTue.Caption = "$" + CStr(gintCost)
            
            ' If we have Kilometers entered
            If Not lblTravelPMTue.Caption = "$0" Then
                ' Get the cost of the travel
                gintTRCost = lblTravelPMTue.Caption
                ' Add it to the FIeld Cost
                gintCost = gintTRCost + CDbl(lblFieldPMTue.Caption)
            End If
            
            ' Update the total cost label
            lblCostPMTue.Caption = "$" & CStr(gintCost)
        Else
            ' If we have no data in either text box or combo box, clear total cost and Field Cost labels
            lblFieldPMTue.Caption = "$0"
            lblCostPMTue.Caption = "$0"
        End If
    As you'd be aware, this a MAJOR pain the rear to maintain as I need to make any changes at least 13 times across the various routines.

    What I'd like to be able to do is create a Function where I can pass in the names of the relevant controls and maintain one code block instead of 39+ duplicate blocks.

    The code I have is below:
    Code:
    Private Function EmpUpdate(EmpCombo As String, DurationCtl As String, Field As String, Travel As String, Cost As String) As Boolean
    'On Error GoTo update_Error
    DoCmd.SetWarnings False
    
        Dim ctlControl As Control
        Dim objCombo As Control
        Dim objDur As Control
        Dim objField As Control
        Dim objTravel As Control
        Dim objCost As Control
        
        For Each ctlControl In Me.Controls
            Select Case ctlControl.Name
                Case EmpCombo
                    objCombo = ctlControl
                Case DurationCtl
                    objDur = ctlControl
                Case Field
                    objField = ctlControl
                Case Travel
                    objTravel = ctlControl
                Case Cost
                    objCost = ctlControl
            End Select
        Next ctlControl
    ...
    This little code block (I was hoping) would locate the various controls on the form and assign them to the control variables so I can use them in place of hard-coded control names in the first code snippet. What I'm getting is an "Run-time Error '91' - Object variable or With block variable not set".

    Am I missing something or is this not possible?

    Cheers
    toncc

  2. #2
    Lounger
    Join Date
    Apr 2002
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Fixed! What I need to do is use
    Code:
    Me([Index]).<property>
    So the first code snippet becomes
    Code:
    Private Function EmpUpdate(EmpCombo As String, DurationCtl As String, Field As String, Travel As String, Cost As String) As Boolean
    On Error GoTo update_Error
    DoCmd.SetWarnings False
        
        ' If we have values in both text and Combo box
        If Not IsNull(Me(EmpCombo).Value) And Not IsNull(Me(DurationCtl).Value) Then
            ' Convert h:mm to a decimal value
            gintDuration = (CDate(Me(DurationCtl).Value) * 24)
        
            ' Determine our staff type and get appropriate rate
            If Me(EmpCombo).Value = "AIN" Then
                gintRate = txtAINRate.Value
            Else
                gintRate = txtRNRate.Value
            End If
            
            ' Calculate the cost of the visit (Duration * Hourly rate)
            gintCost = gintRate * gintDuration
            ' Update the Field Cost label
            Me(Field).Caption = "$" + CStr(gintCost)
            
            ' If we have Kilometers entered
            If Not Me(Travel).Caption = "$0" Then
                ' Get the cost of the travel
                gintTRCost = Me(Travel).Caption
                ' Add it to the FIeld Cost
                gintCost = gintTRCost + CDbl(Me(Field).Caption)
            End If
            
            ' Update the total cost label
            Me(Cost).Caption = "$" & CStr(gintCost)
        Else
            ' If we have no data in either text box or combo box, clear total cost and Field Cost labels
            Me(Cost).Caption = "$0"
            Me(Field).Caption = "$0"
        End If
    With a function call of:

    Code:
     blnResult = EmpUpdate("cbxEmpAMMon", "txtDurAMMon", "lblFieldAMMon", "lblTravelAMMon", "lblCostAMMon")

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •