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.
"Jeff Boyce" wrote:
> 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
>
> "RyanJLH" <(E-Mail Removed)> wrote in message
> news:EE3CEDBC-E7ED-4732-95E1-(E-Mail Removed)...
> >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
>
>
>
|
Similar Threads
