Ok this is exactly what I was talking about, you do not have a unique key field that will help you identify the most recent record to copy data from. In essence any code you use is not necessarily going to give you the most recent record. You are lucky (based on this example) that the data is showing in the order you want. I would very, very, very strongly recommend you put in an autonumber field (at the very least) and use it as your primary key for the table [Running Conditions Log]. In the version of access you're using it should be as simple as adding a field named Log_ID with an autonumber property, when you open the table again all of your fields should be in the same order but now have a 'counter' to show you which is the most recent record.
So here's what I did
1. Put in an autonumber field and called it LOG_ID in your [Running Conditions Log]
2. On the form [Running Conditions Log Form] I put a TAG property on all your data entry fields of "DE"
3. Added a button with the label 'Copy Previous Record'
Added this code to the button
Code:
Dim ctl As Control
Dim lMaxRec As Long
Dim db As database
Dim rst As Recordset
Dim sSQL As String
Dim iCurrField
Set db = CurrentDb
lMaxRec = DMax("[Log_ID]", "[Running Conditions Log]") 'gets the most recent log_ID
Debug.Print lMaxRec
'This section of code builds a SQL statement to power the population of the data on the form
sSQL = "SELECT "
For Each ctl In Me.Controls
If ctl.Tag = "DE" Then
Debug.Print ctl.Name & " " & ctl.ControlSource 'if your form changes this will help you design the query to get fields in the correct order
sSQL = sSQL & "[" & ctl.ControlSource & "],"
End If
Next ctl
sSQL = Left(sSQL, Len(sSQL) - 1) & " FROM [Running Conditions Log] WHERE (Log_ID = " & lMaxRec & ")"
Set rst = db.OpenRecordset(sSQL) 'opens the sql statement as the recordset for populating data
rst.MoveFirst
iCurrField = 0
For Each ctl In Me.Controls
If ctl.Tag = "DE" Then
ctl.Value = rst.Fields(iCurrField)
iCurrField = iCurrField + 1
End If
Next ctl
rst.Close
Debug.Print sSQL
Set db = Nothing
A few notes here. You are going to have more and more problems as you progress with your development for a few reasons that you should correct now if you can.
1. Don't ever use spaces in your database object names, it causes no end of trouble when programming, for instance when I constructed the SQL statement to drive the copy I had to insert [] marks around each field name, if you had named your fields with no spaces I could have done away with that and made the code shorter.
2. Don't ever use special characters in object names (you've used ', #, (, ), and ? in the one table I looked at) ' is a text indicator, # is a date indicator etc. Please, please, please change your field names while you can!
3. Always use a primary key, access has an autonumber field for that very reason, even if you think you'll never use it, put it in, it will save you a ton of headaches.
Now for some other important notes. The method I'm using assumes you want to copy every single field from the previous record. If that's not the case you ONLY set the tag of the fields you want to copy to "DE" and remove the tag from the fields you do not want to copy. The code I gave you is intended to work dynamically, so in case your data fields change this code will continue to work without any maintenance. This code will ONLY work if you are pulling data from 1 table, if you start linking other tables the SQL statement builder will have to be modified to handle that. Additionally when you use the method I've used for cycling through controls on your form Access does not allow you to alter the order in which they are processed so you have to build your query to show fields in the same order the controls are ordered, I've left a debug.print statement in there to show you the name of the control and the control source of that control in the order the form cycles through them. You'll notice if you look at the result after you click the 'copy previous record' button they appear in the immediate window of your code box.
I also had to add a reference and change a reference (when you convert to a prior version access doesn't do it for you)
I'm using Microsoft Office 12.0 Object Library and Microsoft DAO 3.6 Object Library.
You'll have to find the replacement references in your version of access to accomodate this code.
Here's a copy of your database with a functioning 'copy button'
Fake Database-modified.zip
Windows 7 32bit 
Access 2010 32bit
Reply With Quote
Originally Posted by rpeare
