Insert Statement

[burrina]

I have a Main form with 2 subforms on it. It is used for Employee Expense Reports.
Main form uses query with tblEmployees and tblExpenseReports
Subform1 uses tblExpenseDetails
Subform2 uses tblExpensePayments

So that an employee can enter expense deductions and expense payments.

I need to insert 2 amounts from those subforms into another table.
Subform1 that uses tblExpenseDetails needs to insert (ExpenseItemAmount) into tblHour
Subform2 that uses tblExpensePayments needs to insert (ExpensePayment) into tblHour

Of course it needs to be for the Employee on the main form which uses a combo to select the employee.

[HansV]

When do you want this to happen?
Do you want to insert the data for the current record in each of the subforms, or for all records in the subforms, or ...?

[burrina]

My thinking is that it needs to the AfterUpdateEvent and for all of the records since you can have many records per subform. i.e. multiple expense deductions and multiple expense payments for that employee. Therefore the sum of them!

[HansV]

And is the EmployeeID the only link?

[burrina]

The subforms are of course linked.
Subform1 is linked by ExpenseReportsID which is for Expense Deductions
Subform2 is linked by EmployeeID and ExpenseReportsID

They both have a Fake Key named txtemployeeid that refers to the main form PK which is EmployeeID

Here is a stab at the code.

'Add Expense Item Amount into tblHour Per Employee.
Dim strSQL As String

strSQL = "INSERT INTO tblHour (ExpenseTotals) "
strSQL = strSQL & "VALUES (#" & Me.txtPaymentTotal & "')"
CurrentDb.Execute strSQL, dbFailOnError


'Add Expense Item Amount into tblHour Per Employee.
Dim strSQL As String

strSQL = "INSERT INTO tblHour (ExpenseTotals) "
strSQL = strSQL & "VALUES (#" & Me.ExpenseItemAmount & "')"
CurrentDb.Execute strSQL, dbFailOnError

[HansV]

Will there be only one record per EmployeeID in tblHour?

[burrina]

Yes and No. You can enter that same employee many times during the current payroll week but of course the expense data should be for that week and employee as well.
Exanple: I can enter James Browns payroll data as an Absence Day and then on a separate record enter payroll hours.
Either way, the expense report data should only be entered once regardless. Payroll form is filtered by current week as well is Expense Report form.

[HansV]

So presumably we must enter more information than just ExpenseTotals in the table. Can you provide ALL relevant information, please?

[burrina]

Main Expense Report Form. Record Source is qryExpenseReports that includes tblEmployees and tblExpenseReports linked by EmployeeID
subform for Expense Deduction uses qryExpenseReportDetails which uses tblExpenseDetails
subform for Expense Payments uses tblExpensePayments

Payroll form uses tblHour and PK is HoursID with FK of EmployeeID

Expense Deductions subform is linked by ExpenseReportID
Expense Payments sybform is linked by EmployeeID and ExpenseReportID

You select a Employee from combo on main Expense Reports form and then enter data for that employee on the subforms.

[HansV]

I'd like to know which data besides ExpenseTotal and EmployeeID need to be entered in tblHour. I assume that HourID is an AutoNumber field, so we won't have to fill that ourselves.

[burrina]

Yes, HoursID is a number and the PK. EmployeeID and txtPaymentTotal and EmployeeID and ExpenseItemAmount

[HansV]

I'm afraid I don't see how this would work, since there is no reference to the pay period.

[burrina]

The reference to the pay period is in the record source for the main form. Between BeginDate and EndDate which is on another form.

[HansV]

But shouldn't the pay period be in tblHour?

[burrina]

The pay period is in tblHour and is set via frmMain which can be changed for whatever so as to view criteria.
Al forms show the date criteria that is filtered via the main form so that normally you would only see the current weeks data on any form.

[HansV]

Does this mean that tblHour is a kind of temporary table?

[burrina]

NO. The Main form of the db is where ALL date criteria is set. In most all of the queries it refers to the dates on the main form.
tblHour is a very important table since it contains all of the payroll data.

[HansV]

As before, I have to admit defeat. I fail to grasp how this would work. Sorry.