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
(Paris, France)
On Apr 28, 2017, at 4:39 PM, david.pratt@outlook.com [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
---In MS_Access_Professionals@yahoogroups.com, <david.pratt@...> wrote :
---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :
Done:
On Error GoTo 0 'reset error handling to VBA default
Exit Sub
Effective SQL
SQL Queries for Mere Mortals
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Access Applications
---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
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
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
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
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
ErrorLog "Form_frmConfigureSpecDetails_cmdCopySpec_Click", Err.Number, Err.Description
Resume Done
End Sub
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (6) |
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.
.
__,_._,___
Tidak ada komentar:
Posting Komentar