Returning A Varibale from a query

[Wes]

I have a form that needs to create a unique ID upon entry. Like a new
Sales Order Number for a new sale.

What I want to do is the run a query that return the MAX value for a
field.

"SELECT MAX(SALNUM) FROM TABLE"

I can then add 1 to that number and create and a new key.

I am having a problem trying to determine how the return the result to a
variable.

strA = SELECT ...... or some such thing

I have tried to look this up in the manual (RTFM) but have not had much
luck.

Any help appreciated.

Thanks

Wes

 

[KARL DEWEY]

Search on DMax for your need.

 

[Marshall Barton]

I have a form that needs to create a unique ID upon entry. Like a new
Sales Order Number for a new sale.

What I want to do is the run a query that return the MAX value for a
field.

"SELECT MAX(SALNUM) FROM TABLE"

I can then add 1 to that number and create and a new key.

I am having a problem trying to determine how the return the result to a
variable.

strA = SELECT ...... or some such thing

The Domain Aggregate functions will run that kind of query
for you. The code in the form's BeforeUpdate event that
does what you want is like:

newnum = Nz(DMax("SALNUM", "TABLE"), 0) + 1

It is important to use the form's BeforeUpdate event because
there is a vanishingly small chance of two users getting the
same number. Doing it anywhere else has a significant
probability of duplicate values.