Get last/max ID or serial#

[rabuayya]

Dear Ms.Access most powerful experts,

I have a problem over which I have lost a lot of hair trying to solve. It may be something simple to you.

I have a table containing list of items and form to enter New items. (a sample database is attached)

Each item has an Id (ItemID), and when entering a new item in the form I would like the last ItemID to be in the default value of the new record ItemID or even better to be the last itemID+1.

Right now what I do, is I run a query that I created to return the Max value of the ItemID and then I enter it manually into the ItemId of the new record. This works but I don't think it's the proper way to do it.

I have used the Max() function in the ItemId default value in the form but it returns an error, I tried the Dmax function but also gives an error! AGghh! What am I doing wrong??

Your input is extremely appreciated.
Thanks
Rami

 

[pr2-eugin]

Hello rabuayya... Use the Form current method, check for New record, then use DMax there.. There should be a sample DB on this forum called DMax()+1, a method put forward by RainLover.. Which might be of great help.. But the simple code will be something along the lines of..

Private Sub Form_Current()
    If Me.NewReocrd Then
        Me.ItemId = Nz(DMax("ItemID", "yourTableName"), 0 ) +1
    End If
End Sub

But just wondering why not use AutoNumber?

PS: I did not get time to go through your sample DB..

 

[rabuayya]

Thanks pr2-eugin,

That worked perfectly. Although I used the same formula, I put in the expression rather than in the code. I don't really know how to use to codes so I just copied and pasted yours and it did the job.

So I don't use Autonumber because sometimes I need to change the serial# or skip one for future use.

Thanks again

 

[pr2-eugin]

Glad you have it working.. Good Luck !

 

[missinglinq]

...Use the Form current method, check for New record...

Note that use the Form_Current event for this kind of thing should only be done in a single-user database! Using it in a multi-user environment greatly increases the chance of two or more New Records having the same number generated:

UserA starts a New Record, the code

Me.ItemId = Nz(DMax("ItemID", "yourTableName"), 0 ) +1

is executed, then UserB starts a New Record, before UserA finishes and saves their New Record, and the code

Me.ItemId = Nz(DMax("ItemID", "yourTableName"), 0 ) +1

is fired again, using the same ItemID that was used by UserA's Record, and two Records now have the same ItemID!

To greatly decrease the chances of this happening, in multi-user environments, use the Form_BeforeUpdate event, instead. This event fires at the last possible second before the Record is saved, and in 10+ years of using this strategy, I've never had an instance of two or more Records being assigned identical numbers.

Also, not using an Autonumber, for something like this, is a good idea, if the number being generated is intended for 'human consumption,' to paraphrase John Vinson! Autonumbers should really only be used as a unique identifier for a Record, to be used 'internal housekeeping duties,' such as searching Records, sorting Records, etc.

Here's the exact quote from John, about Autonumbers:

When using Autonumber, do be aware that there will be gaps in the numbering any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, 1083225152 make such people get very nervous.

Linq ;0)>

 

[jsnfrs]

Hey Guys,

I am trying to implement the Dmax plus function as well, but when I place it in the beforeupdate event I am being getting a "variable not defined error",

Kindly assist.

Regards

 

[missinglinq]

If you're speaking of the DMax()+1 that Paul gave you a link to, that's simply another example of the same hack that we've already given you.

The error would indicate that you've probably misspelled something in your code, a Field name, maybe. There's no way we can trouble-shoot something like this unless you copy and paste your exact code, and post it here for us to eyeball.

Linq ;0)>

 

[jsnfrs]

Not a problem man,

The first sub is the code I got from you guys and tried to get it working but ended up with the error and the second sub is another area I would like help with as well, I am trying to limit the entry of records to 3 entries in this table "tbl_assetsregister" as well but that code doesn't seem to be doing the trick either.



Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.asset id = Nz(DMax("asset id", "tbl_assetsregister"), 0) + 1
End If
End Sub

Private Sub Form_Current()
If Me.NewRecord Then
If Me.RecordsetClone.RecordCount > 2 Then
MsgBox "Only three entries allowed."
Me.Recordset.MoveFirst
End If
End If
End Sub

 

[jsnfrs]

Another thing,

I am creating an application for use by a few clients of the firm I work at, at the moment I testing to see how the finished product will behave and look like to the clients:

I made the file into an accde then packaged it with the extension in access that allows you to creating the installable version.

I got through the first phase successfully but when after installation and at the stage where the client will open the database on a regular basis to make update and add new items etc. I am being faced with a read only message the the user the ability to save the database separately, which will give them access to all the objects of the database, allows them to view macros, query calculations, etc.

Kindly advise me how I can get rid of that read only pop up and make my database solution more secure.

Thanks alot.

 

[jsnfrs]

This is what the message looks like

 

[Brianwarnock]

Not a problem man,

The first sub is the code I got from you guys and tried to get it working but ended up with the error

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.asset id = Nz(DMax("asset id", "tbl_assetsregister"), 0) + 1
End If
End Sub

Is that a space in me.asset id

Brian

 

[jsnfrs]

Yes, the actual field name has a space. When I remove the space I am returned with another error "method or data member not found" or something similar.

 

[missinglinq]

Removing the Space would have to be done everywhere in the Database, including from the Field Name in the Table itself. You can leave the Space in the Field Name, but in all VBA code you'll have to enclose the name in Square Brackets:

Me.[asset id]

to tell the Access Gnomes that asset and id are two parts of a single name.

Linq ;0)>

 

[jsnfrs]

Thanks alot missinglinq, it worked.

Kindly assist me with limiting the record entry to no more than 5 for the database as well, I am apparently getting something wrong with this code as well, it worked once and just stopped after.

Regards

 

[jsnfrs]

This is the code I am using:

Private Sub Form_Current()
Dim intMaxNumRecs as Integer

intMaxNumRecs = 5 'Max Number of Records to Allow

If Me.NewRecord Then
With Me.RecordsetClone
If .RecordCount > 0 Then
.MoveLast: .MoveFirst
If .RecordCount >= intMaxNumRecs Then
MsgBox "Can't add more than " & intMaxNumRecs & " records in the demo database!"
.MoveLast
Me.Bookmark = .Bookmark
End If
End If
End With
End If
End Sub