S
Safwany
Dear Fye,
I posted this question somtime ago and the below was your answer, I tried
today to add it in different places and in different format but it failed, I
need to know where exatly to add this function and if it does work with MS
Access 2007 or there could be any change in the function format?
Thanks for your help...
__________________________________________________________________
Subject: RE: Using a sequence number 1/7/2009 5:16 AM PST
By: Dale Fye
In: microsoft.public.access.queries
Autonumber fields should be used as unique values which have no meaning to
your database users, and you should probably never expose them to your users.
If you need a field that your users will see, which you want to increment by
one for each new record, then you you need to use a function to return that
value for you. A simple version of the function would be:
Public function fnNextRecord() as long
fnNextRecord = NZ(DMAX("PO_Num", "PurchaseOrders"), 0) + 1
End function
This would return the next largest number for use in the [PO Num] field. If
a record is subsequently deleted, and no one has created a PO with a higher
number, then this number would be reused.
The caution with using this is that if you have a multi-user application,
then there is a chance that two users will be creating purchase orders at
almost the same time.
Depending on when you call this function in your code, if you don't save the
record immediately after generating the PO_Num, then the other user could
generate a PO with the exact same number. For this reason, I advise you to
either save the record immediately after generating this number, or not
generate the number until the Forms BeforeUpdate event.
--
HTH
Dale
email address is invalid
Please reply to newsgroup only.
I posted this question somtime ago and the below was your answer, I tried
today to add it in different places and in different format but it failed, I
need to know where exatly to add this function and if it does work with MS
Access 2007 or there could be any change in the function format?
Thanks for your help...
__________________________________________________________________
Subject: RE: Using a sequence number 1/7/2009 5:16 AM PST
By: Dale Fye
In: microsoft.public.access.queries
Autonumber fields should be used as unique values which have no meaning to
your database users, and you should probably never expose them to your users.
If you need a field that your users will see, which you want to increment by
one for each new record, then you you need to use a function to return that
value for you. A simple version of the function would be:
Public function fnNextRecord() as long
fnNextRecord = NZ(DMAX("PO_Num", "PurchaseOrders"), 0) + 1
End function
This would return the next largest number for use in the [PO Num] field. If
a record is subsequently deleted, and no one has created a PO with a higher
number, then this number would be reused.
The caution with using this is that if you have a multi-user application,
then there is a chance that two users will be creating purchase orders at
almost the same time.
Depending on when you call this function in your code, if you don't save the
record immediately after generating the PO_Num, then the other user could
generate a PO with the exact same number. For this reason, I advise you to
either save the record immediately after generating this number, or not
generate the number until the Forms BeforeUpdate event.
--
HTH
Dale
email address is invalid
Please reply to newsgroup only.