Option Compare Database
Dim db As DAO.Database
Dim rs As DAO.Recordset, rs2 As DAO.Recordset
Dim strSQL As String, strSQL2 As String
Global seqNumber As Long
Global lastcall As Date
Function UpdateData()
If gcfHandleErrors Then On Error GoTo Err_regulate_function
Dim mo As String, eu As String
Dim Rev As LongPtr, ct As LongPtr
Set db = CurrentDb()
'delete temp table
strSQL = "DELETE tblReg_Models_up.* " _
& "FROM tblReg_Models_up"
db.Execute strSQL
strSQL = "DELETE tblReg_Models_temp.* " _
& "FROM tblReg_Models_temp"
db.Execute strSQL
'insert new models
strSQL = "INSERT INTO tblReg_Models ( model, begdate ) " _
& "SELECT bm.model, Date() AS dt " _
& "FROM (SELECT DISTINCT Left([eu_itemno],4) AS model " _
& "FROM [" & isc & "].Dbo.custom_pymac_bom as cpb " _
& "WHERE (((Left([eu_itemno],4)) Not Like ""J*"" And (Left([eu_itemno],4)) Not Like ""F*"") AND ((cpb.start_date)>CStr(Format(Date()-15,""yyyymmdd""))))) AS bm " _
& "LEFT JOIN tblReg_Models ON bm.model = tblReg_Models.model " _
& "WHERE (((tblReg_Models.model) Is Null) AND ((Right([bm]![model],1)) Not Like ""x""))"
db.Execute strSQL
'update the model group on the new models
strSQL = "INSERT INTO tblReg_Models_up ( 3m, mdl_grp_id ) " _
& "SELECT DISTINCT Left([model],3) AS 3m, tblReg_Models.mdl_grp_id " _
& "FROM tblReg_Models " _
& "WHERE (((tblReg_Models.mdl_grp_id) Is Not Null))"
db.Execute strSQL
strSQL = "UPDATE (SELECT tblReg_Models.model, tblReg_Models.mdl_grp_id, Left([model],3) AS 3m " _
& "FROM tblReg_Models " _
& "WHERE (((tblReg_Models.mdl_grp_id) Is Null))) AS rm INNER JOIN tblReg_Models_up ON rm.[3m] = tblReg_Models_up.[3m] SET rm.mdl_grp_id = [tblReg_Models_up]![mdl_grp_id]"
db.Execute strSQL
strSQL = "DELETE tblReg_Models_up.* " _
& "FROM tblReg_Models_up"
db.Execute strSQL
'if new models no model grp open model form
strSQL = "SELECT tblReg_Models.mdl_grp_id, tblReg_Models.model, tblReg_Models.begdate, tblReg_Models.obsolete, tblModel_Grp.hs_area_id " _
& "FROM tblModel_Grp RIGHT JOIN tblReg_Models ON tblModel_Grp.mdl_grp_id = tblReg_Models.mdl_grp_id " _
& "WHERE (((tblReg_Models.mdl_grp_id) Is Null))"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
DoCmd.OpenForm "frmModels_no_grp"
Exit Function
End If
'insert models from bom less than 16 days old into temp table
strSQL = "INSERT INTO tblReg_Models_temp ( model, eu_itemno, rev ) " _
& "SELECT p.model, p.eu_itemno, IIf([r] Is Null,0,[r]) AS rev FROM (SELECT DISTINCT Left([eu_itemno],4) AS model, First(cpb.eu_itemno) AS eu_itemno " _
& "FROM [" & isc & "].dbo.custom_pymac_bom as cpb " _
& "WHERE(((cpb.start_date) > CStr(Format(Date() - 15, ""yyyymmdd""))) And ((Len(RTrim([eu_itemno]))) = 10)) " _
& "GROUP BY Left([eu_itemno],4) " _
& "HAVING (((Left([eu_itemno],4)) Not Like ""J*"" And (Left([eu_itemno],4)) Not Like ""F*""))) AS p LEFT JOIN (SELECT DISTINCT tblReg_Models_revs.model, Max(tblReg_Models_revs.Rev) AS r FROM tblReg_Models_revs GROUP BY tblReg_Models_revs.model) AS mr ON p.model = mr.model"
db.Execute strSQL
'set up tblpymac_local
strSQL = "DELETE tblpymac_local.* " _
& "FROM tblpymac_local"
db.Execute strSQL
strSQL = "INSERT INTO tblpymac_local ( eu_itemno, itemno, nak, pn, start_date, not_use ) " _
& "SELECT distinct qryPymac_bom.eu_itemno, qryPymac_bom.itemno, qryPymac_bom.nak, qryPymac_bom.pn, qryPymac_bom.start_date, tblReg_pn.not_use " _
& "FROM ((tblReg_nakago INNER JOIN qryPymac_bom ON tblReg_nakago.nak = qryPymac_bom.nak) INNER JOIN tblReg_Models_temp ON qryPymac_bom.eu_itemno = tblReg_Models_temp.eu_itemno) LEFT JOIN tblReg_pn ON qryPymac_bom.pn = tblReg_pn.pn " _
& "WHERE (((qryPymac_bom.start_date)>CStr(Format(Date()-15,""yyyymmdd""))))"
db.Execute strSQL
'add new parts for child parts
strSQL = "INSERT INTO tblReg_child_pn ( py_pn) SELECT distinct np.pn " _
& "FROM (SELECT qryPymac_bom.pn FROM (tblReg_nakago INNER JOIN qryPymac_bom ON tblReg_nakago.nak = qryPymac_bom.nak) INNER JOIN tblReg_Models_temp ON qryPymac_bom.eu_itemno = tblReg_Models_temp.eu_itemno " _
& "WHERE (((tblReg_nakago.child)=1))) AS np LEFT JOIN tblReg_child_pn ON np.pn = tblReg_child_pn.py_pn " _
& "WHERE (((tblReg_child_pn.py_pn) Is Null))"
db.Execute strSQL
'cycle models to see if need new reg or rev up
strSQL = "SELECT tblReg_Models_temp.model, tblReg_Models_temp.eu_itemno, tblReg_Models_temp.rev " _
& "FROM tblReg_Models_temp"
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
rs.MoveFirst
While Not rs.EOF
mo = rs.Fields("model")
eu = rs.Fields("eu_itemno")
Rev = rs.Fields("rev")
'insert new parts from bom into tblReg_pn
strSQL2 = "INSERT INTO tblReg_pn ( pn, nak_id ) " _
& "SELECT np.pn, np.nak_id " _
& "FROM (SELECT qryPymac_bom.pn, tblReg_nakago.nak_id " _
& "FROM (tblReg_nakago INNER JOIN qryPymac_bom ON tblReg_nakago.nak = qryPymac_bom.nak) INNER JOIN tblReg_Models_temp ON qryPymac_bom.eu_itemno = tblReg_Models_temp.eu_itemno " _
& "WHERE (((tblReg_Models_temp.model)='" & mo & "') AND ((tblReg_nakago.child) <1))) AS np LEFT JOIN tblReg_pn ON np.pn = tblReg_pn.pn " _
& "WHERE (((tblReg_pn.pn) Is Null))"
db.Execute strSQL2
'if rev does not exist then create 1st rev
If Rev = 0 Then
strSQL2 = "INSERT INTO tblReg_Models_revs ( model, Rev, Date_Issued, Issued_By, reason ) " _
& "values ('" & mo & "', 1, #" & Date & "#, 1, ""New sheet"")"
db.Execute strSQL2
Else
'see if new rev is needed
strSQL2 = "SELECT py.pn " _
& "FROM (SELECT tblpymac_local.pn, tblReg_Models_temp.model FROM (tblpymac_local INNER JOIN tblReg_Models_temp ON tblpymac_local.eu_itemno = tblReg_Models_temp.eu_itemno) INNER JOIN tblReg_nakago ON tblpymac_local.nak = tblReg_nakago.nak WHERE (((tblReg_nakago.child)<1) AND ((tblpymac_local.not_use)=False)) " _
& "union " _
& "SELECT DISTINCT tblReg_pn.pn, tblReg_Models_temp.model FROM ((SELECT tblReg_nakago.nak_id, tblReg_nakago.nak, tblReg_child_pn.py_pn, tblReg_child_pn.pn_id FROM tblReg_nakago INNER JOIN tblReg_child_pn ON tblReg_nakago.nak_id = tblReg_child_pn.nak_id WHERE (((tblReg_nakago.child)<1) AND ((tblReg_child_pn.disreqard)=No))) AS n " _
& "INNER JOIN (tblReg_Models_temp INNER JOIN qryPymac_bom ON tblReg_Models_temp.eu_itemno = qryPymac_bom.eu_itemno) ON n.py_pn = qryPymac_bom.pn) INNER JOIN tblReg_pn ON n.pn_id = tblReg_pn.pn_id) AS py LEFT JOIN (SELECT tblReg_pn.pn, tblReg_Models_temp.model FROM (tblReg_Models_temp INNER JOIN (tblReg_Models_revs INNER JOIN tblReg_masters ON tblReg_Models_revs.rec_mod_id = tblReg_masters.rec_mod_id) " _
& "ON (tblReg_Models_temp.model = tblReg_Models_revs.model) AND (tblReg_Models_temp.rev = tblReg_Models_revs.Rev)) INNER JOIN tblReg_pn ON tblReg_masters.pn_id = tblReg_pn.pn_id WHERE (((tblReg_pn.not_use)=No))) AS old ON (py.pn = old.pn) AND (py.model = old.model) " _
& "WHERE (((py.model)='" & mo & "') AND ((old.pn) Is Null) AND ((old.model) Is Null))"
Set rs2 = db.OpenRecordset(strSQL2)
ct = rs2.RecordCount
strSQL2 = "SELECT old.pn " _
& "FROM (SELECT tblpymac_local.pn, tblReg_Models_temp.model FROM (tblpymac_local INNER JOIN tblReg_Models_temp ON tblpymac_local.eu_itemno = tblReg_Models_temp.eu_itemno) INNER JOIN tblReg_nakago ON tblpymac_local.nak = tblReg_nakago.nak WHERE (((tblReg_nakago.child)<1) AND ((tblpymac_local.not_use)=False)) " _
& "union " _
& "SELECT DISTINCT tblReg_pn.pn, tblReg_Models_temp.model FROM ((SELECT tblReg_nakago.nak_id, tblReg_nakago.nak, tblReg_child_pn.py_pn, tblReg_child_pn.pn_id FROM tblReg_nakago INNER JOIN tblReg_child_pn ON tblReg_nakago.nak_id = tblReg_child_pn.nak_id WHERE (((tblReg_nakago.child)<1) AND ((tblReg_child_pn.disreqard)=No))) AS n " _
& "INNER JOIN (tblReg_Models_temp INNER JOIN qryPymac_bom ON tblReg_Models_temp.eu_itemno = qryPymac_bom.eu_itemno) ON n.py_pn = qryPymac_bom.pn) INNER JOIN tblReg_pn ON n.pn_id = tblReg_pn.pn_id) AS py RIGHT JOIN (SELECT tblReg_pn.pn, tblReg_Models_temp.model FROM (tblReg_Models_temp INNER JOIN (tblReg_Models_revs INNER JOIN tblReg_masters ON tblReg_Models_revs.rec_mod_id = tblReg_masters.rec_mod_id) " _
& "ON (tblReg_Models_temp.model = tblReg_Models_revs.model) AND (tblReg_Models_temp.rev = tblReg_Models_revs.Rev)) INNER JOIN tblReg_pn ON tblReg_masters.pn_id = tblReg_pn.pn_id WHERE (((tblReg_pn.not_use)=No))) AS old ON (py.pn = old.pn) AND (py.model = old.model) " _
& "WHERE (((py.pn) Is Null) AND ((py.model) Is Null) AND ((old.model)='" & mo & "'))"
Set rs2 = db.OpenRecordset(strSQL2)
ct = ct + rs2.RecordCount
'if is needed add new rev and insert parts
If ct > 0 Then
'insert new model into new models rev
strSQL2 = "INSERT INTO tblReg_Models_revs ( model, Rev, Date_Issued, Issued_By, reason ) " _
& "values ('" & mo & "', " & Rev + 1 & " , #" & Date & "#, 1, ""Automated New rev"")"
db.Execute strSQL2
End If
End If
rs.MoveNext
Wend
End If
strSQL = "DELETE tblReg_Models_temp.* " _
& "FROM tblReg_Models_temp"
db.Execute strSQL
strSQL = "DELETE tblpymac_local.* " _
& "FROM tblpymac_local"
db.Execute strSQL
rs.Close
Set rs = Nothing
Set db = Nothing
Exit_regulate_function:
Exit Function
Err_regulate_function:
Call LogError(err.Number, err.Description, "regulate_function()")
Resume Exit_regulate_function
End Function