AutoNumber Question

[bepaling]

I have tblWorkDirective w/ the field WorkDirectiveID as a PK Autonumber. We (my boss & I) want to have our own autogenerating number that increases with every new record, AND has several components.

We'd like it to appear as: E05-XXXX, where "E" is always that letter, "05" is the last two digits of the current year and XXXX is the autogenerating number.

Right now, in tblWorkDirective I've got the field WorkDirectiveID Format as "E05-"0000. That SHOWS UP properly, but that won't allow for the Year to reset automatically at the end of the Year, or for the number's to recycle.

I've checked the FAQs & searched quite a few other posts & sites, but I'm lacking the proper direction. Any thoughts/ideas?

Cheers.

 

[ZmrAbdulla]

AUTONUMBER FAQs here
[ul]
[li] Use of Autonumber faq702-5106 [/li]
[li] How can I auto-prefix my autonumber field? faq181-1725 [/li]
[li] Create a Autonumber with a Suffix faq181-1179[/li]
[li] How to autonumber date values in a control. faq181-1129 [/li]
[li] What can I use besides AutoNumber faq181-1023 [/li]
[li] Make an intelligent autonumber field? faq181-159 [/li]
[li] can I make a dynamic autonumber? faq181-158 [/li]
[/ul]


________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[bepaling]

Hey Zameer,

Thanks for the reply; I've checked out those FAQs prior to my first post; since your post, I've revisited them & I've checked through your site - a lot of useful information there, by the way.

Here's what I've come up with so far:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me!WorkDirective = Nz(DMax("[WorkDirective]", "[tblWorkDirective]"), 0) + 1
End Sub

I've tooled around with this a couple different ways & all I can get it to show is the AutoNumber. I need to be able to add the features described above ("E" & the last two digits of the year).

Any further help would be appreciated,

Cheers.

 

[ZmrAbdulla]

try the following

Function CreateAutoNumber()
    Dim MyYear As String
    Dim MyNumber As Variant
    Dim MyAutoNumber As String
    
    MyYear = Right(Date, 2)
    MyNumber = Nz(DMax("[WorkDirective]", "[tblWorkDirective]"), 0) + 1
    [b][COLOR=blue]MyAutoNumber = "E" & MyYear & MyNumber[/color][/b]
    Me!WorkDirective = MyAutoNumber
End Function
[COLOR=green]'====================================[/color]
Private Sub Form_BeforeInsert(Cancel As Integer)
    CreateAutoNumber
End Sub

You can also use a this format.

[b][COLOR=blue]MyAutoNumber = "E" & "-" & MyYear & "-" & MyNumber[/color][/b]
 '(E-05-22)

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[ZmrAbdulla]

A little change with the code.

Function CreateAutoNumber()
    Dim MyYear As String
    Dim MyNumber As Variant
    Dim MyAutoNumber As String
    
    MyYear = Right(Date, 2)
    MyNumber = Nz([b]DCount[/b]("WorkDirective", "tblWorkDirective"), 1) + 1

    MyAutoNumber = "E" & MyYear & MyNumber
    Me!WorkDirective = MyAutoNumber
End Function

________________________________________
Zameer Abdulla
Visit Me
A child may not be able to lift too much.
But it can certainly hold a marriage together

 

[bepaling]

Awesome Zameer, thanks so much! I'll try them out & let you know.

What's the difference between the DMax & the DCount?

 

[bepaling]

Hey Zameer,

I used the following Code:

Function CreateAutoNumber()
    Dim MyYear As String
    Dim MyNumber As Variant
    Dim MyAutoNumber As String
    
    [COLOR=red]MyYear = Right(Date, 2)[/color]
    MyNumber = Nz(DCount("[WorkDirectiveID]", "[tblWorkDirective]"), 1) + 1
    MyAutoNumber = "E" & MyYear & "-" & MyNumber
    Me!WorkDirective = MyAutoNumber
    
End Function
______________________________________________________
Private Sub Form_BeforeInsert(Cancel As Integer)

    CreateAutoNumber
    
End Sub

When I run it, it comes back with Error 94: Invalid use of Null and takes me to the line selected in Red. Any thoughts/ideas?

Cheers.

 

[PHV]

Have you by chance a control named Date ?
You may try this:
MyYear = Right(VBA.Date, 2)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[bepaling]

Hey PHV,

I checked and I did have a TextBox named Date; I've renamed it to txtDate.

Then, I changed my Code to include the VBA.Date... works like a charm! Thanks PHV!

Can you explain what difference it makes with "VBA.Date" as opposed to "Date?"

Cheers!