Autonumber

M

magmike

Is it possible to create your own autonumber scenario by using the
DefaultValue property? For example, have code that takes the largest
value from all records, and adds 1 to it and inserts that value as the
default value of a new record?

Thanks!
 
B

boblarson

Yes, that is how many people do it who do not want to use the supplied
Autonumber datatype.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials at http://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.
 
M

magmike

Yes, that is how many people do it who do not want to use the supplied
Autonumber datatype.
--
Bob Larson
Access World Forums Super Moderator
Utter Access VIP
Tutorials athttp://www.btabdevelopment.com
__________________________________
If my post was helpful to you, please rate the post.






- Show quoted text -
Click to expand...

Exactly how is it done? I've tried a couple of scenarios that did not
work.

Thanks!
 
A

Anthos

I generally work on a agregated query that searches for the last
record.

Here is an example of a query I run, which I then do a +1 to to
enumerate the next Invoice number for a particular site.

e.g.
SELECT Count(tbl_data_InvoiceMatrix.InvoiceNumber) AS
LastInvoiceNumber
FROM tbl_data_sites INNER JOIN tbl_data_InvoiceMatrix ON
tbl_data_sites.SiteName = tbl_data_InvoiceMatrix.InvoiceSite
GROUP BY tbl_data_sites.SiteName
HAVING (((tbl_data_sites.SiteName)=fncCurrentSite()));

Let me know if there is anything else I can do to help, or if this
doesn't work for you.

Regards
Anthony Moore
IT Excellence
 
M

magmike

I generally work on a agregated query that searches for the last
record.

Here is an example of a query I run, which I then do a +1 to to
enumerate the next Invoice number for a particular site.

e.g.
SELECT Count(tbl_data_InvoiceMatrix.InvoiceNumber) AS
LastInvoiceNumber
FROM tbl_data_sites INNER JOIN tbl_data_InvoiceMatrix ON
tbl_data_sites.SiteName = tbl_data_InvoiceMatrix.InvoiceSite
GROUP BY tbl_data_sites.SiteName
HAVING (((tbl_data_sites.SiteName)=fncCurrentSite()));

Let me know if there is anything else I can do to help, or if this
doesn't work for you.

Regards
Anthony Moore
IT Excellence
Click to expand...

I'm a little nervous about using Count +1 in the event that a record
(that is not the last record) has been deleted and then shortly after
a new record created, recreating the most recent id# and end up with
duplicates in the system. Do you think there is another way it could
be done - like using the highest value of the field and then +1 on
that? Please forgive me for being unschooled.

magmike

PS - know of a good beginners resource for coding?
 
D

Douglas J. Steele

magmike said:
I'm a little nervous about using Count +1 in the event that a record
(that is not the last record) has been deleted and then shortly after
a new record created, recreating the most recent id# and end up with
duplicates in the system. Do you think there is another way it could
be done - like using the highest value of the field and then +1 on
that? Please forgive me for being unschooled.
Click to expand...


Use Max

SELECT Nz(Max(tbl_data_InvoiceMatrix.InvoiceNumber),0) AS
LastInvoiceNumber
FROM tbl_data_sites INNER JOIN tbl_data_InvoiceMatrix ON
tbl_data_sites.SiteName = tbl_data_InvoiceMatrix.InvoiceSite
GROUP BY tbl_data_sites.SiteName
HAVING (((tbl_data_sites.SiteName)=fncCurrentSite()));

PS - know of a good beginners resource for coding?
Click to expand...

Jeff Conrad has a great set of resources documented at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html
 
Top