Automatic Increment/Logging of a Number

[salisha]

I hope somone out there can help me with this problem. I have this field called CaseNo and this number looks something like this YY-9999. The YY is the last two digits of the year and the 9999 is four digits, starting with 0001. I am trying to find a way to increment this number by one, each time the user logs into a form to make an entry. Are there any suggestions as to how do I go about doing that. Thanks!

 

[SkipVought]

What application are you working in? Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[salisha]

I guess I forgot that important piece of information. I am working with MS Access 2000.

 

[xlbo]

The other questions are
1: Where should this number be stored ?
2: Should the number be created when the form is STARTED or when it is SUBMITTED ie should a number be created if the form is simply opened and then closed without new data being entered ?? Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[salisha]

xlbo, this number is going to be stored in an access table, and it will be created as soon as the form is started. I hope this makes sense.

 

[xlbo]

Ok - bear in mind that I do not work with Access that much (vba anyways)
I've strung this together from 2 posts I found in the archives. Should hopefully do what you want. Needs to be in the form load/activate event

Set rsMyTable = CurrentDb.OpenRecordset("tblMyTable", dbOpenDynaset)
with rsMyTable
.MoveLast
oVal = rsMytable.myFieldA
nVal = right(year(now()),2 & "-" & val(right(oVal,4))+1
.AddNew
.fields("myFieldA&quot.value = nVal
end with

obviously, tblMyTable would be the name of the table where you are storing these unique nos.
myFieldA would be the name of the field that holds these numbers

I hope this works cos I'm not sure I could debug if it doesn't - If not, it may well be worth asking this question in one of the Access forums on this site Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[salisha]

thank you Geoff, im going to work on this right now....

 

[xlbo]

d'oh - forgot a bracket:
nVal = right(year(now()),2) & "-" & val(right(oVal,4))+1
Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[salisha]

thanks geoff. wow, i'm amazed, you are like totally timely on answering my questions, thanks a bunch!~