Prev: Query filtering by Aggregate
Next: SQL Select Query Prompting for NonExistent Parameter
From: Wes on 17 Mar 2010 17:15
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
From: KARL DEWEY on 17 Mar 2010 17:58
Search on DMax for your need.

--
Build a little, test a little.


"Wes" wrote:

> 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
> .
>
From: Marshall Barton on 17 Mar 2010 18:25
Wes wrote:

>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.

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Query filtering by Aggregate
Next: SQL Select Query Prompting for NonExistent Parameter