Hi David,
adding on to John's excellent advice ...
>> "create code that might unknowingly produce erroneous related child records"
while you are testing, you may want to set relationships to be:
- Enforce Referential Integrity (always, unless you have reason not to)
- Cascade Delete Related Record (only do this, as a general rule, temporarily) -- then when you delete your test records, the related records will go bye-bye too
'~~~~~~~~~~~~
re: Last vs Max
data can be sorted ... the last record 'showing' is not necessarily the latest one added. It can be ... but, as John said, better to use dMax for AutoNumbers if you are getting the highest autonumber saved
To track when records were added, you can include this field in all your tables:
-dtmAdd, Date/Time, DefaultValue =Now(), Description = Date/time record was added
'~~~~~~~~~~~~
>> "And could instead be inserted into the deck to take the place of an older card that has been removed and thrown away?"
perhaps you also want to add:
-dtmEdit, Date/Time, DefaultValue =Now(), Description = Date/time record was edited ... update with code on form BeforeUpdate event or when update query runs. Then, perhaps instead of the Max autonumber, you want to get the max value for dtmEdit
WHERE:
db is a database object variable such as set to CurrentDb
'~~~~~~~~~~~~
instead of using CurrentDb.Execute, multiple times, it is better to DIMension a database object variable (ie: dim db as dao.database) and then set it (ie: set db =CurrentDb)
to ensure that the code truly sees the latest record, it might be good to then db.Tabledefs.Refresh (see changed made by other users and processes) and DoEvents (do it now) for latest changes to be seen
'~~~~~~~~~~~~
if you want to capture the autonumber just added, perhaps it is better to use a recordset instead of SQL.
>> Recordset <<
this gives you a way to programmatically open a recordset that is defined by a table name, query name, or SQL statement -- I usually use the latter. You can add and edit records, or find a record(s). After adding in Access (which CAN capture autoNumber value before save), to be comatible with SQL Server and other platforms the data might be migrated to in the future, which don't assign autonumber until record is saved,
.~~~~~~~~~~~~~~~~~~~
dim nMyIDName as long _
,sSQL as string
dim db as dao.database _
, rs as dao.recordset
set db = CurrentDb
sSQL = "SELECT blah blah"
set rs = db.openrecordset(sSQL, dbOpenDynaset) 'dbOpenDynaset means you can add and edit if definition allows it
with rs
.AddNew
'statements, for instance:
'!MyTextFieldname = """" & myValueString & """"
'!MyNumberFieldname = myValueNumber
.Update
.Bookmark = .LastModified
nMyIDName = !MyAutoNumberFieldname
end with 'rs
'close and release object variables
rs.close
set rs = nothing
set db = nothing
'~~~~~~~~~~~~~~~~~~~~
respectfully,
crystal
~ have an awesome day ~
David-
In summary:
DLast returns the value from the last physical record. If records have been deleted (leaving "blank" spaces in the middle), Access will re-use that empty space for new records, so the latest record added (with the highest AutoNumber) might not be at the last physical position.
DMax returns the highest or largest value. When you do DMax on an AutoNumber (Increment) field, you will get the value of the last record added, regardless of its physical position in the database.
John Viescas, AuthorEffective SQLSQL Queries for Mere MortalsMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access Applications(Paris, France)
On Apr 28, 2017, at 6:14 PM, david.pratt@outlook.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
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, AuthorEffective SQLSQL Queries for Mere MortalsMicrosoft Access 2010 Inside OutMicrosoft Access 2007 Inside OutMicrosoft Access 2003 Inside OutBuilding Microsoft Access Applications(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, authorEffective SQLSQL Queries for Mere MortalsMicrosoft Office Access 2010 Inside OutMicrosoft Office Access 2007 Inside OutBuilding 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, authorEffective SQLSQL Queries for Mere MortalsMicrosoft Office Access 2010 Inside OutMicrosoft Office Access 2007 Inside OutBuilding 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 ErrHandlerDim 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 SubErrHandler:
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.DescriptionResume DoneEnd Sub
Posted by: crystal 8 <strive4peace2008@yahoo.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (9) |
Tidak ada komentar:
Posting Komentar