Copy records from same table if it meets a criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all
I have a table with 10 columns and the primary key is "week" given by
number. I am wondering whether it is possible to code to copy the data from
one week to another week for certain fields (as only some of the fields
changes every week while the other fields remain the same). For example, if I
have values from 1 to 10 for field A for Week # 10, I would like to have it
copied to week # 11. It's really critical for my application to incorporate
this features as my company would deal with thousands of record every week
and it's really important for me to reduce the user work load wherever
possible. Do you guys have any ideas to accomplish this? Thanks...
 
vcsphx said:
I have a table with 10 columns and the primary key is "week" given by
number. I am wondering whether it is possible to code to copy the data from
one week to another week for certain fields (as only some of the fields
changes every week while the other fields remain the same). For example, if I
have values from 1 to 10 for field A for Week # 10, I would like to have it
copied to week # 11. It's really critical for my application to incorporate
this features as my company would deal with thousands of record every week
and it's really important for me to reduce the user work load wherever
possible. Do you guys have any ideas to accomplish this? Thanks...
Click to expand...

You can not do that in a table. You could use a query to
insert a new record, but you really should be using a form
to view/edit the records.

You can use a form's Load event to set the DefaultValue
properties of the text boxes bound to the fields you want to
duplicate onto a new record:

With Me.Recordset
If .RecordCount > 0 Then
.MoveLast
Me.txtboxA.DefaultValue = !fieldA
Me.txtboxB.DefaultValue = !fieldB
. . .
End If
End With

This will have no effect on the table until a user moves to
the new record and starts to enter a value in any control.
 
Thanks, Marshall for the response. With the loading procedure you have
suggested, I would be able to get only one previous record...But my
application demands to duplicate around 200 to 300 records every week. Is
there any other means we can achieve that using VBA coding? Thanks...
 
If you want to duplicate all the records from one week to
another week, you can execute an append query:

Dim db As Database
Dim strSQL As String
Set db = CurrentDb()

strSQL = "INSERT INTO table (week, fa, fb, ...) " _
& "SELECT [new week number] As week, " _
& "fa, fb, ... " _
& "FROM table " _
& "WHERE week = [old week number]"
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected & "records were added"

Replace the [new week number] and [old week number] with
the values you want (probably using text boxes on a form).
 
Works great! Thanks a ton!!!!

Marshall Barton said:
If you want to duplicate all the records from one week to
another week, you can execute an append query:

Dim db As Database
Dim strSQL As String
Set db = CurrentDb()

strSQL = "INSERT INTO table (week, fa, fb, ...) " _
& "SELECT [new week number] As week, " _
& "fa, fb, ... " _
& "FROM table " _
& "WHERE week = [old week number]"
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected & "records were added"

Replace the [new week number] and [old week number] with
the values you want (probably using text boxes on a form).
--
Marsh
MVP [MS Access]

Thanks, Marshall for the response. With the loading procedure you have
suggested, I would be able to get only one previous record...But my
application demands to duplicate around 200 to 300 records every week. Is
there any other means we can achieve that using VBA coding? Thanks...
Click to expand...
Click to expand...
 
Back
Top