After update (1 Viewer)

[dullster]

I have a Main form with 2 subforms. The main form contains records from the Table “Employees”, showing “Hourly Rate, “Hours per Pay Period” and “Total of paycheck”. I have added a Text Box that calculates Overtime owed if there is any.

Subform 1 shows the calculation of the Taxes owed from the field “Total of paycheck” on the main form.

Subform 2 is to manually enter the calculated field into the Transaction Table

After calculating the regular hours, plus overtime hours, I want to enter that calculated amount to “Total of paycheck” on the main form and recalculate the Taxes owed in Subform 1.

I have the AfterUpdate set to

Private Sub Total_of_pay_Check_AfterUpdate()
Form"frmpayrollrecommsub"Requery
End Sub

I'm getting Syntax Error. I've tried several different arguments and it's not working.

 

[dullster]

I have a Main form with 2 subforms. The main form contains records from the Table “Employees”, showing “Hourly Rate, “Hours per Pay Period” and “Total of paycheck”. I have added a Text Box that calculates Overtime owed if there is any.

Subform 1 shows the calculation of the Taxes owed from the field “Total of paycheck” on the main form.

Subform 2 is to manually enter the calculated field into the Transaction Table

After calculating the regular hours, plus overtime hours, I want to enter that calculated amount to “Total of paycheck” on the main form and recalculate the Taxes owed in Subform 1.

I have the AfterUpdate set to

Private Sub Total_of_pay_Check_AfterUpdate()
Form"frmpayrollrecommsub"Requery
End Sub

I'm getting Syntax Error. I've tried several different arguments and it's not working.

I tried

Private Sub Total_of_pay_Check_AfterUpdate()
Me.frmpayrollrecommsub.Form.Requery
End Sub

I'm getting
Compile error, Method or data member not found

 

[tvanstiphout]

When you typed "Me.", was the form in the Intellisense list? Then it should compile.
Decompile the app and see if that helps.

 

[dullster]

When you typed "Me.", was the form in the Intellisense list? Then it should compile.
Decompile the app and see if that helps.

I don't know what you mean by Intellisence List. I did a decompile but it didn't help

 

[dullster]

I tried

Private Sub Total_of_pay_Check_AfterUpdate()
Me.frmpayrollrecommsub.Form.Requery
End Sub

I'm getting
Compile error, Method or data member not found

I tried this
Private Sub Total_of_pay_Check_AfterUpdate()
Me.Requery
End Sub

It does a requery of the frmpayrollrecommsub but then exists to the first employee and starts a new record.

 

[dullster]

I tried this
Private Sub Total_of_pay_Check_AfterUpdate()
Me.Requery
End Sub

It does a requery of the frmpayrollrecommsub but then exists to the first employee and starts a new record.

I think I finally succeeded. I tried this

Private Sub Total_of_pay_Check_AfterUpdate()
Forms![FrmPayroll]![frmCDpayrollrecommsub].Requery
End Sub

AND created a update Salary button to update the Employee table. Then it worked.

 

[Gasman]

Requery the recordset of the form and not the form itself, if you do not want the record pointer to move.

 

[dullster]

Requery the recordset of the form and not the form itself, if you do not want the record pointer to move.

How is that done?

 

[dullster]

Requery the recordset of the form and not the form itself, if you do not want the record pointer to move.

This is what i currently have

Private Sub Form_AfterUpdate()
[Forms]![frmPayroll]![frmCDpayrollrecommsub].[Form].Refresh
End Sub

 

[cheekybuddha]

Is your code in frmPayroll's module?

If so, I think @Gasman means you try something like:

Private Sub Form_AfterUpdate()
  Me.frmCDpayrollrecommsub.Form.Recordset.Requery
End Sub

 

[dullster]

How is that done?

I tried this with the same results

Private Sub Form_AfterUpdate()
Me.Recordset.Requery
End Sub

Is your code in frmPayroll's module?

If so, I think @Gasman means you try something like:

Private Sub Form_AfterUpdate()
  Me.frmCDpayrollrecommsub.Form.Recordset.Requery
End Sub

Same results. It starts a new record in the 2nd subform

 

[cheekybuddha]

Can you post a copy of your db and instructions to reproduce what you see.

We do not have enough information to go on with what you have described. Someone will be able to take a look and see what is going wrong

 

[The_Doc_Man]

OK, syntax depends on where this is done.

IF the code is running within the context of an event on the main form and frmCDpayrollrecommsub is the name of the CONTROL in which you have loaded a sub-form,

Me.frmCDpayrollrecommsub.Form.Requery

should requery the sub-form in isolation. If the sub-form is bound AND is linked to a field in the main form, navigating or requerying the main form will affect the sub-forms. If the sub-form is bound but NOT linked to the main form, you might have to intervene in code by manually running the .Requery on the sub, or manipulate the sub-form by navigating through code.

 

[dullster]

Can you post a copy of your db and instructions to reproduce what you see.

We do not have enough information to go on with what you have described. Someone will be able to take a look and see what is going wrong

On the Main Menu, click on the Utilities button, then click the Payroll button.

In the subform for the first Employee, the type is W2 Payroll check, MyUCA is 51100, enter the check amount of $334.34.
As an example, the employee gets $75.00 for insurance premium.

To recalculate the tax on the total of $408.34, you enter that amount in the Recalculate Taxes on the Main form.

It creates a new record in the subform instead of staying on that subform record.

 

[Pat Hartman]

I'm sorry to be so harsh but what you are doing is so wrong on so many levels, I can't even begin to fix it. It's like you are entering the answers and then trying to back into the arguments of the calculation. Also, the directions to recreate the issue make no sense. I got as far as entering 334.34 on the firs row. No date was entered and no error was raised. The total on the main form showed 333.34. How did that happen? I didn't even look at the code to try to figure it out, not that I could find any code to look at. There is no code in any subform event and no code in any of the control events on the subform. Then you said "As an example, the employee gets $75.00 for insurance premium." What does that mean? Where is the 75 entered? Being really confused, I put the form into design view to see if I could figure out where the data should go. Couldn't make sense of the schema. Went back to the form and the record had disappeared. At least I figured that out - the subform is set to DataEntry so it only shows new records and always opens "empty". Maybe this is the answer to your problem but I can't figure out what you are doing to create a new record. Then I changed the salary on the main form to 408.34 and pressed the recalc button which seemed to recalculate so the directions did not lead me to creating a second record.

You seem to have some written information with the employee salary already calculated and the insurance already specified, etc and you are now trying to paste these figures into this form.

Did you write this application? When? Is it currently being used? Where is everything actually being calculated? What are you looking at to do the data entry? Why are you modifying the existing program? You should NEVER be creating each payroll record one by one by typing in data. The payroll should require NO data entry except for the number of hours worked if an employee is hourly. PERIOD. And all the hours should be entered in BEFORE ANY payroll record is calculated. The payroll is calculated in code in a loop by reading each active employee record and if the salary is fixed dividing by the number of pay periods per year to come up with a period amount or by multiplying the hours for this period times the hourly rate. Then the OT hours are multiplied by the OT rate. Then the deductions are calculated each as a separate column (which is wrong but we'll let you get away with it) as well as the taxes - again using columns rather than rows. Then the code moves on to the next record. You/someone are doing an enormous amount of totally unnecessary data entry and there is no validation to prevent errors.

If you are prepared to start payroll again from scratch, we can help you to define a simple schema with fixed columns for taxes, deductions, hours, OT hours, etc. It won't hold up if the IRS or state changes anything and it will be technically wrong and a PITA to change if you have to add new deduction types but it will be comprehensible and not require any data entry beyond the hours for each hourly employee for each period.

 

[dullster]

I'm sorry to be so harsh but what you are doing is so wrong on so many levels, I can't even begin to fix it. It's like you are entering the answers and then trying to back into the arguments of the calculation. Also, the directions to recreate the issue make no sense. I got as far as entering 334.34 on the firs row. No date was entered and no error was raised. The total on the main form showed 333.34. How did that happen? I didn't even look at the code to try to figure it out, not that I could find any code to look at. There is no code in any subform event and no code in any of the control events on the subform. Then you said "As an example, the employee gets $75.00 for insurance premium." What does that mean? Where is the 75 entered? Being really confused, I put the form into design view to see if I could figure out where the data should go. Couldn't make sense of the schema. Went back to the form and the record had disappeared. At least I figured that out - the subform is set to DataEntry so it only shows new records and always opens "empty". Maybe this is the answer to your problem but I can't figure out what you are doing to create a new record. Then I changed the salary on the main form to 408.34 and pressed the recalc button which seemed to recalculate so the directions did not lead me to creating a second record.

You seem to have some written information with the employee salary already calculated and the insurance already specified, etc and you are now trying to paste these figures into this form.

Did you write this application? When? Is it currently being used? Where is everything actually being calculated? What are you looking at to do the data entry? Why are you modifying the existing program? You should NEVER be creating each payroll record one by one by typing in data. The payroll should require NO data entry except for the number of hours worked if an employee is hourly. PERIOD. And all the hours should be entered in BEFORE ANY payroll record is calculated. The payroll is calculated in code in a loop by reading each active employee record and if the salary is fixed dividing by the number of pay periods per year to come up with a period amount or by multiplying the hours for this period times the hourly rate. Then the OT hours are multiplied by the OT rate. Then the deductions are calculated each as a separate column (which is wrong but we'll let you get away with it) as well as the taxes - again using columns rather than rows. Then the code moves on to the next record. You/someone are doing an enormous amount of totally unnecessary data entry and there is no validation to prevent errors.

If you are prepared to start payroll again from scratch, we can help you to define a simple schema with fixed columns for taxes, deductions, hours, OT hours, etc. It won't hold up if the IRS or state changes anything and it will be technically wrong and a PITA to change if you have to add new deduction types but it will be comprehensible and not require any data entry beyond the hours for each hourly employee for each period.

Let's try a new employee. The wages are entered in tblemployees because most are salary. There is only 1 employee "Stacey" that could get OT, the rest are salary and the amount never changes.

Go to Employee #6 - "Stacey"
Enter 2 Hours Of OT

Enter on line 1 Date: 6/1/25 Check#: 9000 Trans Type: W2 payroll Check MyUCA: 53300 Amount: 2080.00
Enter on Line 2 Date: 6/1/25 Check#: Should Populate Trans Type: W2 payroll check MyUCA: 53300 Amount 78.00 Note: 2 Hrs OT

Then I enter the new Total of $2158.00 Into the Recalculate Taxes on to recalculate the Taxes. This is when the Subform data disappears.

To answer your questions. No, someone else wrote it years ago. I can attach it if your interested in seeing it. I'm just modifying it to be user friendly.

The old version is still in use. The tax calculations are done in 3 queries with the State Taxes calculated in VBA because they are a strange long calculation. The information is then compiled in the QryCDpayrollrecomm.

I'm not opposed to changing it. Do you have a sample of what you are describing?

 

[dullster]

OK, syntax depends on where this is done.

IF the code is running within the context of an event on the main form and frmCDpayrollrecommsub is the name of the CONTROL in which you have loaded a sub-form,

Me.frmCDpayrollrecommsub.Form.Requery

should requery the sub-form in isolation. If the sub-form is bound AND is linked to a field in the main form, navigating or requerying the main form will affect the sub-forms. If the sub-form is bound but NOT linked to the main form, you might have to intervene in code by manually running the .Requery on the sub, or manipulate the sub-form by navigating through code.

they are both bound by EmployeeID. Do you know where i can find information or how i would go about running a code manually to Requery?

 

[Pat Hartman]

I guess I haven't been clear. When you "do" payroll, you enter NO DATA AT ALL. PERIOD. Prior to running payroll, you enter all the hours and OT hours for the hourly employees. You enter NO OTHER DATA AT ALL. Running payroll is a matter of pushing a button and calculating all the taxes for this period and then generating all the checks or EFTs. It doesn't get any simpler than that. Your current process has completely unnecessary data entry. Why would you want to make the users enter data you already know? Why would you want to force them to "touch" every single employee record in order to run a payroll? Why do you want them to copy data from one control to another and press a recalc button? Please, please, step back and think about what you are forcing them to do or more likely, how you are perpetuating the very poor interface and are not making it better.

No one has sample payroll applications lying around, especially ones with non-conforming schemas that mimic spreadsheets. No one builds payroll applications today. I've only actually ever built three payroll applications. They were very early in my career because payroll was probably the first major application that everyone either outsourced or bought third party apps to run in house. And one of them was for the government of Kuwait which is how I came to have to read large sections Shariah Law.

Try to think about this and finish defining these tables. Notice that neither the table names nor their contents bear any resemblance to what you have now.

tblEmployee
One row per employee with all demographic data and active flag.
EmpID (autonumber, PK)
EmployeeNumber (state's ID)
ActiveYN
demographic information

tblSalary
One row per employee per salary change. Used to calculate payroll each period
SalaryID (autonumber, PK)
EmpID (FK to tblEmployee) (uniqueIdx, fld1)
SalaryEffectiveDT (uniqueIdx, fld2)
SalaryEndDT
HourlyRate
AnnualSalary
PayMethod
EFTAccount
tax status data

tblDeductions
Definition of deductions
DedID (autonumber, PK)
ActiveYN
DedName
DedType
...

tblSalaryDeductions
Deductions for each employee. Used to calculate payroll each period
SalDedID (autonumber, PK)
ActiveYN
SalaryID (FK to tblSalary)
DedID (FK to tblDeductions)
DedAmt
...

tblTaxRates
...

tblPayPeriod
Header record for each pay period
PayPeriodID (autonumber, PK)
PmtDT

tblPayPeriodHours
One record per hourly employee per pay period. Used to calculate salary for pay period
PayPerHrsID (autonumber,PK)
PayPeriodID (FK to tblPayPeriod)
SalaryID (FK to tblSalary)
RegHours
OTHours
HolidyHours
...

tblPayment
One header record per employee per pay period
PaymentID (autonumber, PK)
SalaryID (FK to tblSalary)
CheckNum

tblPaymentDetails
One row per detail item (tax, deduction, hours, salary, etc) per employee, per pay period. Results of payroll calculations. Used to print checks and create EFT and other exported files
PayDetID (autonumber, PK)
PaymentID (FK to tblPayment)
ItemType
ItemDesc
Amt
....