Autonumbers

[wjtuk]

Hi

I find this site really useful for any of my access queries but i have one here that i can not find a solution for as i know vey little sql.

I did not build the database but need to get a fix in for my problem.

I have a unique reference in five tables which is generated off the autonumber: 12345ABC1, 12345ABC2 etc

For reasons that will take me a while to write here and on a rare occassion i need to reset the autonumber to a certain value i found this very clever little bit of code in SQL that resets it to a desired value:

ALTER TABLE A_Business_Details ALTER COLUMN Bus_ID COUNTER(1,1);

Bus_ID is the autonumber and i just change the COUNTER(1,1) to my desired value

but instead of going into the sql and manually putting the figure in i need, i want it to look up a value in a field in a query, which is only doing a count.

So if the value in the query is 25 i want to be able to run the SQL query to reset the autonumber to that value.

I know this is how autonumbers should not be played with like that, but i can not change how the database works for various reasons.

Thanks for any help on this.

Wayne

 

[plog]

What exactly is your question? 'How do I get the count from a query?'

If so, you would use the Dlookup Function: http://www.techonthenet.com/access/functions/domain/dlookup.php

If not, please clarify what your question is.

 

[wjtuk]

Thanks for your response.

I need to reset the autonumber of the table to a result in a query.

For example if the query has a result of 25 in the Bus_Ref column i need to reset the autonumber too that.

Thanks again for your help.

 

[Galaxiom]

Autonumber cannot be controlled. If you need this ability you must manage the increment of the field value manually. One common way to do it is with the DMax + 1 technique.

Search the forum for DMax and most of the threads will cover this topic.

 

[wjtuk]

Ok thanks for your help. I have decided to ignore the autonumber and generate my own by a max as you have mentioned.

Thanks for your help.