Using one form to create mulitple records

  • Thread starter Thread starter Bradford
  • Start date Start date
B

Bradford

How do I create a form that will create multiple records from one view of the
form? I want to use a single form to input activities for a given day. For
example - I have three activity categories: Planning, Survey, and Design. On
one form I want input fields for: Date, Staff Member Name, Planning, Survey,
and Design. So, lets say on a given day I perform three activites. On this
one form I input: Date: "10/28/2009"; Staff Member Name: "Joe Jones";
Planning: "Smith layout"; Survey: "Peters site survey"; Design: "Jackson pond
design". From this entry on one form, three activtiy records are created:
Activity ID 01: "10/28/2009; Joe Jones; Planning - Smith Layout"
Activity ID 02: "10/28/2009; Joe Jones; Survey - Petes site survey"
Activity ID 03: "10/28/2009; Joe Jones; Design - Jackson pond design"

How can I do this? Thanks!
 
Create a table named CountNumber with number field - long integer named
CountNUM with numbers 1, 2, and 3.
UNTESTED UNTESTED
INSERT INTO YourTable ( ActivityID, ActivityDate, [Staff_Member_Name],
Activity )
SELECT CountNUM AS ActivityID, [Forms]![YourFormName]![ActivityDate],
[Forms]![YourFormName]![Staff_Member_Name], IIF([CountNUM]=1,
[Forms]![YourFormName]![Planning], IIF([CountNUM]=2 AND
[Forms]![YourFormName]![Survey], [Forms]![YourFormName]![Design])) AS
Activity
FROM CountNumber
WHERE (CountNumber.CountNUM<= 3);
 
Back
Top