Sequential Numbering with VBA code

[spike250]

Hi,

I have a text field on my form which I have limited to no duplicates.

I have tried to use an Auto Number format but I would encount problems at the end of the year due to the format of the number

i.e. 2009 = Z,09.00001, Z,09.00002 etc
2010 = Z,10.00001, Z,10.00002 etc

What I am trying to do is the following;

When the users types in a number out of sequence with the previous one a message box pops up advising that the Z number is not in sequence do you wish to proceed? the user would then click no for 2009 but when 2010 comes the user would select yes in the above message box which would change the format to Z,10.''''' etc and the sequence would start again from z,10 onwards.

This would carry on for the following years.

I have been told that it is possible to this using VBA but I am unsure how.

I would appreciate any help on this matter.

Regards

Spike

 

[NigelShaw]

I have a text field on my form which I have limited to no duplicates.

shame this ethos isnt enforced when posting

just kidding!

Hi,

i dont quite follow your explanation. are the numbers always preceeded with "Z"? is the digit set "00002" a number that increases numerically?

if so you could use

Dim strZ As String
Dim intYr as Date
Dim intCurr As Integer
Dim varSQL As Variant
Dim varFullNum As Variant

varSQL = "SELECT tblYourTable.YourID, Max(tblYourTable.YourNumberField)"
varSQL = strSQL & " AS MaxOfYourNumberField"
varSQL = strSQL & " FROM tblYourTable"
strSQL = strSQL & " GROUP BY tblYourTable.YourID;"

DoCmd RunSQL "varSQL"

strZ = "Z"
intYr = Format(Date(), "yy")
intCurr = strSQL + 1

varFullNum = strZ & "," & intYr & "." & intCurr

then use the varFullNum as the number in whatever you are doing but bear in mind that if the variable is being used by other code, the types might need to be the same as Variant = Variant but im not 100%

to try and explain the scenario in a layman term

varSQL is getting the max number from tblYourTable from the field YourNumberField
strZ is set to hold "Z" so it is always used
intYr is formatting the date to give you only the "09" part of a date
intCurr is getting the retrieved number and adding 1 to it.

the code above is not trapped for null values or empty fields so you will need to look into this. i can do it but it would be good practice to understand what is required so ill leave it to you

maybe someone might update the code as im not actually on a pc today but typing away on a 3" phone screen! ill give it a good look later as the format date might not be correct but hey ho.....



regs

nigel

 

[spike250]

Hi Nigel,

Sorry tried to explain it as best as I could but as you have probably guessed I am new to VBA.

The user has to type the number in manually and I am trying to get it so there are no errors with the sequence.

I have put in a code on the after update to prevent duplicate entries.

All the numbers will preceed with a Z.

Hope this helps.

 

[NigelShaw]

Hi Nigel,

Sorry tried to explain it as best as I could but as you have probably guessed I am new to VBA.

The user has to type the number in manually and I am trying to get it so there are no errors with the sequence.

I have put in a code on the after update to prevent duplicate entries.

All the numbers will preceed with a Z.

Hope this helps.

OK,

is the number that is manually entered sequential? im off to the cinema but i'll do a quick demo when i get back.

im guessing the finished number is stored in a table field?


NS

 

[spike250]

Hi Nigel,

Sorry about the delay in replying I have been busy with another project.

The number that is entered manualy is sequential.

Z,09. is fixed for all records added
after the . the numbers get added manually and are increased by 1 every time a new record is created.

The whole z,09.00000 is stored in a table.

Lastly the 09 part will need to change when the year changes?

Look forward to hearing from you.

Regards
Spike

 

[gemma-the-husky]

the answer is not to store the z.09 - or to store the z.09 in another fiield

just worry about the sequential number part only.

 

[spike250]

Hi,

Thanks for you reply.

How can I go about getting the number to increase?
Also
When the number is stored in the table I need it to show the full Z,09.00000 is this possible?

Spike

 

[NigelShaw]

Hi Spike

is the Z always going to be Z and never changed? There are 2 ways of approaching this.

1. Hard copy Z into your code
2. Save Z to a table and call it.

So let's say for example, z is hard coded. You would expect something like

Dim strZ As String
Dim dteDate As Date
Dim intAutoNum As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset

strZ = "Z"
dteDate = Format( Date ("YY"))

Set db = CurrentDb
Set rs = Db.OpenRecordset("MyTable", DbOpenDynaset)

With rs
    If Not .EOF Or .BOF Then
       .MoveLast
    End If

     intAutoNum = !PkID
End With

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

MsgBox StrZ & "," dteDate & "." & intAutoNum

should give you a result returned in the message box
you would the use the returned result to display in your form

Me.MyTextField.Value = StrZ & "," dteDate & "." & intAutoNum

I'll have a better look later, I'm on my phone at the minute putting my child to bed!! Hellish task

regs

Nidge

 

[gemma-the-husky]

its a matter of separating the storage from the display

now the user should NEVER see the table

so it doesnt matter to him how you store the bits of the record number. but splitting it makes it easier for you to handle

so
a) store the code letter in one field - Z, or R, or ABC etc etc
b) store the prefix number in another - 09, or 01, or 02
[note that showing leading zeroes on numbers means either a display option or storing as text - in which case you need to be aware of the maximum number you may go to - ie is 00 to 99 suffciient, or does it need to be 001-999. Leading zeroes are always tricky as a number (integer) doesnt have leading zeroes - 01 is just 1, and 001 is also 1 -so you have no mechanism to tell these apart
c) store the sequential bit as a number

then, when you show them to the user, just put them back as
Z-09-12345

so the sequential bit. the easiest way is to set a new number to be equal to one more than the highest number in the table - so basically this does it

newnumber = dmax("numberfield"."table")+1

thats why is better to store the number on its own - you can add 1 to it

if you store Z-09-12345, you cant just add 1 to it, because its a text string, not a number.

 

[spike250]

Hi Nigel,

The code that you posted on the 11th October is not working?

dteDate = Format( Date ("YY"))

MsgBox StrZ & "," dteDate & "." & intAutoNum

The above two bits of code are being highlighted in red.

Can this code be put in the On Focus part of the text box?

Spike

 

[NigelShaw]

Spike,

if it is purely for visual purposes ( as i guess it would because you would save the full date to any table ), change-

Dim dteDate As Date

to

Dim dteDate As String

if the date is saved to a table, you can get it by ( full code )-

Dim strZ As String
Dim dteDate As String
Dim intAutoNum As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset

strZ = "Z"

Set db = CurrentDb
Set rs = Db.OpenRecordset("MyTable", DbOpenDynaset)

With rs
    If Not .EOF Or .BOF Then
       .MoveLast
    End If

     intAutoNum = !PkID
     dteDate = MyDate 'change to suit the date field in your table
End With

rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

'Format the collected date to show only the year
dteDate = Format( dteDate ("YY"))

MsgBox StrZ & "," dteDate & "." & intAutoNum

regs

Nigel