VBA Autonumber with a Prefix (1 Viewer)

[mesh1o]

Hi all,

thank you for taking time out to read this hope you can help

I have a module which generates an autonumber based on a table content (Tbl:Numbervalues) and a prefix (prefix held in Tbl: TextValues)

the module itself works fine and after every getprefix & getnextnumber it updates the number within numbervalues table to the next unique figure.

However what i want it to do is within my database i want to loop through all the records and update a column based on the getprefix() & getnextnumber()

so at the moment lets say column A has values of SHA000001 (in all 100 records) i want it to loop through those 100 records and update to be SHA(from the prefix table) and 000001 all the way to 000100.

Thank you ever so much for taking time out to look at my query
Hope that makes sense and here is my module code

Public Function GetPrefix() As String
On Error GoTo GetPrefix_ErrorHandler
 
  GetPrefix = DLookup("Value", "TextValues", "Description = ""InvoicePrefix""")
  If Len(GetPrefix) <> 2 Then
    msgbox "The Invoice Prefix Value in the Text Values Table is not 2 characters long!", vbCritical, "Critical Warning"
  End If
 
Exit_GetPrefix:
  Exit Function
GetPrefix_ErrorHandler:
  Select Case Err
    Case Else:  msgbox "ERROR " & Err & ": " & Error
  End Select
  Resume Exit_GetPrefix
End Function
 
Public Function GetNextNumber() As String
On Error GoTo GetNextNumber_ErrorHandler
  Dim MyWorkspace As Workspace
  Dim Mydb As Database
  Dim InvoiceNumbers As Recordset
  Set Mydb = CurrentDb
  Set MyWorkspace = DBEngine.Workspaces(0)
  Set InvoiceNumbers = Mydb.OpenRecordset("SELECT DISTINCTROW Description, Value FROM NumberValues WHERE Description=""CurrentNumber""", dbOpenDynaset)
  If InvoiceNumbers.EOF Then
    msgbox "Their is no Invoice Prefix Value in the Number Values Table!", vbCritical, "Critical Warning"
  Else
 
    MyWorkspace.BeginTrans
    GetNextNumber = zeros(5 - Len(CStr(InvoiceNumbers!Value))) & CStr(InvoiceNumbers!Value)
    InvoiceNumbers.Edit
    InvoiceNumbers!Value = InvoiceNumbers!Value + 1
    InvoiceNumbers.UPDATE
    MyWorkspace.CommitTrans
 
  End If
Exit_GetNextNumber:
  InvoiceNumbers.Close
  Exit Function
 
GetNextNumber_ErrorHandler:
  MyWorkspace.Rollback
  Select Case Err
    Case Else:  msgbox "Error number " & Err.Number & ": " & Err.Description
  End Select
  Resume Exit_GetNextNumber
End Function

 

[Rx_]

Won't have time to provide a completed coding solution. Here are the steps. You might be able to do some of them and then ask specific questions about the others.

Create a function with an argumant MyTableName return a boolean ' It returns a pass/fail

Add On Error code to trap an error. On error the function returns a False
Set function name = False ' Function fails until code runs to end and passes.

With table name, create a recordset object. Use MyTableName to open the recordset
Create a local variable RecordCountToRenumber
With recordset object use RecordCount to count the number of records in the table.
(note, you may need to use the Recordset movelast the movefirst to get an accurate recordcount).

Set up a For Next loop starting at 1 to RecordCountToRenumber
Add a variable MyCurrentForNextNumber - this gets incremented at the end of the loop below e.g MyCurrentForNextNumber = MyCurrentForNextNumber +1

With the First record, read the text value into a variable MyInvoice (e.g. SHA000001 )

Now the formula:
MyInvoicePre = left(MyInvoice, 3)
MyInvoicePost= Right(Myinvoice, (len(MyInvoice) -3)
MyInvoiceLen =len(MyInvoice) -3
You have the invoice first 3 letters to reassemble at the end of the formula
The Post number will get substuted with MyCurrentForNextNumber
The MyCurrentForNextNumber needs to be changed to text and padded with MyInvoiceLen total characters.
MyNewInvoiceNumber = MyInvoicePre + MyCurrentForNextNumber
Then use the Recordset object to replace the field value by write the new value to the field.

Update the counter (MyCurrentForNextNumber ), Use the Loop in the For Next statement

After the loop structure: Set Function name = True ' you passed
Exit Funciton
Error trap
debug.print "Error was" & err.description
' Function is still equal to false for return
Exit Function

 

[mesh1o]

Hi

Thanks for the post - however i'm still struggling probably mainly due to not fully bein able to write code but able to understand. Is there a link somewhere which i can kinda follow to get the points mentioned above implemented?

Thank you

 

[gemma-the-husky]

seriously - the right way to do this is to have two separate fields - one for the prefix, and one for the value

but if you iterate a recordset, basedno a query then simply

startserial=0
while not myrst.eof
    startserial = startserial+1
    rst!serial = startserial
    rst.movenext
wend

if you have to string slice to separate the prefix and the number, it's more complexe, but the same idea

 

[mesh1o]

Hi

I didn't actually think of having a separate field with default value of SHI so i'll do that.. with regards to autonumber are you suggesting that the script provided will be enough to get what I want to work? or is there more to it? (Sorry I am really rubbish at this code stuff ! but going on a course soon so hopefully will get better)

Thank you in advance and thanks for helping

 

[gemma-the-husky]

if I understood what you wanted, correctly, I think the code example will manage the process of iterating a record set. You need more code to manage the record set. take a copy of anything before you try it, in case.