Is it possible to set default value to a SQL query?

[Guest]

I would like to set the default value of an integer column to be:
(SELECT (MAX(THIS_COLUMN)+1) FROM THIS_TABLE)
tested as a querie as
SELECT (MAX(THIS_COLUMN)+1) FROM THIS_TABLE;


Attempting this results in 'object invalid or no longer set'.
Is it possible to find the MAX value (plus 1) of other rows and set the new
default value of this rows column to it?
No - Autonumber cannot be used as this column is user changeable - it is a
sort-order column and user must be able to change the value (via coded
functions to move rows up or down exist and work).
Thanks,
Todd

 

[Guest]

Try using the Domain Aggregrate function DMax.
Here is a page that discusses a similar function, DLookup. The parameters
would be the same (ie. Expression, Domain, Criteria).

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

You might want to also check out Access MVP Roger Carlson's 'simulated'
autonumber. It's not an autonumber at all, but it does include the use of
DMax to retrieve the highest value and then add one to it.

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

 

[John Vinson]

I would like to set the default value of an integer column to be:
(SELECT (MAX(THIS_COLUMN)+1) FROM THIS_TABLE)
tested as a querie as
SELECT (MAX(THIS_COLUMN)+1) FROM THIS_TABLE;


Attempting this results in 'object invalid or no longer set'.
Is it possible to find the MAX value (plus 1) of other rows and set the new
default value of this rows column to it?
No - Autonumber cannot be used as this column is user changeable - it is a
sort-order column and user must be able to change the value (via coded
functions to move rows up or down exist and work).
Thanks,
Todd

No, you cannot do this in Table view in Access (you can use an On
Insert trigger in SQL/Server though, if that's where you have your
data).

The pure-Access alternative would be to do all data entry using a Form
and use the Form's Beforeinsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!This_Column = NZ(DMax("[This_Column]", "[tablename]")) + 1
End Sub

John W. Vinson[MVP]

 

[Guest]

Thanks for the quick answer John - exactly what I was looking for - an honest
'No'.
Using DMax as Tom and you suggest would not work - there are no forms - this
is a code-accessed database. I'll have to modify the code to do this - would
have been nicer if it could have been handled in the database

(Hear that MS - would be a nice improvement - an autoMaxNumber that
automatically sets the column to one more than the highest existing value in
that table's column... could be supported as 1) calling a query in the same
database to get the value or 2) a VB routine or 3) direct SQL support -
simply executing a quick query before doing an insert and using the queries
return value doesn't sound that hard...)

Thanks,
Todd

I would like to set the default value of an integer column to be:
(SELECT (MAX(THIS_COLUMN)+1) FROM THIS_TABLE)
tested as a querie as
SELECT (MAX(THIS_COLUMN)+1) FROM THIS_TABLE;


Attempting this results in 'object invalid or no longer set'.
Is it possible to find the MAX value (plus 1) of other rows and set the new
default value of this rows column to it?
No - Autonumber cannot be used as this column is user changeable - it is a
sort-order column and user must be able to change the value (via coded
functions to move rows up or down exist and work).
Thanks,
Todd

No, you cannot do this in Table view in Access (you can use an On
Insert trigger in SQL/Server though, if that's where you have your
data).

The pure-Access alternative would be to do all data entry using a Form
and use the Form's Beforeinsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!This_Column = NZ(DMax("[This_Column]", "[tablename]")) + 1
End Sub

John W. Vinson[MVP]

 

[Guest]

I hope you don't think my answer was dishonest. After all, you didn't mention
that you were only using JET and not Access.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________