Re: [MS_AccessPros] DLast : Random or Last?

 

John,

I saw another thread where it was said that DMax with an autonumber field may not be reliable since Autonumber could not be relied upon to always use a sequential value.

I don't know how that can happen since I thought Autonumber never re-used an ID number and would always increment the number value by one.

I am just checking to verify.  I don't want to create code that might unknowingly produce erroneous related child records.

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

David-

True, DLast returns a "random" record because it doesn't return the oldest value but rather the value from the last physical record stored in the database.  If you have never deleted records from the table, it is probably the last value inserted, but if you have deleted records at any time, the new record might have been saved at a location other than "last", so you won't get what you want.

Because you're not including SpecNameID in the INSERT statement, I assume it's an AutoNumber.  In that case, use DMax to get the highest value - which should be the value of the last record inserted.

John Viescas, author

Effective SQL

SQL Queries for Mere Mortals

Microsoft Office Access 2010 Inside Out

Microsoft Office Access 2007 Inside Out

Building Access Applications

http://www.viescas.com/

On Apr 28, 2017, at 00:54, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have the following code attached to a COPY  command button.  The purpose is to copy a parent record from one list box and the related child records from a second list box.  After copying the parent record, I get (I think) the record ID I just created by using the DLast function on the parent table.  I then use that ID field to insert into the newly created child records.

It works as far as I have tested so far.  I am concerned because MSDN and Office.com both say DLast and DFirst return random records.  TechOnTheNet says DLast returns the last record.  For me it appears to be returning the last record so far,  in the limited testing I have done.  But I don't know if I can trust it or not. If DLast and DFirst just return random numbers, why do they exist?  Why not just have DRandom?

Here is what I have written.  If there is a more reliable method I will appreciate your advise.  Comments on any portion of the procedure are appreciated.

Private Sub cmdCopySpec_Click()

    On Error GoTo ErrHandler

    Dim strSQL As String
    Dim lngSpecNameID As Long
    Dim lngNewSpecNameID As Long
   
'call custom function SaveNow() which checks for errors and saves any dirty record if error free
'if there is an error, SaveNow() logs the error message; if error, exit the Sub
    If Not SaveNow() Then Exit Sub
   
'verify that a Spec Name has been selected
    If Me.lstSpecNames.ItemsSelected.Count = 0 Then
        MsgBox "You must select a Specification Name to copy.", vbOKOnly, "No Spec Name Selected"
        Me.lstSpecNames.SetFocus
    Else
        If Me.lstSpecDetails.ListCount = 0 Then
            MsgBox "There are no spec details to copy.", vbRetryCancel, "No Details to Copy"
            Me.lstSpecNames.SetFocus
            Exit Sub
        End If
        MsgBox "Proceeding to copy data now."
       
        'duplicate the SpecName record but with new name
'
            lngSpecNameID = lstSpecNames.Value
            Debug.Print "lngSpecNameID = " & lngSpecNameID
           
            strSQL = "INSERT INTO [tblSpecNames] (SpecName, LocationID, ApplicationGroupID)" & _
            "SELECT  '_copy'&SpecName, LocationID, ApplicationGroupID " & _
            "FROM [tblSpecNames] WHERE SpecNameID = " & lngSpecNameID & ";"
            CurrentDb.Execute strSQL, dbFailOnError
         
            'Get the just created SpecNameID number as a new SpecNameID for use as the foreign key in the new records
            lngNewSpecNameID = DLast("SpecNameID", "tblSpecNames")
            Debug.Print "lngNewSpecNameID = " & lngNewSpecNameID
           
            strSQL = "INSERT INTO [tblSpecDetails] (SpecNameID, ParameterID, LIMSAnalysisID, " & _
                        "TargetValue, LLowLimit, LowLimit, HighLimit, HHighLimit, KPI, Priority)" & _
            "SELECT " & lngNewSpecNameID & " As SpecNameID, ParameterID, LIMSAnalysisID, TargetValue, " & _
                    "LLowLimit, LowLimit, HighLimit, HHighLimit, KPI, Priority " & _
            "FROM [tblSpecDetails] WHERE SpecNameID = " & lngSpecNameID & ";"
            CurrentDb.Execute strSQL, dbFailOnError
           
            Me.lstSpecNames.Requery
            Me.lstSpecDetails.Requery
    End If

Done:
    On Error GoTo 0    'reset error handling to VBA default
    Exit Sub

ErrHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & vbNewLine & vbNewLine & _
        "Error is in procedure cmdCopySpec_Click of Form_frmConfigureSpecDetails." & vbNewLine & vbNewLine & _
        "Error was automatically logged for programmer notification." & vbNewLine & vbNewLine & _
        "Try again or click Cancel to close without correction.", vbRetryCancel, gstrAppTitle

    'call the public sub ErrorLog to enter info into ErrorLog table; clears Err Object info with Resume statement
    ErrorLog "Form_frmConfigureSpecDetails_cmdCopySpec_Click", Err.Number, Err.Description

    Resume Done

End Sub

__._,_.___

---

Posted by: david.pratt@outlook.com

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___