Newbie Question on command buttons

PSwa

New member
Local time
Today, 23:04
Joined
May 10, 2020
Messages
13
Hi there. I'm very new to MS Access so I'm sure this is a newbie-type question.
I'd like to know how to use a command button to perform a calculation on all records on a form without me having to physicaly select the records one at a time. An example would be, say, a calculation of current age using the DoB of each person in a "Person" table. I know I could use a calculated field but I don't want to go down that route. I just want the user to be able to click on a button and see the results for each record
 
Hi. You won't even need a button. Just do the calculation in the query and display the result on your form.
 
Hi theDBguy, thanks for responding. I understand I can do it that way for something as simple as the DoB example I gave, but what I'm actually trying to do is a bit more complex and requires multiple calculations. I've got it working so that clicking the button does the calculations when I select a particular record. I'd just like to be able to automate the process for all records displayed on the form
 
Hi theDBguy, thanks for responding. I understand I can do it that way for something as simple as the DoB example I gave, but what I'm actually trying to do is a bit more complex and requires multiple calculations. I've got it working so that clicking the button does the calculations when I select a particular record. I'd just like to be able to automate the process for all records displayed on the form
In that case, we would probably need some more information. Can you post the code for your button?
 
The best way to go about this might be to use the button to activate a Query. Can this be written in a query? If not, then you would need to open a recordset and process it with vba (then close it).
 
Perhaps you could put the calculation code (which you already have worked out), in the form's OnCurrent event.
Or, encapsulate the code in a VBA function, and then set a control textbox's value to the function.
 
Perhaps you could put the calculation code (which you already have worked out), in the form's OnCurrent event.
Or, encapsulate the code in a VBA function, and then set a control textbox's value to the function.
Hi pisorsisaac, the code for the calculations is in the button's onclick event .
I'll post snippets when I get back to my machine
 
The best way to go about this might be to use the button to activate a Query. Can this be written in a query? If not, then you would need to open a recordset and process it with vba (then close it).
Hi HalloweenWeed, that's the root I'm trying to take but my do while... movenext loop doesn't seem to move to the next record.
I'm obviously doing something stupid (won't be the first time) so I'll post the onclick event code when I get back to my machine
 
Oh I'm sorry, I must have misunderstood. I thought this was a "per-record" calculation, which you wanted to display on each record without a click being required.
 
The best way to go about this might be to use the button to activate a Query. Can this be written in a query? If not, then you would need to open a recordset and process it with vba (then close it).

Code Tags Added by UG
Please use Code Tags when posting VBA Code

Please feel free to Remove this Comment
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

Code:
Private Sub cmdCalculatePayroll_Click()

On Error GoTo Err_Handler
    Dim dbs As DAO.Database
    Dim rsQuery As DAO.Recordset
  
    Set dbs = CurrentDb
    'Open a dynaset-type Recordset using a saved query
    Set rsQuery = dbs.OpenRecordset("qryStaffMonthlyPayroll", dbOpenDynaset)

    GetNASSITAndNRARates

    'Begin row processing

    Do While Not rsQuery.EOF

strStaffJobType = Me.StaffJobType
        'Retrieve the member of Staff's DoB for use in calculating Age
        dtStaffDoB = Me.StaffDateofBirth
        sngBasicSalary = Nz(Me.BasicSalary, 0)
        'Called so we can calculate the Allowances
        Me.txtTotalAllowances = CalculateAllowances
        'Called so we can calculate the Gross Salary
        Me.txtGrossIncome = CalculateGrossSalary
        'Called so we can calculate the Nassit Deductions
        Me.txtNassitDeds = CalculateNassitDeduction
       Me. txtTaxableIncome = CalculateTaxableIncome
        'Called so we can calculate the Nassit and NRA Deductions
        Me.txtNraDeds = CalculateNraDeductions
        Me.NRATaxDed = sngNraDeds
        Me.NetIncome = CalculateNetIncome

        rsQuery.MoveNext

    Loop

    MsgBox "Monthly Payroll has been calculated", vbInformation + vbOKOnly, "cmdCalculatePayroll_Click"

    'Cleanup
    rsQuery.Close

Exit_cmdCalculatePayroll_Click:

    Set rsQuery = Nothing

    Exit Sub

Err_Handler:

    MsgBox Err.Description, vbInformation, "cmdCalculatePayroll_Click"

    Resume Exit_cmdCalculatePayroll_Click

End Sub
 
Last edited by a moderator:
You appear to be using Functions, but no indication as to who is being processed.?
Might also want to MoveFirst before processing the recordset (to be on the safe side?)
 
Code Tags Added by UG
Please use Code Tags when posting VBA Code

Please feel free to Remove this Comment
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/

Code:
Private Sub cmdCalculatePayroll_Click()

On Error GoTo Err_Handler
    Dim dbs As DAO.Database
    Dim rsQuery As DAO.Recordset
 
    Set dbs = CurrentDb
    'Open a dynaset-type Recordset using a saved query
    Set rsQuery = dbs.OpenRecordset("qryStaffMonthlyPayroll", dbOpenDynaset)

    GetNASSITAndNRARates

    'Begin row processing

    Do While Not rsQuery.EOF

strStaffJobType = Me.StaffJobType
        'Retrieve the member of Staff's DoB for use in calculating Age
        dtStaffDoB = Me.StaffDateofBirth
        sngBasicSalary = Nz(Me.BasicSalary, 0)
        'Called so we can calculate the Allowances
        Me.txtTotalAllowances = CalculateAllowances
        'Called so we can calculate the Gross Salary
        Me.txtGrossIncome = CalculateGrossSalary
        'Called so we can calculate the Nassit Deductions
        Me.txtNassitDeds = CalculateNassitDeduction
       Me. txtTaxableIncome = CalculateTaxableIncome
        'Called so we can calculate the Nassit and NRA Deductions
        Me.txtNraDeds = CalculateNraDeductions
        Me.NRATaxDed = sngNraDeds
        Me.NetIncome = CalculateNetIncome

        rsQuery.MoveNext

    Loop

    MsgBox "Monthly Payroll has been calculated", vbInformation + vbOKOnly, "cmdCalculatePayroll_Click"

    'Cleanup
    rsQuery.Close

Exit_cmdCalculatePayroll_Click:

    Set rsQuery = Nothing

    Exit Sub

Err_Handler:

    MsgBox Err.Description, vbInformation, "cmdCalculatePayroll_Click"

    Resume Exit_cmdCalculatePayroll_Click

End Sub
Thank you
 
You appear to be using Functions, but no indication as to who is being processed.?
Might also want to MoveFirst before processing the recordset (to be on the safe side?)
Hi Gasman, I don't quite understand what you're saying re, "but no indication as to who is being processed."
I've tried "movefirst", unsuccesfully
 
Code Tags Added by UG
Please use Code Tags when posting VBA Code
UG? - FYI - 2 pics are missing from the beginning of that linked thread. Perhaps they don't apply anymore either, given that the forum was upgraded, or maybe the paths were altered.
 
I'm going to guess that qryStaffMonthlyPayroll" is not the query that the form is based on. Thus the (recordset) rs you're looping over sets the fields in that query (assuming it is in fact updatable) to the form field values - for every rs record; i.e. every record in that recordset gets the same values in each field as what's in the form fields pertaining to the current record.

If the query is what the form's record source is, the answer to your original question would be - you don't have to do anything but requery the form and if every record needs to have the same values (as I've pointed out), then you'll see that as you navigate over the form's records.

If none of that is true, then perhaps what you're missing is that you're not looping over the form records, just the rs ones. Or you're asking how to modify the code so that it does loop over the form records and you already realize that's not happening. Which (or even if it's neither) isn't clear, at least to me. If it was to others, then I think we'd be further ahead than what we are at present.
 
Hi Gasman, I don't quite understand what you're saying re, "but no indication as to who is being processed."
I've tried "movefirst", unsuccesfully
You open the recordset, you are on the first record
You set various controls on the form, and then get the next record until EOF.

I do not understand how you can identify which employee you are working on? In fact you do not appear to even use anything from the query recordset.?
 
You set various controls on the form, and then get the next record until EOF.
In fact you do not appear to even use anything from the query recordset.?
I didn't see it that way. I see a recordset based on a query, looping through the rs and an attempt to set the rs field values to be whatever the form controls are. Then move to the next rs record and set that record's values to the exact same form values because the form record is the same. I don't see form controls being updated - I see that as being the other way around. However, if you can update a query that way it is news to me.
Have I misinterpreted the code?
 
More likely I have, but I could not see rsQuery!whatever being set.?
I only see form controls mainly being set from various functions.?

I would expect for a recordset that fields would be set, then an .Update being performed. :confused:
 

Users who are viewing this thread

Back
Top Bottom