How do I customize autonumber in access?

K

Khalid Al-Masoodi

Hi,

I need to customise an autonumber. When I enter a new record in the table, I
need a feild that **AUTO GENERATE'S "DDMMYYYY-$"**.

The $ sign is a squence number starting from 1 ongoing. This is for each day.

(example. A record entered on 5th Jan 2005 will show 05012005-1, A record
entered on 6th Jan 2005 will show 06012005-1, A second record entered on 6th
Jan 2005 will show 06012005-2).

Is this possible ???? The feild type does not have to be an autonumber.

Really needing help.

Khalid Al-Masoodi
[email protected]
 
A

Alex White MCDBA MCSE

Well the only field type that would work is a string, the autonumber system
could not cope with this, you need a my_counter field (numeric) and a
my_date field (date) in a control table. When getting the next number lookup
in the control table for my_date if my_date = today then increment
my_counter by 1 and pass the value back, if the my_date <> today then set
my_date = today and reset my_counter = 1 and pass 1 back.

to build the string you would want something like

dim strKey as string
strKey = format(now,"DDMMYYYY) & "-" & GetNextKey


Public Function GetNextKey() as string
dim adoControl as new adodb.recordset
adoControl.open "Select my_Date, my_Counter from
TblControl",currentproject.connection,adopenKeyset,adOpenDynamic
if .recordcount > 0 then
if fields("my_Date).value = Format(MyDateValue, "\#yyyy\-mm\-dd\#")
then
if the same then
.fields("my_Counter").value = .fields("my_Counter").value +
1
GetNextKey = trim(str(.fields("my_Counter").value + 1))
else
.fields("my_date").value = now
.fields("my_counter").value = 1
GetNextKey = 1
end if
.update
.close
end if
end if
end function

you will have to test as I have just knocked it up without testing....
 
L

Larry Daugherty

In the context of Access it's unwise to refer to anything other than
an Autonumber data type provided by Access as an "autonumber".

You'll need to add a table to store program variables: WorkingDate and
SequenceNumber. The Sequence number stored in the table will always
be the next available number.

In operation, if the current date is greater than the stored date then
save the current date as the new working date and set SequenceNumber
to 001. To create the Magic number:

MagicNumber= format(Date(), "ddmmyyyy") & "-" & SequenceNumber

Increment and save SequenceNumber

HTH
 
Top