Copying the last record to a new record

  • Thread starter Thread starter Bigbob
  • Start date Start date
B

Bigbob

I'm trying to copy the last record of a table to the new record. Can't find a
macro command to do it with.
 
Records stored in a table are like bricks in a barrel - Access does not know
the last one in unless you tag it.
You need a query that includes the tag as criteria.
 
In addition to Karl's observation about how Access treats a table as a
bucket o' data, I'm wondering why you want to have two records that match
("copy the last records ... to the new record").

If your tables are designed such that you can have (or feel you need to
copy) one record into another, you may be treating Access as if it were a
spreadsheet. You won't get very easy use of the relationally-oriented
features/function Access offers if you insist on feeding it 'sheet data.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
The issue is that I have a large number of fields (~30), and only one
parameter may be change. In order for us not fill in all fileds but see all
current data every time we open the particular form.

I lock down the records on save and I have it to the point where it displays
the last record. Now when the new records button is clicked all fields go to
0 and one would have to repopulate all fields (boss' requirement).

Will a query populate the same table?

Thanks so much for the help.
 
Bosses can set the requirements ("What I need to be able to do") but rarely
understand the underlying system well enough to know "HOW" it needs to be
done.

If you're using Microsoft Access (or some other relational database), but
you're treating it as if it were a spreadsheet, why not just use a
spreadsheet?

First, a large number of fields (~30) suggest that your table may not be
well-normalized. Post back a description of the fields and their contents
for feedback from these newsgroups.

Next, if "only one parameter may ... change", then there's even a better
chance that your table is not well-normalized.

So what, you ask? Back to my original response -- if your data is
structured in a way Access can readily use, both you and Access will have to
work extra hard to do simple things.

I'll point out that, unlike Excel, your data in Access DOES NOT have to be
structured like you were going to send it straight out for a report. That's
what queries are for.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
It's mainly numbers to 3 decimal points with date/time stamps for tracking.
The reason I went with access is the huge amount of data, approx. 1000 data
points we plan to track changes on. My reasoning was that a database will be
able to handle the large quantity of data faster than excel. Additionally we
wanted to make it as easy and fast as possible for techs to enter their data.
 
Hi Bob,

From your discussion, I guess the last record would be designated as the one
with the biggest/newest timestamp? Assuming this is true for the moment,
then the last record could be isolated using that information -

SELECT TOP 1 * FROM [mytable] ORDER BY [mytimestamp] DESC;

This should give you a recordset containing one row of data - the most
recent record.

Not sure (without using VBA) the best way to proceed at this point.

We could append this recordset (minus the primary key and substituting Now()
as mytimestamp) to the database, sort the record source by mytimestamp
desc(ending) and move to the first record - that would get us to a copy of
the last record as our new record.

Personally, I'd probably use VBA & DAO to set a form's unbound controls to
the values contained in the last record, let the user edit what they wished
and click a Save New Record button to insert their new record into the
database.

Hope this helps,
Gordon
 
Thanks Gordon,
Almost there I think.

I have a macro that sets the time/date upon save, another macro uses the
GoTo to display the last record by the primary key.

Now I wanted to just copy that data to the new record. I'm not against using
VBA but as I am new to it I would need an example of the code required to
copy that record as the new record. Preferably when the user clicks the new
record button that shows on the bottom of the form. But I could make a new
button for new record (onClick copy recordLast to record new)

Thank you very much.
Bob

gllincoln said:
Hi Bob,

From your discussion, I guess the last record would be designated as the one
with the biggest/newest timestamp? Assuming this is true for the moment,
then the last record could be isolated using that information -

SELECT TOP 1 * FROM [mytable] ORDER BY [mytimestamp] DESC;

This should give you a recordset containing one row of data - the most
recent record.

Not sure (without using VBA) the best way to proceed at this point.

We could append this recordset (minus the primary key and substituting Now()
as mytimestamp) to the database, sort the record source by mytimestamp
desc(ending) and move to the first record - that would get us to a copy of
the last record as our new record.

Personally, I'd probably use VBA & DAO to set a form's unbound controls to
the values contained in the last record, let the user edit what they wished
and click a Save New Record button to insert their new record into the
database.

Hope this helps,
Gordon



bigbob said:
It's mainly numbers to 3 decimal points with date/time stamps for
tracking.
The reason I went with access is the huge amount of data, approx. 1000
data
points we plan to track changes on. My reasoning was that a database will
be
able to handle the large quantity of data faster than excel. Additionally
we
wanted to make it as easy and fast as possible for techs to enter their
data.
Click to expand...
Click to expand...
 
Back
Top