Re: MS Access front end - MySQL back end
Continuation of previous posts:
Sometime instead of using stored procedures to update multiple tables, I would like to use vba calculations using text box values. Here is an attempt:
UseDSum = True works.
UseDSum = False works if you step through in debug mode.
UseDSum = False does not work running normally. At Me.Parent.Refresh a
Write Conflict error popsup with Save Record grayed out. I'm developing
the database so there are no other users.
Private Sub RecalcLIViaForm()
On Error GoTo RecalcLI_Err
Dim cmdStr As String
Dim v As Variant
Dim criteria As String
Dim LISum As Double
Dim JobSum As Double
Dim JobEarnedSum As Double
Dim UseDSum As Boolean
UseDSum = Nz(chkUseDSum.Value, False)
If Me.ActiveControl.Value = Me.ActiveControl.OldValue Then
Exit Sub
End If
'update lineitem values
Me.Refresh
[EXTTOTAL] = [UNITS] * [PRICE] * IIf([USEPRORATE], [PRORATEP] / 100, 1)
Me.Refresh
'update jobs values
If UseDSum Then
criteria = "JOBNO=" & [JOBNO]
LISum = DSum("EXTTOTAL", "lineitems", criteria)
Me.Parent.[JOBVALUE].Value = LISum
Me.Parent.[EARNED].Value = Me.Parent.[JOBVALUE].Value * Nz(Me.Parent.[PCENTDONE].Value, 0) / 100
Else
Me.Parent.[JOBVALUE].Value = txtLISum.Value
Me.Parent.[EARNED].Value = Me.Parent.[JOBVALUE].Value * Nz(Me.Parent.[PCENTDONE].Value, 0) / 100
End If
v = SysCmd(acSysCmdSetStatus, "Set Jobs values fields")
Me.Parent.Refresh 'bombs on execution, but works if stepping through with debug
v = SysCmd(acSysCmdSetStatus, "Refreshed Jobs")
'update project values
If UseDSum Then
criteria = "PROJID=" & [PROJID]
JobSum = DSum("VALUE", "jobs", criteria)
Me.Parent.Parent.[ProjectVALUE].Value = JobSum
JobEarnedSum = DSum("EARNED", "jobs", criteria)
Me.Parent.Parent.[WIP].Value = JobEarnedSum
Else
Me.Parent.Parent.[ProjectVALUE].Value = Me.Parent.txtJOBValueSum.Value
Me.Parent.Parent.[WIP].Value = Me.Parent.txtJOBEARNEDSum.Value
End If
If Me.Parent.Parent.[ProjectVALUE].Value <> 0 Then
Me.Parent.Parent.[PERCENT].Value = Me.Parent.Parent.[WIP].Value / Me.Parent.Parent.[ProjectVALUE].Value * 100
End If
v = SysCmd(acSysCmdSetStatus, "Set Project Values Fields")
Me.Parent.Parent.Refresh
v = SysCmd(acSysCmdSetStatus, "Refreshed Projects")
RecalcLI_Err_Exit:
Exit Sub
RecalcLI_Err:
v = SysCmd(acSysCmdSetStatus, "Err.Number " & CStr(Err.Number))
MsgBox Error$
Resume RecalcLI_Err_Exit
End Sub
I would appreciate any advice.
Edited 2 time(s). Last edit at 05/16/2012 04:41PM by Daniel Hofer.