Assigning the result of a Max() Function to a variable

[pledbetter]

Given: A table with a unique Integer field [ApplID]

From an unbound form, I want to determine the highest number in [ApplID], increment that value by 1 and assign it to a variable "NextApplID".



Pseudocode: NextAppID = ("SELECT MAX(ApplID) FROM tblApplicant") + 1

How can i do that? It's Friday, I'm foggier than usual

[June7]

Can't use SQL statement like that.
Use DMax() domain aggregate function.

[Bulzie]

Don't forget to add code to increment the value in your table also.

[Micron]

to the question, maybe this?

NextApplID = DMax("ApplID","tblApplicant") + 1
https://learn.microsoft.com/en-us/of...plication.dmax

[CJ_London]

I do hope your app is not multi-user - two users determining NextAppID at the same time will get the same result.

[Micron]

I do hope your app is not multi-user - two users determining NextAppID at the same time will get the same result.

Agreed. That's why when I had to do this sort of thing the value got created when the form was saved (as long as user does not really need to know what it would be up front). If user does need to know but bails on the process at the end, then that number could be created but flagged somehow; e.g. archived - as long as consecutive values are not required. Often they are deemed to be required, but really aren't. Gaps in numbers play mind games with some people.

[ArviLaanemets]

Pseudocode: NextAppID = ("SELECT MAX(ApplID) FROM tblApplicant") + 1

Try

NextAppID = (SELECT TOP 1 ApplID FROM tblApplicant ORDER BY ApplID DESC)+1

[pledbetter]

I do hope your app is not multi-user - two users determining NextAppID at the same time will get the same result.

Only one user will be using this at a time.

[pledbetter]

Try

NextAppID = (SELECT TOP 1 ApplID FROM tblApplicant ORDER BY ApplID DESC)+1

"Type Mismatch". I changed the variable type from Integer to String, and same error.

[pledbetter]

The computed number will eventually be the very number presented in a future INSERT INTO action.

[Welshgasman]

Try

NextAppID = (SELECT TOP 1 ApplID FROM tblApplicant ORDER BY ApplID DESC)+1

Can you really use SQL like that?

[Welshgasman]

The computed number will eventually be the very number presented in a future INSERT INTO action.

Not using bound form then?

[pledbetter]

to the question, maybe this?

NextApplID = DMax("ApplID","tblApplicant") + 1
https://learn.microsoft.com/en-us/of...plication.dmax

That's what I get for not reading the Syntax rules carefully. I didn't think the Criteria switch was optional. Thanks!

[Micron]

That's what I get for not reading the Syntax rules carefully. I didn't think the Criteria switch was optional. Thanks!

I take it that solved your issue?

Maybe you were looking at the wrong documentation for the application. AFAIK, it is required for Excel. Got my feet wet with Access vba. When I see how different common things are between these apps I wonder if the Excel guys were invited to the same meetings. That comment doesn't necessarily pertain to DMax, just in general.

If you have a link where it says it is required for Access vba (not just someone's misinformed post), I'd like to check it out if you'd care to post it.

[CJ_London]

Only one user will be using this at a time.

I have a client who tells me the same thing. So far they have messed the data up three times as they 'forgot'