FWIW - Here is one option. If you're going to try this method, do
so on a backup copy of your app.
Create a new table to store the letters that will be added to the
invoice numbers. This table would have a structure like;
tblInvoiceExtensions
**************
ExtID (Primary Key - use Number data type, not Autonumber)
ExtLetter
The data in the table would look like;
ExtID ExtLetter
1 A
2 B
3 C
4 D
5 E
etc.
Next, in the Before Update event of your form put code like the
following:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "Select * From tblInvoices Where Mid([InvoiceNumber]," _
& "1, Len(" & Me.txtInvoiceNumber & "))=""" _
& Me.txtInvoiceNumber & """;"
Set rs = CurrentDb.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
rs.MoveLast
Me.InvoiceNumber = Me.InvoiceNumber & DLookup _
("ExtLetter", "tblInvoiceExtensions", "ExtID=" _
& rs.RecordCount)
End If
rs.Close
Set rs = Nothing
End Sub
Basically, this procedure would count the number of existing occurrences
of the invoice number that has just been entered in the text box on
your form (not including the letter on the end). It then uses that record
count to look up the appropriate letter from the lookup table. So, for
example, if your table already has invoices 123 and 123A then the record
count will be 2. It will then retrieve the ExtLetter from the lookup table
which has an ID value of 2 (which is "B") and add that to the end of the
newly entered invoice number before it is saved to the table.
This is tested, but not thoroughly, so it may have some flaws. Also, you
should add your own error handling.
--
_________
Sean Bailey
Bob sg said:
I need a VBA code to put an A, B, C, etc, after the invoice
number for each of the invoice numbers that are the same number. The format
for the invoice number under the table is text. Hope you can
help me out on this.
Thanks
