VBA code

[Bob sg]

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

 

[ntc]

you are saying in different records in a table of the same field/column? i.e.
invoice number
123
123
123

you want to change to:
123a
123b
123c

??

If so that is a pretty tricky sql exercise.... also now the integrity of
the invoice number is lost, which may be cross referencing to another
table....you might be better putting the a,b,c, designation in a separate
field and joining them for display purposes....and what happens if you reach
beyond letter z ?? - - also I trust it doesn't matter which record gets a vs
b vs c ??

In any case it would take some time to work out the correct sql
statement...will be very interested in other input on this one.....

 

[Beetle]

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.

 

[Bob sg]

Couple more questions if I could ask. I understand about adding a new table
for the letters. Would this work if my query had the following invoice
numbers:

6552333
6552333
6552334
6552334
6552334
6552555
6552555
6552555
6552555

Would I be able to use this code on specific queries (some queries I don't
want to put a letter after the invoice number). And I'm not sure where to put
this code that you gave me below. Let me know.
Thanks

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

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

 

[Bob sg]

Couple more questions if I could ask. I understand about adding a new table
for the letters. Would this work if my query had the following invoice
numbers:

6552333
6552333
6552334
6552334
6552334
6552555
6552555
6552555
6552555

Would I be able to use this code on specific queries (some queries I don't
want to put a letter after the invoice number). And I'm not sure where to put
this code that you gave me below. Let me know.
Thanks

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

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

 

[Bob sg]

Couple more questions if I could ask. I understand about adding a new table
for the letters. Would this work if my query had the following invoice
numbers:

6552333
6552333
6552334
6552334
6552334
6552555
6552555
6552555
6552555

Would I be able to use this code on specific queries (some queries I don't
want to put a letter after the invoice number). And I'm not sure where to put
this code that you gave me below. Let me know.
Thanks

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

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