Dano
12-08-2015, 07:13 AM
Hi,
Please help can I run this code from a macro on exit from a field. if not show me how to write it up.
Public Function CalcDateTerminaison(ProduitID As Long) As Date
Dim rs As Recordset
Dim sqlString As String
Dim tempDate, DateExp As Date
Dim DateOK As Boolean
Dim count As Integer
DateOK = False
sqlString = "SELECT IIf(tbl_Commande.DateSignature<Max(tbl_Tache.DateFinReel),Max(tbl_Tache.DateFinReel),DateValue(tbl_Command e.DateSignature))+56 AS DateTerminaisonCalc, tbl_Commande.DateExpedition " & _
"FROM (tbl_Commande_Produit LEFT JOIN tbl_Tache ON tbl_Commande_Produit.ID_CommandeProduit = tbl_Tache.ID_CommandeProduit) INNER JOIN tbl_Commande ON tbl_Commande_Produit.CommandeID = tbl_Commande.ID_Commande " & _
"WHERE (((tbl_Commande_Produit.ProduitID) = " & ProduitID & ") AND ((tbl_Tache.TypeTacheID) Is Null Or (tbl_Tache.TypeTacheID)=19) AND ((tbl_Tache.MachineID) Is Null Or (tbl_Tache.MachineID)=34)) " & _
"GROUP BY tbl_Commande.DateSignature, tbl_Commande.DateExpedition;"
Set rs = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)
If Nz(rs.Fields("[DateTerminaisonCalc]"), 0) <> 0 Then
tempDate = rs.Fields("[DateTerminaisonCalc]")
Else
tempDate = 0
DateOK = True
End If
DateExp = Nz(rs.Fields("[DateExpedition]"), 0)
rs.Close
Do While DateOK = False
sqlString = "SELECT tbl_DateConge.DateConge " & _
"FROM tbl_DateConge " & _
"WHERE (((tbl_DateConge.DateConge)=#" & tempDate & "#));"
Set rs = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)
count = rs.RecordCount
rs.Close
If Weekday(tempDate) = 1 Or Weekday(tempDate) = 7 Then
tempDate = tempDate + 1
ElseIf count > 0 Then
tempDate = tempDate + 1
Else
DateOK = True
End If
Loop
If tempDate > DateExp Then
CalcDateTerminaison = DateExp
Else
CalcDateTerminaison = tempDate
End If
End Function
Please help can I run this code from a macro on exit from a field. if not show me how to write it up.
Public Function CalcDateTerminaison(ProduitID As Long) As Date
Dim rs As Recordset
Dim sqlString As String
Dim tempDate, DateExp As Date
Dim DateOK As Boolean
Dim count As Integer
DateOK = False
sqlString = "SELECT IIf(tbl_Commande.DateSignature<Max(tbl_Tache.DateFinReel),Max(tbl_Tache.DateFinReel),DateValue(tbl_Command e.DateSignature))+56 AS DateTerminaisonCalc, tbl_Commande.DateExpedition " & _
"FROM (tbl_Commande_Produit LEFT JOIN tbl_Tache ON tbl_Commande_Produit.ID_CommandeProduit = tbl_Tache.ID_CommandeProduit) INNER JOIN tbl_Commande ON tbl_Commande_Produit.CommandeID = tbl_Commande.ID_Commande " & _
"WHERE (((tbl_Commande_Produit.ProduitID) = " & ProduitID & ") AND ((tbl_Tache.TypeTacheID) Is Null Or (tbl_Tache.TypeTacheID)=19) AND ((tbl_Tache.MachineID) Is Null Or (tbl_Tache.MachineID)=34)) " & _
"GROUP BY tbl_Commande.DateSignature, tbl_Commande.DateExpedition;"
Set rs = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)
If Nz(rs.Fields("[DateTerminaisonCalc]"), 0) <> 0 Then
tempDate = rs.Fields("[DateTerminaisonCalc]")
Else
tempDate = 0
DateOK = True
End If
DateExp = Nz(rs.Fields("[DateExpedition]"), 0)
rs.Close
Do While DateOK = False
sqlString = "SELECT tbl_DateConge.DateConge " & _
"FROM tbl_DateConge " & _
"WHERE (((tbl_DateConge.DateConge)=#" & tempDate & "#));"
Set rs = CurrentDb.OpenRecordset(sqlString, dbOpenDynaset, dbSeeChanges)
count = rs.RecordCount
rs.Close
If Weekday(tempDate) = 1 Or Weekday(tempDate) = 7 Then
tempDate = tempDate + 1
ElseIf count > 0 Then
tempDate = tempDate + 1
Else
DateOK = True
End If
Loop
If tempDate > DateExp Then
CalcDateTerminaison = DateExp
Else
CalcDateTerminaison = tempDate
End If
End Function