Bernard Peek <ba*@shrdlu.com> wrote in message news:<oC**************@shrdlu.com>...
In message <d1**************************@posting.google.com >, bigbinc
<bi*****@hotmail.com> writes
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.
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.
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.