Table Not Updating Unless Sub Triggered Twice

[gokrip]

In the subrountine below, the table does not get updated unless I click on the chkSetup box twice. Both the first click and the second click the update code is processed evidenced by the msgbox "standard costing updated" triggering. Any Ideas?

Private Sub chkSetup_Click()
Const ConnectionString = "DSN=HKPEDATA;UID=;PWD="
'Dim ProdLot As String
Dim MatNum As String
Dim MoldNum As String
Dim PerfMach As String
Dim PricePerLb As Currency
Dim PartWtg As Single
Dim RunnerWt As Single
Dim NumCavs As Integer
Dim Yield As Single
Dim AnnualProdRuns As Integer
Dim RMLbsPerSetup As Integer
Dim AnnualQty As Long
Dim DLPercent As Single
Dim QAPercent As Single
Dim ActualCycle As Single
Dim Efficiency As Single
Dim SetupHrs As Single
Dim DeprFactor As Single
Dim UtilityFactor As Single
Dim AnnualMaintCost As Integer
Dim AddChargesEa As Currency
Dim ProdLot As String
Dim rst As New ADODB.Recordset
Dim con As New ADODB.Connection
Dim sql As String

On Error Resume Next

If [chkSetup] = False Then
Exit Sub
Else

Call con.Open(ConnectionString)

DoCmd.Hourglass True

shift1 = True
shift2 = True
shift3 = True

PricePerLb = DLookup("[price/lb]", "[material]", "[materialnum] = forms![frmProductionSchedule]![matnum]")
PartWtg = DLookup("[partwtg]", "[part number]", "[partnumber] = forms![frmProductionSchedule]![partnumber]")
RunnerWt = DLookup("[runnerwt]", "[tooling]", "[moldnum] = forms![frmProductionSchedule]![moldnum]")
NumCavs = DLookup("[# cav]", "[tooling]", "[moldnum] = forms![frmProductionSchedule]![moldnum]")
Yield = DLookup("[yield]", "[part number]", "[partnumber] = forms![frmProductionSchedule]![partnumber]")
AnnualProdRuns = DLookup("[annualprodruns]", "[part number]", "[partnumber] = forms![frmProductionSchedule]![partnumber]")
RMLbsPerSetup = DLookup("[rmlbspersetup]", "[tooling]", "[moldnum] = forms![frmProductionSchedule]![moldnum]")
AnnualQty = DLookup("[annualqty]", "[part number]", "[partnumber] = forms![frmProductionSchedule]![partnumber]")
DLPercent = DLookup("[dlpercent]", "[tooling]", "[moldnum] = forms![frmProductionSchedule]![moldnum]")
QAPercent = DLookup("[qapercent]", "[part number]", "[partnumber] = forms![frmProductionSchedule]![partnumber]")
ActualCycle = DLookup("[actual cycle]", "[tooling]", "[moldnum] = forms![frmProductionSchedule]![moldnum]")
Efficiency = DLookup("[efficiency]", "[part number]", "[partnumber] = forms![frmProductionSchedule]![partnumber]")
SetupHrs = DLookup("[setupcharge]", "[tooling]", "[moldnum] = forms![frmProductionSchedule]![moldnum]")
DeprFactor = DLookup("[deprfactor]", "[asset list]", "[assetnum] = forms![frmProductionSchedule]![perfmach]")
UtilityFactor = DLookup("[utilityfactor]", "[asset list]", "[assetnum] = forms![frmProductionSchedule]![perfmach]")
AnnualMaintCost = DLookup("[annualmaintcost]", "[tooling]", "[moldnum] = forms![frmProductionSchedule]![moldnum]")
AddChargesEa = DLookup("[addchargesea]", "[part number]", "[partnumber] = forms![frmProductionSchedule]![partnumber]")

rm = MldgRMCost(PricePerLb, PartWtg, RunnerWt, NumCavs, Yield, _
AnnualProdRuns, RMLbsPerSetup, AnnualQty)

dl = MldgDLCost(DLPercent, QAPercent, NumCavs, ActualCycle, Efficiency, _
Yield, SetupHrs, AnnualProdRuns, AnnualQty, 1)
mach = MldgMachCost(DeprFactor, UtilityFactor, NumCavs, ActualCycle, Efficiency, Yield, 1)

tool = AnnualMaintCost / AnnualQty

pack = Nz(DSum("[loprice]*[qtyper]", "[qryBomDetail]", _
"[usedonpartnum]=forms![frmProductionSchedule]![partnumber] and [pnclass] = '5'"), 0)

other = Nz([AddChargesEa], 0) + Nz(DSum("[loprice]*[qtyper]", "[qryBomDetail]", _
"[usedonpartnum]=forms![frmProductionSchedule]![partnumber] and [pnclass] <> '5' and [pnclass]<> '7'"), 0)

ProdLot = Forms![frmproductionschedule]![ProdLotID]
sql = "SELECT * FROM [tblProdLot] WHERE [ProdLotID] = " & ProdLot & ";"

If Me.Dirty Then
' MsgBox "turning dirty to false"
Me.Dirty = False
End If

Call rst.Open(sql, con, adOpenDynamic, adLockOptimistic)

rst("stdRMCost").Value = Nz(rm, 0)
rst("stdDLCost").Value = Nz(dl, 0)
rst("stdMachCost").Value = Nz(mach, 0)
rst("stdToolCost").Value = Nz(tool, 0)
rst("stdPackCost").Value = Nz(pack, 0)
rst("stdOtherCost").Value = Nz(other, 0)
rst.Update
rst.Close
con.Close

' MsgBox "prior to refresh"
Refresh

Set rst = Nothing
Set con = Nothing
DoCmd.Hourglass False
MsgBox "Standard costing updated."

End If
End Sub

[hansup]

In the subrountine below, the table does not get updated unless I click on the chkSetup box twice. Both the first click and the second click the update code is processed evidenced by the msgbox "standard costing updated" triggering. Any Ideas?

What happens if you click chkSetup once, get the MsgBox "Standard costing updated.", then immediately close the form, wait 1 minute, re-open the form and navigate back to the same row?

Do you see the original or updated values?

[gokrip]

Hansup, thanks for your response. Once returning to the form (per your suggestion) the fields are updated fine. If I remove the "me.dirty" code, a write conflict message comes up leading me to believe there is a problem with the data. The form is based on a qry while the table i'm writing to in this sub is one of those tables used in the qry. Still interesting how once the data is "dirty" is works fine. Probably the chkSetup value coming in as Null being changed to a False/True once it's clicked that first time.

[hansup]

Once returning to the form (per your suggestion) the fields are updated fine.
Good. That's what I expected.


If I remove the "me.dirty" code, a write conflict message comes up leading me to believe there is a problem with the data.

If the form is dirty (Me.Dirty = True), that means you have unsaved changes to one or more of the fields in the current record. Meanwhile your ADO code attempts to make changes to the very same record. A write conflict error is not at all surprising.


The form is based on a qry while the table i'm writing to in this sub is one of those tables used in the qry. Still interesting how once the data is "dirty" is works fine. Probably the chkSetup value coming in as Null being changed to a False/True once it's clicked that first time.

The form uses DAO with the database engine. Your ADO code uses a separate connection to the engine, makes its changes, then exits. Then your Requery happens before the engine has finished saving the changes made from ADO ... so you saw the previous values. Closing the form after the first click, then re-opening it allowed you to verify that the changes did in fact get saved ... without clicking the check box a second time.

I would almost bet real money your problem would go away if you made the changes with a DAO (instead of ADO) recordset. In that case the time lag would be less or perhaps even undetectable ... since both the form and the update code would be using the same database connection.

I don't see any reason you need ADO here; DAO could do the same thing. And DAO is the better choice (IMO) for native Jet/ACE data sources ... that's what it's specifically designed for. ADO is useful for other types of data sources. Additionally ADO offers some features with Jet/ACE data that DAO doesn't provide; however you're not using any of them.

Performance can be another persuasive reason to choose DAO instead of ADO for Jet/ACE data sources. DAO can be a lot faster.

But looking at this again now, I'm skeptical whether you really need to open a recordset at all, regardless of whether it's DAO or ADO.

For example your code writes the "rm" value to a field named stdRMCost. But your form must include a data control bound to that field. (You hope to view an updated value in the form, so you must have a data control bound to it.) Let's say the control is a text box named txtStdRMCost. Instead of writing rm to a recordset field, write it to the text box:

Me.txtStdRMCost = rm

Same for the other values you were writing. Then to save those changes, use:

Me.Dirty = False

[gokrip]

Hansup, thanks again! Your idea of adding the "std" fields to the form worked spendidly. I don't show these fields on to the user so previously there were not on the form, but i added them as invisible fields and also added them to the underlying query.

Your idea about DAO is intriguing and I will try that too. Otherwise, I'm good to go. THANKS A TON!