Sequence number To "Dale Fye"

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: 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.



"Safwany" wrote:

in the Table design, when I am using the Autonumber and adding data, if you
just press escape key it will skip the sequence and write the next number,
therefoer I want to know how to use a sequence number without skipping any
number.

Safwany,

Where have you tried it because I see Dale has suggested where to put in a
multi-user setting?

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

"Safwany" wrote in message
...
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: 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.



"Safwany" wrote:

in the Table design, when I am using the Autonumber and adding data, if
you
just press escape key it will skip the sequence and write the next
number,
therefoer I want to know how to use a sequence number without skipping
any
number.

I would put this function in a code module.

Open the VB editor
Right click in the Project window (upper left corner). Mouse over "Insert"
and select the Module option.
This will create a new code module. Copy the code and paste it in this
module.

Then, open the form you are using to enter/edit data in design mode. Click
on the square in the upper left portion of the form to select the form.
Then, in the Events tab of the Properties dialog box, find the BeforeUpdate
event. Change it to read Expression, then click on the "..." on the right.
This will open the forms code module, and will take you to the
Form_BeforeUpdate event. In that event, enter code similar to:

Private Sub Form_BeforeUpdate

if me.NewRecord then
me.txtPONum = fnNextRecord()
endif

End Sub

With this code, when you get ready to save the current record, it will check
to see whether it is a new record, or is an old record. If it is a new
record (has never been saved before), then it will call the code to get the
next PO number. If it is not a new record, then it will ignore this line of
code.

HTH
Dale


"Safwany" wrote in message
...
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: 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.



"Safwany" wrote:

in the Table design, when I am using the Autonumber and adding data, if
you
just press escape key it will skip the sequence and write the next
number,
therefoer I want to know how to use a sequence number without skipping
any
number.