Access VBA Update Form Textbox With Sub Parameter

Question

I'm having an issue with figuring out how to update my forms textbox with the value I ended with in the procedure. If a messagebox is used the output is fine. However, I want the user to be able to click the button and the textbox on that form be updated with the answer.

Private Sub btnTotalGuests_Click()

    Call CalculateTotalGuestsForEachService

End Sub

Here is the procedure

Public Sub CalculateTotalGuestsForEachService()

    'Declare variables
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim intTotalParty As Integer
    Dim intID As Integer
    
    'Set the current database
    Set db = CurrentDb
    
    'Set the recordset
    intID = InputBox("Enter the Service ID (1-6)", "Service ID")
    Set rst = db.OpenRecordset("Select Orders.* From Orders Where ServiceID =" & intID)
    
    'Cycle through the records
    Do While Not rst.EOF
        If Not IsNull(rst!NoInParty) Then
            intTotalParty = intTotalParty + rst!NoInParty
        End If
    rst.MoveNext
    Loop
    
    'Display total amount
    txtTotalGuests.Text = intTotalParty 'Wondering how to display this in the textbox on the form.

    
    'Close the recordset
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    
End Sub

The form for the program is a single button to launch the procedure and a textbox to get the answer back. — Swifty509
– Swifty509, Commented Nov 18, 2020 at 5:47
Saving calculated data is usually not necessary and often a bad idea, especially aggregate data. If it can be calculated for saving, it can be calculated when needed. — June7
– June7, Commented Dec 2, 2020 at 6:18

June7 · Accepted Answer · 2020-11-18 06:39:21Z

1

Don't use Text property. You want Value property and since Value is default, don't even need to explicitly reference. Presume CalculateTotalGuestsForEachService is in the form module so can use Me.txtTotalGuests.

Consider domain aggregate function instead of looping recordset.

Dim intID As Integer
intID = InputBox("Enter the Service ID (1-6)", "Service ID")
Me.txtTotalGuests = DSum("NoInParty", "Order", "ServiceID =" & intID)

answered Nov 18, 2020 at 6:39

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Access VBA Update Form Textbox With Sub Parameter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related