Dynamic SQL Double Count

  • Thread starter Thread starter Allan Koodray
  • Start date Start date
A

Allan Koodray

I'm banging my head on this one -

I created a Dynamic SQL routine (an Update Query) to
update payroll information for a budget collection
process by cost center (department). The query has three
criteria - the plan name, the cost center number and the
account number. For some odd reason, the query updates
the correct plan, the correct cost center but updates
both the specified account (which is hard coded) and the
FIRST record limited by only the plan and cost center.
The macro is initiated by a macro button on a from that
is restricted by a selection query.

For example:
The user is prompted by a form to select a plan and cost
center, then selects OPEN. A form based query is opened
(actual contains several forms restricted by a selection
query). The user selects a tab used to edit budget data
(supplies..., labor). Obviously this subform contains
more than just one record. The user then clicks
the "Update Payroll" button to change the payroll record.
It is here that both the payroll account and the first
account within the cost center / plan combination is also
updated (the table is auto numbered).

Any ideas on what is wrong or how to circumvent the
problem.

Allan
 
-----Original Message-----
I'm banging my head on this one -

I created a Dynamic SQL routine (an Update Query) to
update payroll information for a budget collection
process by cost center (department). The query has three
criteria - the plan name, the cost center number and the
account number. For some odd reason, the query updates
the correct plan, the correct cost center but updates
both the specified account (which is hard coded) and the
FIRST record limited by only the plan and cost center.
The macro is initiated by a macro button on a from that
is restricted by a selection query.

For example:
The user is prompted by a form to select a plan and cost
center, then selects OPEN. A form based query is opened
(actual contains several forms restricted by a selection
query). The user selects a tab used to edit budget data
(supplies..., labor). Obviously this subform contains
more than just one record. The user then clicks
the "Update Payroll" button to change the payroll record.
It is here that both the payroll account and the first
account within the cost center / plan combination is also
updated (the table is auto numbered).

Any ideas on what is wrong or how to circumvent the
problem.
Click to expand...

Here is the SQL code:
"UPDATE tblCostCtrData SET tblCostCtrData.Jan=" & Jan
", tblCostCtrData.Feb=" & Feb & _
", tblCostCtrData.Mar = " & Mar & ",
tblCostCtrData.Apr=" & Apr & _
", tblCostCtrData.May = " & May & ",
tblCostCtrData.Jun=" & Jun & _
", tblCostCtrData.Jul = " & Jul & ",
tblCostCtrData.Aug=" & Aug & _
", tblCostCtrData.Sep = " & Sep & ",
tblCostCtrData.Oct=" & Oct & _
", tblCostCtrData.Nov = " & Nov & ",
tblCostCtrData.Dec=" & Dec & _
" WHERE tblCostCtrData.CostCenter='" &
Form_frmCostCtrData.CostCenter & "' And " & _
"tblCostCtrData.SAPAcct='517999' AND
tblCostCtrData.Plan='" & Form_frmCostCtrData.Plan & "';"
 
Back
Top