lostfocus for field in same record of subform

[uguimess]

I have a subform in datasheet view for entering species count data; one record for species with several fields for different count techniques and times. There are two fields to hold the total count across columns for each record, one locked field for the calculated total for the row, the other for the user to optionally enter a modified total (rounded or whatever) if desired (This is needed). The following code shows how I update the Totals field when navigating to a new record.

Question 1. Does my approach to totalling counts seem sound?
Question 2. How can I get the total to update when tabbing from field to field in the subform, whithin the same record?

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Screen.ActiveControl.ControlType = acTextBox Then
        Call UpdateMyTotal
    End If
End Sub

Private Sub UpdateMyTotal()

    Dim iTotal
    Dim ctl As Control
    
    iTotal = 0
    For Each ctl In Me.Controls
        ''If ctrl.Tag = 0 Then it is ignored in calculations.
        If ctl.ControlType = acTextBox And ctl.Tag <> 0 Then
            If Not Trim(ctl.Value & "") = "" And IsNumeric(ctl.Value) Then  iTotal = iTotal + ctl.Value 
    Next ctl
    
    'This line writes the new calculated total for this species to the table for this Site.
    Me.txtTtl = iTotal
   
End Sub

-----------------------------------------
Where would we be if we didn't try?

 

[lameid]

Generally it is not recommended to calculate values in this way. Typically you would set the control source of the control to display the sum of the fields and use a similar expression in a query everywhere else you want to use it.

That said, I am a little concerned that the control updated may not be commited for use when the form before update fires. It would have to fire after the control's after update event.

I'm also concerned about..

            If Not Trim(ctl.Value & "") = "" And IsNumeric(ctl.Value) Then  iTotal = iTotal + ctl.Value

Instead I think you are less likely to hit errors with the following but you may still need to be sure about the order of events.

      iTotal = iTotal + NZ(ctl.Value,0)

 

[uguimess]

Thanks lameid.

Thanks for the NZ(). I agree.

What I had done is try the calc in AfterUpdate, and did not work. I saw a thread where it worked in BeforeUpdate, so I used it and since my loop reads ctl.value, it gets any new values you type in before you actually navigate to the next record.
I just want a different way (Question 2.) that would get that 'Totals' field updated even if you don't go to the next record, but instead just navigate to a new field in the current record.

As far as putting the sum in the control source for txtTtl, I am not sure how that would look or work. In this database, the user can open a different (customization) form that allows the addition of fields to the temp table that is part of the subform's RecordSource query, and also add corresponding fields to the subform.

[tab](because I haven't written that part, I am kind of unsure what that code will look like.
[tab]For Instance...How to I update the query to grab the new fields?
[tab]But I will fall off that bridge when I come to it.)

...So at run-time, there are potentially more fields than at design time. In that case, I know my loop will catch them, but (Question 3.) is it possible to put this in the control source for txtTtl???

-----------------------------------------
Where would we be if we didn't try?

 

[lameid]

I am not sure I am completely following everything but it is sounding like you are dynamically adding controls for values to your form?

Any reason you do not have a related charges table and a sub form to enter the related data?

 

[uguimess]

I have to join you with "I am not sure I am completely following [you]..."

What is a related charges table etc? (Question 4)

What I am building is a database for single user environment, but will be distributed to several users, individually. There are several fields that are mandatory for all users to enter their core data. Each user can extend his own data with other fields to enter data that I have no idea about yet (nor do they in some cases).
And the data are always integers that need to be totalled.
I just want that total to show every time a new datum is added to a field, rather than have the user add numbers all the way across a record of fields, only to wonder where he made a typo or missed data because the total field reads lower than he thought it should. Do you think the answer to Question 2 will disappoint me?

-----------------------------------------
Where would we be if we didn't try?

 

[uguimess]

solved.

=UpdateThis() in the 2-click event on the property sheet.

-----------------------------------------
Where would we be if we didn't try?

 

[uguimess]

...for each control. So I can add that when I create the control with VBA.

-----------------------------------------
Where would we be if we didn't try?

 

[TheAceMan1]

How are ya uguimess . . .

[blue]Question 2. How can I get the total to update when tabbing from field to field in the subform, whithin the same record?[/blue]

[ol][li]In the [blue]Tag[/blue] property of the controls you desire to total, add a question mark [blue]?[/blue] [red](No Quotations Please!)[/red]. Tip: if you [purple]group select[/purple] the controls, you only have to add the question mark once! [/li]
[li]In the [blue]AfterUpdate[/blue] event of each control tagged, copy/paste the following line:

[blue]   If Screen.ActiveControl.Tag = "?" Then Call UpdateMyTotal[/blue]

[/li]
[li]Your common code changes to:

[blue]Private Sub UpdateMyTotal()
   Dim Tlt, ctl As Control
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         If Trim(ctl & "") <> "" And IsNumeric(ctl) Then Tlt = Tlt + ctl
      End If
   Next ctl
   
   Me.txtTtl = Nz(Tlt, 0)
   
End Sub[/blue]

[/li]
[li]Don't forget to rem or remove your code in the [blue]Form_BeforeUpdate[/blue] event.[/li]
[li][blue]Done! ... Perform your testing![/blue][/li][/ol]
[blue]Your Thoughts? . . .[/blue]

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] [blue]Its Worthy Reading![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[uguimess]

Thanks AceMan1.

I made an error in my second last post.
I meant to say that I put "=UpdateThis()" in the BeforeUpdate line on the property sheet for each field that should be involved in the calculation and "0" in the Tag property for each field that is not. This works beautifully (I think).

My thoughts:
I really don't get the limitations and advantages of choosing Before or After Update in different circumstances. Usually I just try one and then the other until I get it to work. Is there some advantage to using the AfterUpdate event in this case to compel me to switch?

Here is the entire function for calculations; fun isn't it?

Private Function UpdateMyTotal()

    Dim sSQL As String
    Dim iTotal, iPartialSum, iGroupTabs As Integer
    Dim ctl As Control
    Dim i As Integer
    Dim iSum
    
    iTotal = 0
    For Each ctl In Me.Controls
        ''If ctrl.Tag = 0 Then it is ignored in calculations.
        If ctl.ControlType = acTextBox And ctl.Tag <> 0 Then
            iTotal = iTotal + Nz(ctl.Value, 0)  'From LameID
        End If
    Next ctl
    
    'This line writes the new calculated total for this species (record) _
     to the "Ttl-Calc" form field for this Site (Tab page), BUT is not written _
     to the table until After_Update.
    Me.txtTtl = iTotal
   
    ''iPartialSum is the sum of values for this species for all Sites, except this one.
    iPartialSum = DSum("Qty_Ttl_Calculated", "Q_T_Bird_Data_Entry_Tabs" _
                        , "Site_ID <> 0 AND Site_ID <> " & Me.Site_ID & " AND ID = " & Me.ID)
    'This writes calculated total for this species for all Sites to the record _
     that display under the "All Sites" tab.
    sSQL = "Update T_Bird_List SET Qty_Ttl_Calculated = " & iPartialSum + iTotal & _
           " WHERE Site_ID = 0 AND Profile_Bird_ID = " & Me.ID
    DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL
    DoCmd.SetWarnings True
    
    iPartialSum = DSum(Screen.ActiveControl.ControlSource, "Q_T_Bird_Data_Entry_Tabs" _
                        , "Site_ID <> 0 AND Site_ID <> " & Me.Site_ID & " AND ID = " & Me.ID)
    'This writes calculated total for this species for all Sites to records _
     that display under the "All Sites" tab.
    iSum = Nz(iPartialSum, 0) + Nz(Screen.ActiveControl.Value, 0)
    If iSum = 0 Then iSum = vbNull
    sSQL = "Update T_Bird_List SET " & Screen.ActiveControl.ControlSource & " = " & iSum & _
           " WHERE Site_ID = 0 AND Profile_Bird_ID = " & Me.ID
    DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL
    DoCmd.SetWarnings True
    
    Me.txtTtl.Requery

End Function

-----------------------------------------
Where would we be if we didn't try?

 

[lameid]

That said, I am a little concerned that the control updated may not be commited for use when the form before update fires. It would have to fire after the control's after update event.

The before update happens before the update. After update happens after the update. Generally you want to use before update if you want to possibly undo the update (setting the cancel variable) otherwise use after update as it ensures your data is updated. I am sure there are varied circumstances to use before update but none are jumping out at me at the moment.