Re: MS Access front end - MySQL back end
This is a solution to my previous post:
Change the Sub-Sub-Form UNITS field of the lineitem and refresh so that the record is not dirty. Then run the stored procedure to recalc the new lineitem extended total and update all other related fields in the parent tables and refresh the Parent form.
Code:
*********************************************************
Private Sub UNITS_AfterUpdate()
On Error GoTo UNITS_AfterUpdate_Err
If [UNITS] = [UNITS].OldValue Then
Exit Sub
End If
Refresh
Form_frmJobs.RecalcValuesViaStoredProcedure
Me.Parent.Parent.Refresh
UNITS_AfterUpdate_Err_Exit:
Exit Sub
UNITS_AfterUpdate_Err:
MsgBox Error$
Resume UNITS_AfterUpdate_Err_Exit
End Sub
*********************************************************
Public Sub RecalcValuesViaStoredProcedure()
Dim cmdStr As String
Dim Pid As Long
Pid = [PROJID]
cmdStr = "sp_updatelijobprojvalues(" & Pid & ")"
RunMySQLStoredProcedure (cmdStr)
End Sub
Public Sub RunMySQLStoredProcedure(cmdStr As String)
Dim cmd As ADODB.Command
Dim Pid As Long
Dim strConn, strServer, strDrive As String
'Dim t1, t2 As Date
't1 = Now()
strServer = TempVars![MySQLServer]
strDrive = Nz(TempVars![AccessDrive], "H")
strConn = "ODBC;MySQL ODBC 5.1 Driver;UID=hdbmysqluser;PORT=3306;DATABASE=hdb;PASSWORD=xxxxxxx;SERVER=" & strServer & ";FILEDSN=" & strDrive & ":\AccessHdb\HDB\HDB.dsn;"
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = strConn
.CommandText = "CALL " & cmdStr
.CommandType = adCmdText
.Execute
End With
't2 = Now()
'MsgBox ("Done, " & CStr(Minute(t2 - t1) * 60 + Second(t2 - t1)) & " seconds")
Set cmd = Nothing
End Sub
*********************************************************
DELIMITER $$
CREATE DEFINER=`hdbmysqluser`@`%` PROCEDURE `sp_updatelijobprojvalues`(pid LONG)
BEGIN
UPDATE lineitems RIGHT JOIN (SELECT lineitems.ID, jobs.JOBNO, lineitems.EXTTOTAL, PRICE*UNITS*If(USEPRORATE,PRORATEP/100,1) AS NEWEXTTOTAL, jobs.PROJID
FROM (lineitems INNER JOIN jobs ON lineitems.JOBNO = jobs.JOBNO) INNER JOIN biditems ON lineitems.ITEMID = biditems.ITEMID
WHERE (((jobs.PROJID)=pid))) AS prep ON lineitems.ID = prep.Id
SET lineitems.exttotal = newexttotal;
UPDATE jobs RIGHT JOIN
(SELECT lineitems.JOBNO, Sum(lineitems.EXTTOTAL) AS SumOfEXTTOTAL FROM lineitems
WHERE lineitems.JOBNO IN (SELECT jobs.JOBNO FROM jobs WHERE jobs.PROJID=pid)
GROUP BY lineitems.JOBNO) as temp
ON jobs.JOBNO = temp.JOBNO
SET jobs.VALUE = temp.SumOfEXTTOTAL;
UPDATE jobs
SET jobs.EARNED = jobs.VALUE * jobs.PCENTDONE/100
WHERE jobs.PROJID = pid;
UPDATE projects RIGHT JOIN
(SELECT jobs.PROJID, SUM(jobs.value) AS SumOfJobValues FROM jobs
WHERE jobs.PROJID = pid) as temp
ON projects.PROJID = temp.PROJID
SET projects.VALUE = SumOfJobValues;
UPDATE projects RIGHT JOIN
(SELECT jobs.PROJID, SUM(jobs.value) AS SumOfJobValues, SUM(jobs.EARNED) AS SumOfJobEarned FROM jobs
WHERE jobs.PROJID = pid) as temp
ON projects.PROJID = temp.PROJID
SET projects.PERCENT = SumOfJobEarned*100.00/SumOfJobValues;
UPDATE projects
SET projects.WIP = projects.PERCENT*projects.VALUE/100.
WHERE projects.PROJID = pid;
END
Since I'm still porting an old Paradox desktop database to Access2010/MySQL5.5, I haven't done any multi-user testing yet.