1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
|
Private Function getAnomalyID() As String
Dim vsQuery As String
Dim vrstRecord As DAO.Recordset
vsQuery = "SELECT ANOMALY_ID FROM ANOMALIES WHERE ANOMALY_REF LIKE '" & Format(Date, "yy") & "-*';"
Set vrstRecord = CurrentDb.OpenRecordset(vsQuery, dbOpenSnapshot)
If vrstRecord.RecordCount > 0 Then
vsQuery = "SELECT Value FROM tbl_Values WHERE Field='LastAnomalyRef';"
Set vrstRecord = CurrentDb.OpenRecordset(vsQuery, dbOpenSnapshot)
DoCmd.RunSQL "UPDATE tbl_Values SET tbl_Values.Value='" & vrstRecord(0) + 1 & "' WHERE Field='LastAnomalyRef';"
getAnomalyID = CStr(Format(Date, "yy") & "-" & Format(vrstRecord(0) + 1, "0000"))
Else
DoCmd.RunSQL "UPDATE tbl_Values SET tbl_Values.Value='1' WHERE Field='LastAnomalyRef';"
getAnomalyID = CStr(Format(Date, "yy") & "-" & Format(1, "0000"))
End If
End Function |
Partager