Expression for Default Value

[SmartestIdiot]

I have a form that opens as a new record, but there is one field I would like to show the last value in the field "ControlNumber". I'm avoiding the VBA because of the security on the local machines. I have an expression in the default value property that looks like this:



=Last([tblLeave]![ControlNumber])

I just need the last control number to show in the form so the user can enter in the next sequential Control Number.

I'm using Access 2003.

[pbaldy]

Don't think that will work, but try DMax() (or DLast(), but that often produces unexpected results).

[rpeare]

Why are you leaving the entry of this control number to the user? You can populate it without the user having to touch it using the dmax function as pbaldy described. And what does VBA have to do with security you can set up trusted locations on your server or trusted files. At any rate if this is an important field and you want to 'force' a specific value you also want to be careful if you are using a BOUND form because if you actually populate the field then, say, open and close the form 10 times, you're going to end up with 10 blank records with just the controlnumber populated in your table.

[SmartestIdiot]

The Field I want populate is actually the key, so it won't produce multiple records with the same entry. The key is a combination of letters and numbers so its not something that can be predicted by access. I thought it would be easy to get the last value in a field, but it s turning out to be a headache.

[SmartestIdiot]

Still no luck. I'm avoiding VBA because every time I try to put a module in the form I get an alert message about empty cells or having the Have module property set to yes. Its a real pain.

[rpeare]

it sounds to me like you don't need this to be your primary key, if you make an autonumber your primary key that would be better. If this is something like a patient control number, or customer number that is an internal designation of your system and you can build that customer or patient number based on other fields in your data you can also carry this internal number through and calculate it at the time of data entry if it's a 'new' record. I would strongly recommend that you don't try to create your own PK if you're not extremely comfortable with VBA.

[SmartestIdiot]

I took the suggestion of using the autonumber as the primary key, however I am still have issues getting the text field to populate with the last control number using Dmax function in as the default value. I'm not sure if I'm approaching this the right way. I just know this is what I need. A txt box that will show the last control number entered so the user can add the next sequential control number.

[June7]

Generation of custom unique identifier is a common topic.

Here is one thread https://www.accessforums.net/access/...nce-40410.html