Sequential Numbering with Alphanumeric

  • Thread starter Thread starter 4charity
  • Start date Start date
4

4charity

I am fairly new to programming, and have been struggling through this code.
It seems like it should be pretty easy.

I have a field called [SystemID] in my tblInvoiceDetail Table. If the field
is not populated from a feed of data, it is designated as a Vendor Invoice
(ion the [TypeofInvoice] field, and I need to automatically assign an ID
number. The number should start with the letter "V", to indicate that it is a
manually entered Vendor number.

I have been attempting to do this as a BeforeUpdate on the Form, starting
with:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.TypeOfInvoice = "Vendor Invoice" Then

????????????????????????????????????????

End If
End Sub

I have tried various things in the ???????? space. Can anyone help me out?
 
I have gotten some code in there that works, but I am not sure what it all
means:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.TypeOfInvoice = "Vendor Invoice" Then
Dim varNextNum As Variant
Dim strLogPrefix As String

strLogPrefix = "V"
varNextNum = DMax("[SystemID]", "tblInvoiceDetail", _
Left([SystemID], 9))
varNextNum = IIf(IsNull(varNextNum), 1, Right(varNextNum, 3) + 1)
Me![SystemID] = strLogPrefix & Format(varNextNum, "000")


End If

End Sub

Is any of this extraneous? What are the "9" and "3" ?

Thanks!
 
One last question:
I have been playing with this to try and get it to make a longer SystemID.
Which variables do I change in the code to get it to be, say, digits long?
If I change the 000 to 00000, I get seven digits, but it does not advance by
one.
If I also change the 3 to a 5, I get a type mismatch run error.
Thanks.

4charity said:
I have gotten some code in there that works, but I am not sure what it all
means:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.TypeOfInvoice = "Vendor Invoice" Then
Dim varNextNum As Variant
Dim strLogPrefix As String

strLogPrefix = "V"
varNextNum = DMax("[SystemID]", "tblInvoiceDetail", _
Left([SystemID], 9))
varNextNum = IIf(IsNull(varNextNum), 1, Right(varNextNum, 3) + 1)
Me![SystemID] = strLogPrefix & Format(varNextNum, "000")


End If

End Sub

Is any of this extraneous? What are the "9" and "3" ?

Thanks!



4charity said:
I am fairly new to programming, and have been struggling through this code.
It seems like it should be pretty easy.

I have a field called [SystemID] in my tblInvoiceDetail Table. If the field
is not populated from a feed of data, it is designated as a Vendor Invoice
(ion the [TypeofInvoice] field, and I need to automatically assign an ID
number. The number should start with the letter "V", to indicate that it is a
manually entered Vendor number.

I have been attempting to do this as a BeforeUpdate on the Form, starting
with:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.TypeOfInvoice = "Vendor Invoice" Then

????????????????????????????????????????

End If
End Sub

I have tried various things in the ???????? space. Can anyone help me out?
Click to expand...
Click to expand...
 
Something is not stable about the code, I now get a runtime error 2001.

varNextNum = DMax("[SystemID]", "tblInvoiceDetail", _
Left([SystemID], 9))

is highlighted in the code.


4charity said:
One last question:
I have been playing with this to try and get it to make a longer SystemID.
Which variables do I change in the code to get it to be, say, digits long?
If I change the 000 to 00000, I get seven digits, but it does not advance by
one.
If I also change the 3 to a 5, I get a type mismatch run error.
Thanks.

4charity said:
I have gotten some code in there that works, but I am not sure what it all
means:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.TypeOfInvoice = "Vendor Invoice" Then
Dim varNextNum As Variant
Dim strLogPrefix As String

strLogPrefix = "V"
varNextNum = DMax("[SystemID]", "tblInvoiceDetail", _
Left([SystemID], 9))
varNextNum = IIf(IsNull(varNextNum), 1, Right(varNextNum, 3) + 1)
Me![SystemID] = strLogPrefix & Format(varNextNum, "000")


End If

End Sub

Is any of this extraneous? What are the "9" and "3" ?

Thanks!



4charity said:
I am fairly new to programming, and have been struggling through this code.
It seems like it should be pretty easy.

I have a field called [SystemID] in my tblInvoiceDetail Table. If the field
is not populated from a feed of data, it is designated as a Vendor Invoice
(ion the [TypeofInvoice] field, and I need to automatically assign an ID
number. The number should start with the letter "V", to indicate that it is a
manually entered Vendor number.

I have been attempting to do this as a BeforeUpdate on the Form, starting
with:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.TypeOfInvoice = "Vendor Invoice" Then

????????????????????????????????????????

End If
End Sub

I have tried various things in the ???????? space. Can anyone help me out?
Click to expand...
Click to expand...
Click to expand...
 
Hi,
try to make it:
varNextNum = DMax("[SystemID]", "tblInvoiceDetail")
and it should work

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


4charity said:
Something is not stable about the code, I now get a runtime error 2001.

varNextNum = DMax("[SystemID]", "tblInvoiceDetail", _
Left([SystemID], 9))

is highlighted in the code.


4charity said:
One last question:
I have been playing with this to try and get it to make a longer
SystemID.
Which variables do I change in the code to get it to be, say, digits
long?
If I change the 000 to 00000, I get seven digits, but it does not advance
by
one.
If I also change the 3 to a 5, I get a type mismatch run error.
Thanks.

4charity said:
I have gotten some code in there that works, but I am not sure what it
all
means:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.TypeOfInvoice = "Vendor Invoice" Then
Dim varNextNum As Variant
Dim strLogPrefix As String

strLogPrefix = "V"
varNextNum = DMax("[SystemID]", "tblInvoiceDetail", _
Left([SystemID], 9))
varNextNum = IIf(IsNull(varNextNum), 1, Right(varNextNum, 3) + 1)
Me![SystemID] = strLogPrefix & Format(varNextNum, "000")


End If

End Sub

Is any of this extraneous? What are the "9" and "3" ?

Thanks!



:

I am fairly new to programming, and have been struggling through this
code.
It seems like it should be pretty easy.

I have a field called [SystemID] in my tblInvoiceDetail Table. If the
field
is not populated from a feed of data, it is designated as a Vendor
Invoice
(ion the [TypeofInvoice] field, and I need to automatically assign
an ID
number. The number should start with the letter "V", to indicate that
it is a
manually entered Vendor number.

I have been attempting to do this as a BeforeUpdate on the Form,
starting
with:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.TypeOfInvoice = "Vendor Invoice" Then

????????????????????????????????????????

End If
End Sub

I have tried various things in the ???????? space. Can anyone help me
out?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top