Re: [MS_AccessPros] DLast : Random or Last?

 

can you look at my last question in my previous post about DMax please?  Is my analogy of the deck of cards accurate?

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

It's actually the New Value property - default is Increment, but you can also set to Random.  Increment is what you want.

John Viescas, Author

Effective SQL

SQL Queries for Mere Mortals 

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications 

http://www.viescas.com/ 

(Paris, France)

On Apr 28, 2017, at 4:39 PM, david.pratt@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thank you.  And I apologize for these questions as I try to fill in the gaps in my knowledge.

"Assuming NextValue is not random"...

Does this mean there is some property for an Autonumber field where a random number can be generated rather than a sequential number?  I have assumed all of my Autonumber fields are sequential.  I did not intentionally set any NextValue property.

I am going to have to take a little time to absorb the .AddNew discussion.  I assume that means using RecordSets, or clones and connections, etc.  None of which I have attempted yet.  but I need to learn.

In your first response you said DMax does not return the value from the oldest/highest, rather the value from the "last physical record".  I was not aware there was a difference.  what I am envisioning now, is that records are like a deck of cards with an auto sequence number added to each card when it is created.  The number on the new card is always the highest number in the card stack.  But the card is not always placed on the top of the deck?  And could instead be inserted into the deck to take the place of an older card that has been removed and thrown away?  And that DLast returns the autonumber value of the card on top of the deck, but DMax returns the highest value in the autonumber field from all of the cards?

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

David-

As long as Next Value is not Random, using DMax should work just fine with an AutoNumber.  Note, however, that if this is a multi-user system and two users do the same thing at the same instant, there's a tiny tiny possibility that you'll get the wrong number.  The one way to ensure that doesn't happen is to use .AddNew, set any one of the field values (which will populate the AutoNumber), then save the value before you finish setting all the fields and do .Update.

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/

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

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 (7)

---

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

• New Members 2

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___