Subform requery is starting a new record (1 Viewer)

[dullster]

After update

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...

www.access-programmers.co.uk

I just realized I had posted this question a couple of weeks ago, I thought it was fixed, I guess the problem persists. I don't know how to remove this post.

I have a form to create payroll checks. It has 2 subform, 1 calculates the taxes on the paycheck amount and the 2nd is to fill in the data that is provided for the check. If the employee has over time, I have a field in the main form to enter the income with overtime and it is set to requery the 1st form to recalculate the new taxes. When the form does a requery, the 2nd subform jumps to a new record instead of staying on the current record.

I changed the Cycle on the Main form to Current Page, 1st subform to Current record and 2nd subform set to all records but nothing has changed. I'm thinking maybe it has to do with the screen refresh. Does anyone know how to keep the 2nd subform on the current record after the refresh of the 1st sub form?

 

[Pat Hartman]

We've all told you that your process is incorrect and so is your schema. Obviously you shouldn't be calculating taxes until the data entry is complete. AND the OT hours should never be entered on the same form as the demographic information. Each wage category should be entered as a separate row in a child table of a person's payroll record. I thought you were in some small African country and had no money to spend at all. Turns out you are in the US. Get a payroll program or service. This is not something to do yourself. I even did a search and posted a link for you in one of your many other threads on the same topic to help you to find something within your budget.

The data entry should be completely separate from the calculation of the payroll. Have the employees enter the timesheet data themselves OR have them write it on forms and give it to a clerk to do the data entry. Once a week, or bi-weekly or whatever works for your payroll period, you validate that all expected data entry has been made. Then you run a batch job that calculates the payroll for all employees at one go and creates the check table as well as the check details. Each taxing authority is a separate line item. SS, State, Local, Federal, Medicare, etc. Each type of wage is a separate line item summed for the period and each deduction type is also a separate line item summed for the period. This parent/child payroll table is used to produce the checks or to create the output that will be sent to a third party provider to complete the process. Most companies do NOT calculate taxes themselves. They send the raw data records to the third-party provider and that company makes it it's business to keep on top of tax laws and rates so you don't have to. They send you back a return file with all the calculated tax data in it. they print/mail checks or use EFT to distribute the wages from YOUR bank account. They also put together the withholding report and send it to the IRS along with an ETF for the correct amount of withholding so you don't get fined and sent to jail for being 3 days late. The IRS takes withholding very seriously and punishes you severely if you are the slightest bit sloppy about the process. These people are now armed, there are 87,000 new agents hired by Biden/Harris to harass small businesses (especially cash businesses) and employees who claim the earned income credit and you really don't want them knocking on your door.

 

[dullster]

We've all told you that your process is incorrect and so is your schema. Obviously you shouldn't be calculating taxes until the data entry is complete. AND the OT hours should never be entered on the same form as the demographic information. Each wage category should be entered as a separate row in a child table of a person's payroll record. I thought you were in some small African country and had no money to spend at all. Turns out you are in the US. Get a payroll program or service. This is not something to do yourself. I even did a search and posted a link for you in one of your many other threads on the same topic to help you to find something within your budget.

The data entry should be completely separate from the calculation of the payroll. Have the employees enter the timesheet data themselves OR have them write it on forms and give it to a clerk to do the data entry. Once a week, or bi-weekly or whatever works for your payroll period, you validate that all expected data entry has been made. Then you run a batch job that calculates the payroll for all employees at one go and creates the check table as well as the check details. Each taxing authority is a separate line item. SS, State, Local, Federal, Medicare, etc. Each type of wage is a separate line item summed for the period and each deduction type is also a separate line item summed for the period. This parent/child payroll table is used to produce the checks or to create the output that will be sent to a third party provider to complete the process. Most companies do NOT calculate taxes themselves. They send the raw data records to the third-party provider and that company makes it it's business to keep on top of tax laws and rates so you don't have to. They send you back a return file with all the calculated tax data in it. they print/mail checks or use EFT to distribute the wages from YOUR bank account. They also put together the withholding report and send it to the IRS along with an ETF for the correct amount of withholding so you don't get fined and sent to jail for being 3 days late. The IRS takes withholding very seriously and punishes you severely if you are the slightest bit sloppy about the process. These people are now armed, there are 87,000 new agents hired by Biden/Harris to harass small businesses (especially cash businesses) and employees who claim the earned income credit and you really don't want them knocking on your door.

I'm not sure my process in incorrect. I've worked hard to get the calculations right. I agree, taxes shouldn't be calculated until all entries are complete, thus why i posted the database and the directions so people can see what is happening as entries are done and yes, you are correct, OT should be on it's own line, as well as insurance reimbursement, mileage reimbursement, etc.

This is a database that the towns are already using and they are calculating taxes in a excel spreadsheet. Funds are very limited for the Towns, balancing the budget every year is a struggle and they don't have funds to pay a tax preparer to do their payroll. So they would continue to do it the cheapest way the can. This database includes all the categories for the yearly CT report that their Levy and State Share is based off from. They can barely afford the software, let alone yearly updates. Because funds are limited, they would do all their books in a excel spreadsheet before they would spend money on an accountant. There are very few employees, like under 10, with 8 of those 10 on a Salary that never changes except at election time, so one time in 2 years.

With all that being said, I did see your suggestions. But I also know what the end result would be, no town would use them. They can't afford them. They're already saying they can't afford the yearly renewals for QB. What I really need, is help with what I am trying to do, not someone telling me I'm an idiot for doing it. I appreciate all you have contributed.

 

[Pat Hartman]

Without the current version of the database and instructions on how to reproduce the problem, no one can help you. Your description in #1 of this thread indicates that the data entry is not separate from the calculation or this wouldn't be happening. ALL data entry must be complete for every employee before ANY calculation is performed. You are recalculating as each record is entered. Payroll is a batch process, not an iterative one. It is done ONCE per pay period. Verified. And marked complete. It does not change after it has been verified. Each change requires the recalculation of the complete payroll, not just a single employee record. If a change is made to a prior payroll, it goes in THIS pay period as an adjustment. There are some very basic accounting practices that need to be followed so you have some level of auditability. Once a report goes to the IRS, you can't just change the underlying data.

 

[dullster]

Without the current version of the database and instructions on how to reproduce the problem, no one can help you. Your description in #1 of this thread indicates that the data entry is not separate from the calculation or this wouldn't be happening. ALL data entry must be complete for every employee before ANY calculation is performed. You are recalculating as each record is entered. Payroll is a batch process, not an iterative one. It is done ONCE per pay period. Verified. And marked complete. It does not change after it has been verified. Each change requires the recalculation of the complete payroll, not just a single employee record. If a change is made to a prior payroll, it goes in THIS pay period as an adjustment. There are some very basic accounting practices that need to be followed so you have some level of auditability. Once a report goes to the IRS, you can't just change the underlying data.

The DB and the directions are posted over here.

After update

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...

www.access-programmers.co.uk