need something like foreach record in table - add record to another table
Hi Randy,
I think that when you pasted the code, an extra linebreak was added
(thanks to OE) - then VBA helpfully added some double quotes where you
don't need them. I should have made the lines shorter so this wouldn't
happen. However, in reading your reply I think you should probably do
some work on the table design before you proceed since your data does
not appear to be normalized - I think you need a third table,
AgentActivity. The Activity table should be revised so that there is
one row per activity, prefereably with a numeric field, ActivityID
that you can use as a foreign key when linking to this table. The
table AgentActivity then becomes a representation of a Many to Many
relationship between agents and activities (One agent can have many
activities and one activity can exist for many agents.)
Here is the revised table structure:
Agent
AgentID,
AgentName,
AgentAddress,
AgentCity,
AgentState,
AgentZip
Activity
ActivityID
ActivityType
ActivityDesc
ActivityDetails
AgentActivity
Agentid
ActivityID
ActivityDueDate - Assuming the due date is specific to the Agent.
Before you can add an activity to each agent, you first need to add
the activity to the Activity Table. Then, it's pretty easy to add an
AgentActivity record to setup that activity for each agent.
Set db = CurrentDb()
' the # characters around the date value are required to let Access
know that the
' value is a date/time
strSQL = "INSERT INTO AgentActivity ( agentid,
Activityid,Activityduedate ) " _
& "SELECT Agent.AgentId, " _
& me.ActivityID & " AS ActivityID, " _
& "#" & me.dueDate & "# As dueDate " & "FROM Agent;" _
--
Sandra Daigle, Microsoft Access MVP
Quote:
> Hi Sandra,
> I tried to get this working but I think there is much that
> I don't know.
> I created a form for the manager to fill in the field
> values to be created - ActivityType, ActivityDesc,
> ActivityDetails, and ActivityDueDate. I made it a data
> entry form with a button that executes the code from a
> macro.
> I created a new module with the function in it using
> modified code from your last post, which I could only
> partially figure out. I was unsure what the &
> Me.cboActivityID & " AS Expr1" code did so I left it alone
> hoping to get a helpful error message when it ran - I
> did'nt get one. Nothing happens except that I get a
> compile error.
> Do you have more help for me?
> My new function;
> Public Function AddActivityToAll()
> Dim strSQL As String
> Dim db As DAO.Database
> Set db = CurrentDb()
> strSQL = "INSERT INTO Activities ( ActivityType,
> ActivityDesc, ActivityDetails, ActivityDueDate) SELECT"
> Agent.AgentId, " & Me.cboActivityID & " AS Expr1" & "FROM
> Agent;"
> db.Execute strSQL
> Set db = Nothing
> End Function
> Just in case it's not clear - table structures are
> Activities table
> AgentID, ActivityDesc, ActivityDetails, ActivityDueDate
> Agent (customers really)
> AgentID, AgentName, AgentAddress, AgentCity, AgentState,
> AgentZip
> AgentID is an autonumber field in Agents table and number
> type in Activities
> >-----Original Message-----
> >A simple append query will do this for you -
> >dim strSQL as string
> >dim db as dao.Database
> >set db=currendb()
> >strSQL ="INSERT INTO custactivity ( Custid, Activity )
> SELECT
> >Customer.CustId, " & Me.cboActivityID & " AS Expr1" _
> > & "FROM Customer;"
> >db.execute strsql
> >set db=nothing
> >--
> >Sandra Daigle, Microsoft Access MVP
> >> Hi all,
> >> This probably seems like a simple thing to do for most
> of
> >> you, but I'm programming challenged so I was hoping for
> a
> >> little help.
> >> I need a piece of code that will execute (probably from
> a
> >> form) to add a new record in a table for each record of
> >> another table.
> >> Here is the deal. I have a database of activities and
> >> customers. The sales manager would like to be able to
> >> easily add an activity for all of the customers by
> filling
> >> in one form.
> >> So if the manager would like all of the salespersons to
> >> see an activity like "Send Christmas card" for all of
> >> their customers, they could fill in an activity form and
> >> click the button "add to all customers"
> >> or
> >> fill in an "Activities for all" form and the onclose
> >> action would create a new record into the activities
> >> table, for each customer listed in the customers table.
> >> Thanks a ton & Happy Holidays,
> >> Randy
> >.
