MySQL Forums
Forum List  »  Microsoft Access

Re: MS Access front end - MySQL back end
Posted by: Daniel Hofer
Date: May 16, 2012 03:26PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: MS Access front end - MySQL back end
3100
May 16, 2012 03:26PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.