Creating an incrementing invoice number with date?

[VBjavaFriend]

I am having problems creating an invoice number the way my boss wants it. The first part of the invoice number should be the current year. The second part should increment by one. The way she wants it to look is: 2002 - 0100 I have no idea how to make it do this. By mistake I have already set it up as a textbox on a form as text. Now I can't change it to autonumber even if I wanted to because it is a primary key and has a relationship with another table. Every time you move from one invoice to the next the number needs to change (which it doesn't do now.) Anybody have any suggestions?

 

[Terpsfan]

First can you delete what's in the table? If it's just development data, hopefully you can. Or just redesign the another table with all the fields. This is how you'd get your desire result - 2002-0001,2002-0002, etc

In your format property under increment type this "2002-"0000

 

[SBendBuckeye]

See this thread which was similar. If you have more questions, please feel free to post them.

thread702-398859 Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[VBjavaFriend]

I tried the "2002 - "0000 suggestion. All the form would display is "AutoNumber" (Did I do something wrong?). It didn't put a value in the field. I looked at thread 702-398859. That might actually be an answer to my problem, but I have no idea where the code goes. I know how to find form Current() and enter a subroutine, but I have never used a function (or a macro). I don't know where to put it. I feel really stupid, but if you read this could you please walk me through the process? Thanks for the help.

 

[Terpsfan]

The format property in your table for your id should work, it worked for me with no problem. I think it's better than writing several lines of code that do the same thing. Are you using Long Integer as your field type and Increment as New Values? Plus it's going to read "auto number" in the field until you enter a new value into one of the other fields on the form. I hope this helps.

 

[KenReay]

Hi

or see this thread thread702-401774 again virtually the same question Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[SBendBuckeye]

With the form open in design view, click the view menu and select properties. Click the events tab and click the On Current event. Select or type [Event Procedure] on that line. It will take you into the VBA code editor and supply you with a sub as follows:

Private Sub Form_Current()

End Sub

Put the function down at the bottom of the code and invode it by naming it above. Here is an example:

Down below

Private Function BlahBlahBlan

End Function

In Form_Current

If Me.NewRecord Then 'you only want to do this for new
myfield = BlahBlahBlah(comma delimeted list of parms)
End If

Hope this helps and Good Luck! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!