loop for previous year in VBA for MS Access

  • Thread starter Thread starter asset number
  • Start date Start date
A

asset number

i am currently creating a database in MS Access. Its an asset management
system, wherein users input various information pertaining to a certain
company asset. It generates an access number containing the year when the
asset was purchased, a number code to specify what kind of asset is it, and a
unique id, which serves as its primary key. the format looks like this:

yyyyzxxx with y being the year, z as the asset type and x as the primary key.

the primary key is dependent on the year. for example, for the first record,
if the year is 2004, it automatically generates 2004z001. for the next record,
with 2004 still inputted as the year, it generates 2004z002.

my problem is that whenever i try to generate a new asset code, but with a
different input year, for example 2005, the primary key doesnt reset back to
001. it continues on with the count from the previous year. for example,
after entering the two previous records from above, and i entered a new item
with the input year 2005, it generates an asset code 2005z003.

i want this primary key to reset back to 001 if the input year is different
from the previous one. this should be the right format: 2005z001.

how do i create a loop which reads the current input year, and reads the
previous year inputed and checks if the year has changed? if the year has
changed, how can i set the primary key back to 001?
 
In your primary key generator procedure, you can use code such as :
If Nz(DLookup(PrimaryFieldName,CurrentTableName,"PrimaryFieldName Like '" &
YearTextbox & "*'), "") = "" Then
NumCode = Format(1, "00#")
Else
'put codes here that you have already been using to increment the last
NumCode input
End If

'put codes here to use NumCode to generate the new primary key value

Hope that helps
 
How will i do this if the autonumber is the one that increments?
In your primary key generator procedure, you can use code such as :
If Nz(DLookup(PrimaryFieldName,CurrentTableName,"PrimaryFieldName Like '" &
YearTextbox & "*'), "") = "" Then
NumCode = Format(1, "00#")
Else
'put codes here that you have already been using to increment the last
NumCode input
End If

'put codes here to use NumCode to generate the new primary key value

Hope that helps
i am currently creating a database in MS Access. Its an asset management
system, wherein users input various information pertaining to a certain
Click to expand...
[quoted text clipped - 20 lines]
previous year inputed and checks if the year has changed? if the year has
changed, how can i set the primary key back to 001?
Click to expand...
Click to expand...
 
According to your opening thread, you have a textual primary key field,
generated by code. How does AutoNumber come into play?

asset number said:
How will i do this if the autonumber is the one that increments?
In your primary key generator procedure, you can use code such as :
If Nz(DLookup(PrimaryFieldName,CurrentTableName,"PrimaryFieldName Like '" &
YearTextbox & "*'), "") = "" Then
NumCode = Format(1, "00#")
Else
'put codes here that you have already been using to increment the last
NumCode input
End If

'put codes here to use NumCode to generate the new primary key value

Hope that helps
i am currently creating a database in MS Access. Its an asset management
system, wherein users input various information pertaining to a certain
Click to expand...
[quoted text clipped - 20 lines]
previous year inputed and checks if the year has changed? if the year has
changed, how can i set the primary key back to 001?
Click to expand...
Click to expand...
Click to expand...
 
the primary key, along with year (which is inputed through a field) is
generated everytime i click the generate button. autonumber increments the
previous primary key number by 1, and assigns that new number to the new
primary key. however, whenever i input a new year(not equal to the previous
one), autonumber still increments the primary key, rather than resetting it
to 001 .
According to your opening thread, you have a textual primary key field,
generated by code. How does AutoNumber come into play?
How will i do this if the autonumber is the one that increments?
Click to expand...
[quoted text clipped - 16 lines]
Click to expand...
 
In the code behind the Generate button, quit using an autonumber as part of
the primary key value, because you cannot control AutoNumbers, and generate
you own number based on data in your table. For instance,
If Nz(DLookup(PriKeyFieldName,CurrentTbleName,"PriKeyFieldName Like '" &
YearTextBox & "*'"), "") = "" Then
NumCode = Format(1,"00#")
Else
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT PriKeyFieldName FROM CurrentTableName
WHERE Left(PriKeyFieldName,4) = '" & YearTextBox & "' ORDER BY
SomeAutoNumberFieldName DESC")
rst.MoveFirst
NumCode = CInt(Left(rst!PriKeyFieldName),4)+1
Endif

'put codes here to use NumCode to your liking to generate you key
asset number said:
the primary key, along with year (which is inputed through a field) is
generated everytime i click the generate button. autonumber increments the
previous primary key number by 1, and assigns that new number to the new
primary key. however, whenever i input a new year(not equal to the previous
one), autonumber still increments the primary key, rather than resetting it
to 001 .
According to your opening thread, you have a textual primary key field,
generated by code. How does AutoNumber come into play?
How will i do this if the autonumber is the one that increments?
Click to expand...
[quoted text clipped - 16 lines]
previous year inputed and checks if the year has changed? if the year has
changed, how can i set the primary key back to 001?
Click to expand...
Click to expand...
Click to expand...
 
ok..thanks..now how do we generate our key, based on the input year?is this
the looping code which terminates if the user input a different year?
In the code behind the Generate button, quit using an autonumber as part of
the primary key value, because you cannot control AutoNumbers, and generate
you own number based on data in your table. For instance,
If Nz(DLookup(PriKeyFieldName,CurrentTbleName,"PriKeyFieldName Like '" &
YearTextBox & "*'"), "") = "" Then
NumCode = Format(1,"00#")
Else
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT PriKeyFieldName FROM CurrentTableName
WHERE Left(PriKeyFieldName,4) = '" & YearTextBox & "' ORDER BY
SomeAutoNumberFieldName DESC")
rst.MoveFirst
NumCode = CInt(Left(rst!PriKeyFieldName),4)+1
Endif

'put codes here to use NumCode to your liking to generate you key
the primary key, along with year (which is inputed through a field) is
generated everytime i click the generate button. autonumber increments the
Click to expand...
[quoted text clipped - 11 lines]
Click to expand...
 
Assuming that there are at least 2 controls on your form that are filled
before you click the GenerateKey button, 1 with the Year of the asset,
another with the Asset type code, you would use the codes that I posted
earlier to generate the Numcode based on whether year is new or not. Then tie
it all up with the following:

PriKeyFieldName = YearTextBox & AssetTypeCode & NumCode.

asset number said:
ok..thanks..now how do we generate our key, based on the input year?is this
the looping code which terminates if the user input a different year?
In the code behind the Generate button, quit using an autonumber as part of
the primary key value, because you cannot control AutoNumbers, and generate
you own number based on data in your table. For instance,
If Nz(DLookup(PriKeyFieldName,CurrentTbleName,"PriKeyFieldName Like '" &
YearTextBox & "*'"), "") = "" Then
NumCode = Format(1,"00#")
Else
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT PriKeyFieldName FROM CurrentTableName
WHERE Left(PriKeyFieldName,4) = '" & YearTextBox & "' ORDER BY
SomeAutoNumberFieldName DESC")
rst.MoveFirst
NumCode = CInt(Left(rst!PriKeyFieldName),4)+1
Endif

'put codes here to use NumCode to your liking to generate you key
the primary key, along with year (which is inputed through a field) is
generated everytime i click the generate button. autonumber increments the
Click to expand...
[quoted text clipped - 11 lines]
previous year inputed and checks if the year has changed? if the year has
changed, how can i set the primary key back to 001?
Click to expand...
Click to expand...
Click to expand...
 
Personally I would have an autonumber field as your primary key, then split
your reference number into three fields - RefYear (integer), RefType (text,1)
and RefSeq (integer) -and define a unique index on those three fields.

When adding a new record, RefYear and RefType will be gleaned from user
input, RefSeq will be

Nz(DMax("RefSeq","tblAssets","RefYear = " & txtInputYear),0) + 1

You can always recreate your reference on forms/reports using RefYear &
RefType & Format(RefSeq,"000")

Jon.
 
Refinement...
Personally I would have an autonumber field as your primary key, then split
your reference number into three fields - RefYear (integer), RefType (text,1)
and RefSeq (integer) -and define a unique index on those three fields.
Click to expand...

You should exclude the RefType field from Unique index. If you included the
RefType field this would allow 2004a001 and 2004b001 to be added without
violating the unique constraint.
 
Back
Top