get max id

[iamresearcher]

ok. guys I have function for getting max id.



for example I have customers table with custid field.

Now, in a multi-user environment, 3 to 5 users might encode new customer record.

So for in my design, I dump the generated custid to a dumpcustid table so that it will be the basis of the custid currently used. Now what if one encoder generates new custid number 5 but later cancels the transaction and custid, 6,7,8 are on record now. so custid5 is not on the record since it was only generated but later cancel.

How can I use that custid number 5 if I am using getmaxid function? I want to make sure that all number series are there.

advice me guys what is the best approach for this.

[Missinglinq]

The usual approach for preventing duplicates in auto-incremented numbers, which is what you're really doing here, is to generate/assign the number in the Form_BeforeUpdate event. This is the last event to fire before a Record is saved, and if the Record is dumped, will not fire at all, and hence the number will not be assigned and therefore will not be 'missing' from the dumpcustid table.

Actually, doing this task in the Form_BeforeUpdate event makes using the dumpcustid table unnecessary. In over a decade of writing Access apps, using this technique, I've never had a problem with duplictate numbers being generated, and the same approach will prevent the 'orphan' numbers from being entered in the dumpcustid table, as well.

Linq ;0)>

[epb]

I have something similar....I have a record number that I want to auto generate. So when a user creates a new record, I want the record to automatically generate the number in YYYYMMDD-XX format. YYYY = Year, MM = Month, DD = Day, XX = The Nth record created that day.

What code do I put into the record handler to find out the XX or Nth record created that day. I would think Access could allow me to pull up the last record created and I could add check to see if they were created that day and increment by "1" the from number field for that record number. You have already said where the code should go.

I just don't know how to do that...any suggestions??

So I am looking for the code to pull the last created record and read the form number and then increment the last record on that day.

Thanks...

Note: I could just pull the mmss (minutes and seconds) and use that instead of XX but I want to see the code for finding the last record created, figuring out if the record was that day and then incrementing if it is.

[Missinglinq]

epb,

I think this does what you want; I've tested it over several 'days' by entering Records, changing the system date, and entering more Records. It comes with one caveat...I believe the Records have to be entered in chronological order, i.e enter Records for 7/28, 7/29, 7/30, etc.

If you enter Records for 7/28, 7/30 and then 7/29 I think it's going to cause Access to hiccup. If you need that capability, you can still use this as a start.

Of course, you'll have to replace RecordID with the actual name of your Field, and this Field has to be defined as a Text Datatype at the Table level.

Linq ;0)>

[epb]

Hello Missinlinq,

I am new to this so I might be missing something but I don't see the actual code.

[Missinglinq]

Well isn't that embarrassing? Sorry, 'She-who-must-be-obeyed' in just out of the hospital, and I've been playing Nancy Nurse all week, and do tend to lose focus, at time!

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
 
 
 If RecordsetClone.RecordCount = 0 Then
  Me.RecordID = Format(Date, "YYYYMMDD") & "-001"
 End If
 
 If RecordsetClone.RecordCount <> 0 Then
  If Left(DMax("RecordID", "YourActualTableName"), 8) = Format(Date, "YYYYMMDD") Then
    Me.RecordID = Format(Date, "YYYYMMDD") & "-" & Format(DMax("val(Right([RecordID],3))", "YourActualTableName", "Left([RecordID],8) = '" & Format(Date, "YYYYMMDD") & "'") + 1, "000")

  Else
    
    Me.RecordID = Format(Date, "YYYYMMDD") & "-001"
  End If
 
 End If

End If

End Sub

Now, this code is in the Form_BeforeUpdate event, which is the last Record-related event to fire before the Record is Saved, and is the necessary location for it if this app is being used in a Multi-user environment. This is needed in order to cut down on the possibility of two users getting the same number, and used in the Form_BeforeUpdate event, like this, I've never had a duplication problem in over a decade of coding. The down-side is that the generated number will not appear until the Record is Saved, usually by leaving the Record and returning to it.

If this is a Single-user environment, you could place the code in the Form_Current event, and it would appear immediately.

Linq ;0)>

[epb]

What is DMax??

[Missinglinq]

Access Help is not the best in the world, but for definitions of Functions and Commands, etc. it's usually adequate!

You can use the DMin and DMax functions to determine the minimum and maximum values in a specified set of records... Use the DMin and DMax functions in a Visual Basic for Applications module... a query expression, or a calculated control...

The 'specified set of records' means a Table or Query.

Linq ;0)>