I have tried searching to no avail. I'd like to dim a public variable that would represent the max value of a specific field in a table. To skin the cat another way, the field is an autonumber so representing the value for that field in the last record of the table would also suffice.
Create a new module. At the top of the window, just under where it says Option Compare Database (or Option Explicit), type this:
Code:
Public lngLastNum as long
Save the module, giving it a name when prompted.
You have now created a public variable to hold a value. This doesn't, by itself, get you your highest number.
To do that, you can use the DMAX function with your public variable. Create a form and put a command button on it. On the command button's click event, put code like the following:
Code:
'assign a value to my public variable
lngLastNum = DMAX("FieldName","TableName")
debug.print lngLastNum 'see immediate window
This assigns the maximum number in the field "FieldName" in the table "TableName" to your variable. (In the DMAX statement, swap in, of course, your app's field and table names.) The debug.print line prints the value of your variable to the Immediate Window on your VBA screen (a quick, easy way to test). Run your form and click your button and hope for the best...
(You can also, by the way, simply put the DMAX statement into the controlsource property of a textbox on a form.)
