Auto Populate

[dbprogman]

Hi All

I am currently constructing a DB for a client and their request has left me scratching my head a little so any help is gratefully appreciated...

We have a form that is connected to a projects table and a user would choose a project from a combobox and then a subform would display related works info. However one field in the works subform/table is a description of the work to be performed. The entire procedure currently works well with the description field being a dropdown as there are only ten (10) choices which would not change. The client has requested that once a user has chosen the desired project and the subform displays the relevent info they want the desriprtion field to be automatically populated with the ten (10) choices to ensure the user doesn't forget to add them all. eg auto create ten records.

1. Is it possible to create a button control that when pressed it creates ten records for that project one for each of the ten choices?

2. If not what would be the alternatives?

Sorry for the headaches Mum always told me to share...

Cheers
dbprogman...

 

[DCrake]

Are the ten descriptions a simple list of items/tasks Eg

Put key in lock
turn key to right
pulll door
etc.

If so you should have a table that holds these tasks and have a primary key for each description.

Then on you click of the command button

Dim Rs1 As DAO.Recrodset
Dim Rs2 As DAO.Recrodset

Set Rs1 = "TblProjectTasks"
Set Rs2 = "TblTasks"

Do Until Rs2.EOF
   Rs1.AddNew
   Rs1("ProjectID") = ID
   Rs1(TaskID") = Rs2("ID")
   Rs1.Update
   Rs2.MoveNext
Loop
rs1.Close
Rs2.Close
Set Rs1 = Nothing
Set Rs2 = Nothing

Remember:Table/Field Names arre for brevity only. Use your real names

 

[dbprogman]

Thanks for your reply, yes, the items are a simple list. I will give your suggestion a run and will let you know how I got on.

Stay tuned for the next exciting episode, when you will hear the words....

Cheers
And many thanks....

 

[dbprogman]

Ok tried your suggestion, got a little lost in the requirements so if you could expand a little on your suggestion this would be most helpful.

Table One

tblProject
ProjectID - PK
WorksID

Table Two

tblWorks
WorksID - PK
ProjectID
Description

Table Three

tblTasks
TaskID - PK
WorksID
Task - This field holds the ten(10) required tasks

Works Form

Combobox - User chooses project
Subform - Displays works details for chosen project has field "Task" (At present task chosen from dropdown - ValueList) - Need button "Add Tasks" this button will then add 10 new records to tblWorks and populate each of the 'Tasks" fields in those ten records with each of the tasks ie, record 1 - Electrical, Record 2 - Plumbing etc...

The problem I seem to be having is the creation of the third table

1. do I have one descriptive feild holding the ten (10) choices
2. Or do I create a feild for each choice ie, task1, task2 etc....
3. By giving all a PK do you mean highlight all feilds and add PK?
4. Should this table be related?

Sorry if seems a simple question its getting to the point I cant see the trees for the forest.

Your assistance is gratefully received
Many thanks....
dbprogman