Can anyone solve this

[tedward]

I would like to use a control button and an UnBound field on a form to obtain a unique record number in Access 2010 starting with 00001. The number will be incremented by 1 each time the control button is selected. The difficulty is on Oct 1st of every year the record count has to auto reset and start over at 00001. As the record number increases the preceding zeros should decrease to maintain four digits or characters, e.g, 00193. Thus far, nothing that I have tried in vba has worked. Any ideas how this might be accomplished? Any ideas or help will be much appreciated.


Thanks:banghead:

 

[Pat Hartman]

Are you planning on deleting all the records on Sept 30th? If you don't, you'll be generating duplicate numbers.

Why won't an ever increasing number work? As long as you store the date the record was added, you can tell in what fiscal year it was entered and in a report you can number the records starting from 1.

 

[tedward]

I plan to keep the records which will be tied to a year date. For instance 2012-00001, etc. This is for historical reasons. Thank you for replying.

 

[VilaRestal]

you can format a number with preceding zeroes very easily:

Format(Field1,"00000")

Store the number as a number and just use that to display it.

So, you're only left with devising a system of resetting the number to 1 and incrementing it.

When you say tied to a year, does the mean on 1st Sep the year will become 2013?

i.e.: Year(Now()) + IIf(Month(Now())>8,1,0)

And anyway, this shouldn't be a primary key field. Make an autonumber primary key that will be hidden from the users. These fields (year and this number) should be display fields and nothing more.

You would set this fields value when a new record is created to something like:

1 + DMax("Field1","Table1","YearNumber = 2013")

It depends how many records you'll have and how many users will be adding them (the chance of collisions). It may need to be much more sophisticated than that.

 

[gemma-the-husky]

if you want preceding zeroes to be permanent you would have to have a field type of text.

as long as everything is the same length you could still use dmax to find the hishest number in the table, convert it to an integer, add 1, and convert back to a 5-char string.

in some cases preceding zeroes are important - in which case this scenario is necessary.

this is maybe also sutitable for a field that is generally numeric, but could also be text.

 

[tedward]

I can change the year at the start of the new fiscal year and add leading zeros without any problem. Resetting the records to one (1) to start over at the new fiscal year is the problem. This seem to be a very difficult problem to solve. The logic is difficult to solve with Access VBA.

Thanks for the effort.
:banghead:

 

[NigelShaw]

Hi,

i cant see why this is difficult to be honest. You shouldnt be using this resetable number as an ID anyway so, it should be a field entry. There have been some good suggestions here already but this could be reset simply by using a settings table. For example-

i use a table for storing numbers. it holds the latest

PO number
Invoice number
Works number
Prefix
Year-End

for every order, invoice, works number i get the latest number from this table and add 1 then update the settings table with the latest number. This works well for me and my team. My Year-end field holds the fiscal year so reports are viewed between these dates only. i can view 'pre fiscal year' data but thats an admin right.

for you, you could use a similar method and reset your setting every year but clicking a button or running an end of year function. I run an End of Year function that calls many functions to close of the fical year and open a new one.

btw, my Prefix is annual set. for example, a works order for this year will be-

CK12-0526544/7

CK12- is the prefix, 05 is the current month & 26544 is the project. the/7 is the item of the project that the works order is related to.


hope this helps in some way and i'll give you some help if you are struggling somewhat!



cheers


Nidge

 

[VilaRestal]

Resetting the records to one (1) to start over at the new fiscal year is the problem. This seem to be a very difficult problem to solve. The logic is difficult to solve with Access VBA.

Not at all, like I said:

Me.FiscalYear = Year(Now()) + IIf(Month(Now())>8,1,0)
Me.UniqueNumber = 1 + Nz(DMax("UniqueNumber","Table1","FiscalYear = " & Me.FiscalYear),0)

Easy so long as the fields (year and the number) are kept separate. They can be displayed together but should be stored apart to make this calculation easy.

SELECT FORMAT(FiscalYear,"0000") & "-" & FORMAT(UniqueNumber,"00000") FROM Table1

 

[tedward]

Not at all, like I said:

Me.FiscalYear = Year(Now()) + IIf(Month(Now())>8,1,0)
Me.UniqueNumber = 1 + Nz(DMax("UniqueNumber","Table1","FiscalYear = " & Me.FiscalYear),0)

Easy so long as the fields (year and the number) are kept separate. They can be displayed together but should be stored apart to make this calculation easy.

SELECT FORMAT(FiscalYear,"0000") & "-" & FORMAT(UniqueNumber,"00000") FROM Table1

My table has the following fields: YearDate as text, ID as Number, and the name of my table is "tblYearDate"

I am using before update for my code. When I substitute ID for Unique Number and YearDate for Fiscal year I receive the following error: Data type mismatch in criteria expression. Any ideas what may be wrong?

Thanks

 

[VilaRestal]

My code assumed YearDate was a number and a number it should be. If there's nothing other than numbers stored there then converting it to a number data type should not result in any loss of data and will be better in every way.

Otherwise we're going to have to modify the code a bit to deal with it as text (putting quotes around values for it in SQL).

 

[tedward]

My code assumed YearDate was a number and a number it should be. If there's nothing other than numbers stored there then converting it to a number data type should not result in any loss of data and will be better in every way.

Otherwise we're going to have to modify the code a bit to deal with it as text (putting quotes around values for it in SQL).

And Sir you are exactly right. You have solved my issue for which I greately appreciate. I have put the program through its paces and it works flawless. Changing YearDate to a number was all that was necessary.