How to copy one record into the new record?

[VPham]

Sometime a new record being input has partial information as a previous record.
I want to filter my database to find the previous record.
Then I want to copy those partial information into my new record.

How can I do this? If you have an example sequence, I would appreciate a
view. Thank you so much.

VPham

 

[Michel Walsh]

There is no 'previous' record, in a table. In a table, the records are like
beans in a pouch filled with beans.


If you edit the data through a FORM, rather than directly in a table view,
you can set the default value of the fields in the After Update subroutine
event handler:


Private Sub Form_AfterUpdate()
Me.ControlForFieldOne.DefalutValue = """" &
Me.ControlForFieldOne.Value & """"
Me.ControlForFieldTwo.DefalutValue = """" &
Me.ControlForFieldTwo.Value & """"
...
End Sub


and thus, the last record saved will supply values for the next NEW created
record.


That won't solve the problem with existing data, but can help for data which
will be added in the future.


Also, if a field CANNOT be left without value, you could have tell it to the
database, in the table design, forcing the field to NOT accept NULLs. With
that condition in place, you would not be able to SAVE a record where the
mentioned field would be without value.



Hoping it may help,
Vanderghast, Access MVP

 

[VPham]

Thank you for your information.

Please clarify.
If a table is a pouch filled with beans, isn't it true that the beans are
actually in order by a primary key? So when I open a Form to put in the next
bean (#65). Let's say that bean #42 somewhat resemble the #65; can't I make
a duplicate of #42 to #65 instead of making a duplicate of #64?

Thanks,

VPham

 

[Michel Walsh]

Yes, they are ordered by primary key order (for Jet, while for MS SQL Server
it depends on the cluster index) *when* there is a pk, in the PAGES (I/O
elements of the database) that hold them, but relevant pages are not
necessary in order on the hard disk (ie, to read in order, someone may have
to read page1, page55, page56, page2, ...) . When they are pumped back for
presentation (form, table data view, whatever), they are read, sequentially,
but SQL does not necessary pre-pump back the (whole) data... So, when SQL is
at work, there is no actual previous record ***for sure***, or it may not be
as expected as if it was strictly in order by the pk.

If you open a recordset (kind of double link list of 'bookmarks" pointers to
each record), you then have a sure 'next' or 'previous' record. But
recordsets exist in VBA, not in Jet-SQL.SO, a possible solution, in your
case, would be to loop through your records, one at a time, in VBA, and
then, either move_previous as required, to read the data you need, and then,
move_next to update the data, and finally, continue your loop.


Vanderghast, Access MVP