Create New Records in a Table by Copying Existing Records

[Guest]

I am trying to automate producing a set of records in a schedule. I have a
table called "HouseSchedule" with multiple task records in it. Each house
has a set of records in this table and there is a "Master" set of records. I
am using a form with a couple of drop down buttons so that a user can select
a "Master" set of records from the table, copy those records, place them back
in the table and change the HouseID field from "Master" to "HouseNumberX".

I have thought of a number of ways to do this including Append Query or VBA
code, however I have had very little success. I can create a query and/or a
form that selects the "Master" set of records; however, I have not yet been
able to overwrite the HouseID field to the new house's "HouseNumber".

1. What is the best approach? Append Query or Code?
2. The code I came up with is below, but I constantly get stuck at the Do
Loop and inside when trying to set the field value to "New Address".

Private Sub CopyScheduleButton_Click()
'This procedure will copy a set of schedule records (typically a master)
'Add them to the schedule and change the address of each record to that
'of the one on the Schedule Form "Select Address" drop down
Dim NewAddress As String

NewAddress = Forms!Schedule!ComboAddressSelect.Value
DoCmd.OpenForm "CopyScheduleQuery", acFormDS ' use a query or a form based
on a query?
Recordset.MoveFirst
Do While Recordset.EOF = False 'Program Crashes here
Recordset.Address.Value = NewAddress 'Program also crashes here when
removed from loop
Recordset.MoveNext
Loop

'Copy records to Schedule Table
'?Unsure how to move these into Schedule table

'Requery Schedule
DoCmd.RunMacro ("RequerySchedule")
End Sub

 

[Tom Ellison]

Dear Ryan:

The query approach would be easier to write and would run faster. It would
also probably be easier to maintain.

Write a query that returns all the columns in the table specifying each one
(not with *, but by name, and in the sequence they appear in the table).

Add a filter so you see only those rows for "Master".

Change the HouseID column in this to "HouseNumberX"

Run the query and see that it produces what you want to have added.

Change it to an append query.

That should do it. Any problems or questions?

Tom Ellison

 

[Jeff Boyce]

Ryan

Are you saying that you are trying to create a series of "empty" records?
If so, why? It's rarely necessary to create placeholder/empty records in a
well-normalized relational database design.

Can you provide further description of what you are trying to accomplish?
You've already described how you are trying to do something, but I'm not
clear on the "what"...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff,

The "Master" records would not be empty, but would contain a list of
scheduled tasks that must be done for each house. This way the user would
not have to create 100 individual records describing each task that must be
performed for the new home. In each of these records, the only thing that
would be blank would be the "Date" field so that all the user would have to
do is enter in the dates for each of the tasks.

I read the previous answer which gives an answer using an append query, but
the users I am writing this for are not acces savy. Having them replace
values and change the query would be problematic. I would need to figure out
the code to automate that suggestion. I am still looking for a way to
accomplish the record replication and field replacemnt inside of a private
sub.