I have a database that keeps track of revisions by letter, and I want to have it automacally update this rev letter through code. Is there a way to do this?
I something similar with an item number. The first time item is assigned it does not receive a letter (I refer to it as suffix). Every re-issue gets an incremental letter. The function I use checks to see what the highest suffix assigned is and then increments it by one.
I don't know if this will help, I am not a coder by trade, but this has worked for me. Use what you can, if you can.
This function passing the number key of the item it needs to increment.
Public Function GetSuffix(lkup As Long) As String
On Error GoTo GetSuffixERR
Dim ret As String
Dim currentHigh As Integer
Dim db As Database
Dim rs As Recordset
Dim sql As String
Dim qdf As QueryDef
sql = "SELECT Max (f_OurSuffix) AS High_OurSuffix FROM tblAdjustments " & _
"WHERE f_OurRef = " & lkup
Set db = CurrentDb
Set qdf = db.CreateQueryDef("", sql)
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
currentHigh = Asc(rs!High_OurSuffix)
ret = Chr((currentHigh + 1))
GetSuffix = ret
GetSuffix_Exit:
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Exit Function
GetSuffixERR:
'if there is no maximum suffix (this is first resend)
'"currentHigh = Asc(rs!High_OurSuffix)" will cause error because High_OurSuffix
'is null. This set the initial value to 64 (1 less than A)
If Err.Number = 94 Then 'INVALID USE OF NULL
currentHigh = 64
Resume Next
Else
MsgBox "GetSuffix Error: " & str(Err.Number) & " " & Err.Description
Resume GetSuffix_Exit
End If
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.