DMax Function Not Responding on Pasted Records

[Mr Key]

Hi all!
I set ID field to Dmax function to generate the next record number at the BeforeInsert of the MainForm(on datasheetView). It Works fine if the records is entered one by one, but when you paste the Records at once, you endup with similar ID at the ID field (not incremented as expected).
My users are always looking for shortcuts to paste records from somewhere!
Here is the Code

Me.[ID] = Nz(DMax("[ID]", "[MytableName]"), 0) + 1
 

Please help!!!

[NeoPa]

I would guess that the addition of all the records together is treated as a single transaction. That is to say that none of the additions of those records will be visible until all of them have been added and the transaction committed.

You will need to handle this in your code. Something like the following may work :

Private Sub Form_BeforeInsert(Cancel As Integer)
    Static lngLastID As Long
    Dim lngID As Long
 
    lngID = Nz(DMax("[ID]", "[MytableName]"), 0)
    If lngID > lngLastID Then lngLastID = lngID
    lngLastID = lngLastID + 1
    Me.ID = lngLastID
End Sub

[Mr Key]

I have tried to paste the code but it gives ODD Value Counting
1 3 5 7 9 ....
and not normal Counting as 1 2 3 4 5 6 .....

[NeoPa]

Very strange. I tested it out on a system I knocked up and not only did I notice that the BeforeInsert event for the form was triggered again after the vale of Me.ID was set in the code (thus causing the double increment), but also that the value of ID was never actually changed either on the form or in the record.

With a minor change however, I was able to produce a routine that did what was required :

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Static lngLastID As Long
    Dim lngID As Long
 
    If Not Me.NewRecord Then Exit Sub
    lngID = Nz(DMax("[ID]", "[MytableName]"), 0)
    If lngID > lngLastID Then lngLastID = lngID
    lngLastID = lngLastID + 1
    Me.ID = lngLastID
End Sub

[Mr Key]

I have tried but it couldnt work either, instead I have this code below to determine the number of records in a table that can be displayed in a form. In this code You just Set controlsource of unbound Text box to'= GetLineNumberForm([Form]).
But I couldnt save this number as my ID field in a table.

'********************************************
'Only USE GetLineNumberForm  and   Serialize
'********************************************
 
 
'Stephen Lebans with help from Chris Bergmans
' Updated by Allen
'Production version of GetLineNumberForm
'Works in Form or SubForm mode
'Set controlsource of unbound Text box to
'= GetLineNumberForm([Form])
'Type exactly as above
 
Function GetLineNumberForm(f As Form)
    Dim rs As Recordset
    Dim frmMain As Form
    Dim frmCur As Form
    Dim strName As String
    Dim IsItSubForm As Boolean
    Dim strFName As String
 
    'is the form referenced in the parameter currently
    'loaded as a SubForm?
    'Check it parent property to find out.
 
    On Error Resume Next
    strFName = f.Parent.NAME
    IsItSubForm = (Err = 0)
 
    'Point to our error handler
    On Error GoTo Err_GetLineNumber
 
    If IsItSubForm Then
        'its a SubForm
        Set frmMain = f.Parent.Form
        strName = f.NAME
        Set frmCur = frmMain(strName).Form
        Set rs = frmCur.RecordsetClone
 
    Else
        'It's not a SubForm
        Set rs = f.RecordsetClone
        Set frmCur = f
    End If
 
        ' Find the current record.
        rs.Bookmark = frmCur.Bookmark
        GetLineNumberForm = rs.AbsolutePosition + 1
 
Bye_GetLineNumber:
       Set rs = Nothing
       Set frmMain = Nothing
       Set frmCur = Nothing
       Exit Function
 
Err_GetLineNumber:
       Resume Bye_GetLineNumber
 
End Function
 

How can I save this numbers on a my table?

[NeoPa]

I never suggested you should. It looks like a thoroughly overcomplicated solution.

If you're interested in my help then you might like to explain what went wrong when you tried the code I suggested in post #4. It worked fine when I tested it so I'm confused as to what you could be doing that would cause it to fail. Certainly nothing you've told us about so far would explain it.

[Mr Key]

Sorry, I dint explain how!!
First: Am using access 2010
Second: When you paste the record on the forms acDataSheetView, it count the same sequence, ODD values, 1, 3 , 5....
Thirdly: When you paste again new record, it leaves a blank line and then starts count again in the same sequece, 7,9,11...
May be something have to be done before I paste the code!!!!

[NeoPa]

That's not what happened when I tested it :-S

Maybe I need to see your database. Unfortunately, I'm just going away for over a week now. I'll look at it for you when I get back though if you post a copy of your database. Make sure you follow the instructions in Attach Database (or other work) though. I still use Access 2003 on my main machine. I'll try to remember to dig this thread up when I get back on Saturday of next week.

[Mr Key]

Ok! Thanks for your suggestion
Here is my copy of mydatabase,
I have set the table to be MytableName and the field to be incremented to be simple ID. At the afterInsert event of a form1 set to EventProcedure which have the following code

Private Sub Form_BeforeInsert(Cancel As Integer)
    Static lngLastID As Long
    Dim lngID As Long
 
    If Not Me.NewRecord Then Exit Sub
    lngID = Nz(DMax("[ID]", "[MytableName]"), 0)
    If lngID > lngLastID Then lngLastID = lngID
    lngLastID = lngLastID + 1
    Me.ID = lngLastID
End Sub
 

Yet if you paste the record, it gives very strange results, just have a look at it!!

[NeoPa]

It doesn't appear that you have used the code I suggested. My code in post #4 handles the form's BeforeUpdate event, while yours is still using the BeforeInsert event (which, if you remember, we discovered does exactly what you're complaining of). Try my latest suggestion and we can move onwards from there.

[Mr Key]

Thanks Sir for your help, finally it works. I dint recognize the change from BeforeInsert to BeforeUpdate
Thanks again!
I can now move to another problem of AfterInsert Event Not Triggered as Expected on Pasted Records
Thanks all for your contributed in this post!!!

[NeoPa]

You're welcome Mr Key.

I should point out though, that the last post in the other thread is four weeks old and requires more from you before anyone is likely to post anything further there.