Autonumber mess, figure out the number

I am in autonumber hell, it is important for me to get the AutoNumber
even in case of previous deleted records, and I cant get the value
through a 'test' insert and then delete method. There is no VBA
function that has that value in memory somewhere. That would be the
ideal.

For example:

AutoNumber-Field:
1
2
3
4
5 *-
6 *---- I need to delete these records
7 *-
8 *
9 *

*Delete*
so now I have

1
2
3
4
--> Next insert
10

From the example above, I really need to get that value '10' without
performing a 'test' insert, I was thinking of using a meta-table or
something, other than that I think I have tried everything, any ideas?

re: Autonumber mess, figure out the number

bigbinc, You cannot reclaim those lost autonumbers nor should you want/need
to. This number should only serve to ensure the uniqueness of a record and
nothing else. You can design/create your own tracking number using other
functions/methods. More info here
http://www.mvps.org/access/general/gen0025.htm

--
Reggie

----------
"bigbinc" <bigbinc@hotmail.com> wrote in message
news:d1b33313.0406170619.5f0bd96a@posting.google.c om...[color=blue]
> I am in autonumber hell, it is important for me to get the AutoNumber
> even in case of previous deleted records, and I cant get the value
> through a 'test' insert and then delete method. There is no VBA
> function that has that value in memory somewhere. That would be the
> ideal.
>
> For example:
>
> AutoNumber-Field:
> 1
> 2
> 3
> 4
> 5 *-
> 6 *---- I need to delete these records
> 7 *-
> 8 *
> 9 *
>
> *Delete*
> so now I have
>
> 1
> 2
> 3
> 4
> --> Next insert
> 10
>
> From the example above, I really need to get that value '10' without
> performing a 'test' insert, I was thinking of using a meta-table or
> something, other than that I think I have tried everything, any ideas?[/color]

re: Autonumber mess, figure out the number

bigbinc@hotmail.com (bigbinc) wrote in message news:<d1b33313.0406170619.5f0bd96a@posting.google. com>...[color=blue]
> I am in autonumber hell, it is important for me to get the AutoNumber
> even in case of previous deleted records, and I cant get the value
> through a 'test' insert and then delete method. There is no VBA
> function that has that value in memory somewhere. That would be the
> ideal.[/color]
[color=blue]
> From the example above, I really need to get that value '10' without
> performing a 'test' insert, I was thinking of using a meta-table or
> something, other than that I think I have tried everything, any ideas?[/color]

You misunderstand the purpose of an autonumber. An autonumber is only
guaranteeed to be unique. That's its sole purpose. If you need to
force the numbers to be sequential and fill in gaps where you've
deleted records, then you'll have to roll your own method.

re: Autonumber mess, figure out the number

Perhaps you're trying to use autonumber for something it is not
designed for. My recommendation would be to use autonumber as a unique
record key only, and if you need a consecutive number field for some
other purpose, then create one and populate it yourself on record
insert.

Hope this helps,

Peter De Baets
Peter's Software - MS Access Tools for Developers
http://www.peterssoftware.com

bigbinc@hotmail.com (bigbinc) wrote in message news:<d1b33313.0406170619.5f0bd96a@posting.google. com>...[color=blue]
> I am in autonumber hell, it is important for me to get the AutoNumber
> even in case of previous deleted records, and I cant get the value
> through a 'test' insert and then delete method. There is no VBA
> function that has that value in memory somewhere. That would be the
> ideal.
>
> For example:
>
> AutoNumber-Field:
> 1
> 2
> 3
> 4
> 5 *-
> 6 *---- I need to delete these records
> 7 *-
> 8 *
> 9 *
>
> *Delete*
> so now I have
>
> 1
> 2
> 3
> 4
> --> Next insert
> 10
>
> From the example above, I really need to get that value '10' without
> performing a 'test' insert, I was thinking of using a meta-table or
> something, other than that I think I have tried everything, any ideas?[/color]

re: Autonumber mess, figure out the number

"Reggie" <NoSpamreggie@NoSpamsmittysinet.com> wrote in message news:<9vWdnYY0M66hSkzd4p2dnA@comcast.com>...[color=blue]
> bigbinc, You cannot reclaim those lost autonumbers nor should you[color=green]
> > From the example above, I really need to get that value '10' without
> > performing a 'test' insert, I was thinking of using a meta-table or
> > something, other than that I think I have tried everything, any ideas?[/color][/color]

I just performed a null insert and then got the value of the
autonumber after the insert and then performed a delete. I didnt want
to do that, but whatever.

re: Autonumber mess, figure out the number

info@peterssoftware.com (Peter De Baets) wrote in message news:<f5f78ef3.0406171328.392f9648@posting.google. com>...[color=blue]
> Perhaps you're trying to use autonumber for something it is not
> designed for. My recommendation would be to use autonumber as a unique
> record key only, and if you need a consecutive number field for some
> other purpose, then create one and populate it yourself on record
> insert.
>
> Hope this helps,
>
> Peter De Baets
> Peter's Software - MS Access Tools for Developers
> http://www.peterssoftware.com
>
> bigbinc@hotmail.com (bigbinc) wrote in message news:<d1b33313.0406170619.5f0bd96a@posting.google. com>...[color=green]
> > I am in autonumber hell, it is important for me to get the AutoNumber
> > even in case of previous deleted records, and I cant get the value
> > through a 'test' insert and then delete method. There is no VBA
> > function that has that value in memory somewhere. That would be the
> > ideal.
> >
> > For example:
> >
> > AutoNumber-Field:
> > 1
> > 2
> > 3
> > 4
> > 5 *-
> > 6 *---- I need to delete these records
> > 7 *-
> > 8 *
> > 9 *
> >
> > *Delete*
> > so now I have
> >
> > 1
> > 2
> > 3
> > 4
> > --> Next insert
> > 10
> >
> > From the example above, I really need to get that value '10' without
> > performing a 'test' insert, I was thinking of using a meta-table or
> > something, other than that I think I have tried everything, any ideas?[/color][/color]

Hmm, interesting, I didnt design the database, I just have to work
with it, ideally I need the autonumber-value to link with other tables
and that is why I need to find that value. I figure out the problem,
but wouldnt you agree if I can gather information through GUIDs on
external registry values or build DirectShow movies into an access
database, wouldnt it make sense to have some kind of COM function to
find just a simple value. Obviously the value is stored somewhere,
and I see this question asked all the time, so it is not an insane
idea.

re: Autonumber mess, figure out the number

In message <d1b33313.0406172136.6a8a8ac4@posting.google.com >, bigbinc
<bigbinc@hotmail.com> writes

[color=blue]
>Hmm, interesting, I didnt design the database, I just have to work
>with it, ideally I need the autonumber-value to link with other tables
>and that is why I need to find that value.[/color]

How about creating a query that links the tables and then updating that.
You wouldn't need to know what the value of the Autonumber filed is.



--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

re: Autonumber mess, figure out the number

Bernard Peek <bap@shrdlu.com> wrote in message news:<oC855L1sLr0AFwO7@shrdlu.com>...[color=blue]
> In message <d1b33313.0406172136.6a8a8ac4@posting.google.com >, bigbinc
> <bigbinc@hotmail.com> writes
>
>[color=green]
> >Hmm, interesting, I didnt design the database, I just have to work
> >with it, ideally I need the autonumber-value to link with other tables
> >and that is why I need to find that value.[/color]
>
> How about creating a query that links the tables and then updating that.
> You wouldn't need to know what the value of the Autonumber filed is.[/color]


I have two tables, one is a link to the secondary-table, the
secondary-table contains the autonumber.

TABLE-PRIMARY:

[Auto-#] [We set the all the values in this table(i.e. 1
1 )]
1 -> records in table-secondary: 1-1
-> records in table-secondary: 1-2
-> records in table-secondary: 1-3


2 -> records in table-secondary: 2-1
-> records in table-secondary: 2-2
-> records in table-secondary: 2-3

3 ...
4 ...

5 ...

if we delete records 3 and 4 and 5, on the next insert,

*now insert*

6 -> records in table-secondary: 6-1
-> records in table-secondary: 6-2
-> records in table-secondary: 6-3

This is ideal, now assuming I cant change the structure of the table(I
can create meta-data tables), how would I get the value '6', (I
currently perform a null insert-delete and get the expected value, for
example '7'[skips 6])

I guess, the table should be setup with my own custom counter and that
should link back to the secondary-table, but the link(primary-table)
is on the autonumber.

if I perform 'select max auto-field' then I would get the value
'3'(wrong), so when I insert a record the link will be thrown off.

[Auto]
6 -> records in table-secondary: 3-1(wrong)
-> records in table-secondary: 3-2(wrong)
-> records in table-secondary: 3-3(wrong)


What do you think I could do that would be a better solution than what
I have.

re: Autonumber mess, figure out the number

In message <d1b33313.0406180628.203433b4@posting.google.com >, bigbinc
<bigbinc@hotmail.com> writes

[color=blue]
>
>What do you think I could do that would be a better solution than what
>I have.[/color]

I'm still trying to work out why you need to know the value in the
autonumber field. If you create a query that is based on the primary and
secondary tables and linked via the autonumber field then you know that
the same value will be used in each table, without needing to know what
that value is.

Is there some other reason why you want to know the value?

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.