Form with multiple items and submit button

[mari_hitz]

Hi everybody, as usual. I have this database with the purpose to storage all the tasks that are done in my team. I have a table named Tasks with all their fields. Now, I would like to set a more user friendly way for clients to update this table. I have created another table with a list of most common tasks, so when a client wants to add their tasks list they can choose one of this tasks and add it to the list. I had created a form with multiple items that contains the common tasks and next to each task a button that adds the information they choose into the table "Tasks". This works just fine. However, I would like to add a single button at the top to add all the tasks instead of having to choose one after one. Can you please help to do this? I know there must be some kind of loop but I do not know how to do it.
The "Add All tasks" button has this code:

Private Sub Command79_Click()
Dim valSelect As Variant, MyDB As DAO.Database, MyRS As DAO.Recordset
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Tasks", dbOpenDynaset)
 

  MyRS.AddNew
    MyRS![TransactionalTime] = TransactionalTime
    MyRS![ConstantTime] = ConstantTime
     MyRS![TaskName] = TaskName
       MyRS![GuID] = GU
    MyRS![TeamID] = Team
     MyRS![VisaID] = Category
  MyRS.Update

MyRS.Close
Set MyRS = Nothing
End Sub

Thanks in advance for all your help!

 

[pbaldy]

Would a multiselect listbox work for you?

http://www.baldyweb.com/MultiselectAppend.htm

 

[mari_hitz]

Hi pbaldy!

Thanks for your reply. Where should I consider the mutiselect list box? I don't see how it can be fit here. Actually I have the common tasks and they have each their specific time, I would like for the clients to add the tasks with the specific time, not only the task itself.

 

[pbaldy]

Where does all that come from now? Can't tell from the code if things like TransactionalTime are variables or form references. In any case, since they appear to be fixed, they could come from there (or from the listbox, if they are part of the data that is its source).

 

[mari_hitz]

Hi Pbaldy,

You can find my database here so you can check what I´ve been trying to do and how everything is connected.

http://www.mediafire.com/download/aauj0vgbv5r3pa7/ECM_Tool_Final_Version-Tasks_Play.zip

I cannot attach it here because it exceeds the forum's limit.

Regards,

Mari

 

[pbaldy]

You could use the listbox and include all the necessary fields in its row source (columns can be hidden. In my code, you'd adjust the listbox reference to allow you to specify columns:

ctl.Column(0, varItem)

That allows the user to select any number of items. You can still do the "All" button, but just cycle through all items instead of selected items. You can do it with the form, but you'd probably have to use a recordset based on the form's source table.

 

[mari_hitz]

Hi Pbaldy!

I appreciate your response. Honestly I am not that advanced and I am not sure how to make what you have mentioned, so I guess I'll keep looking around to make this work better.

 

[pbaldy]

I think if you dove in you wouldn't find it overwhelming. If you want to stick with the existing form, behind the "all" button, open a recordset on the same table the form is based on, loop all the items and within the loop your add code needs to refer to the new recordset:

MyRS![TransactionalTime] = MyOtherRs!TransactionalTime

 

[mari_hitz]

Thanks Pblady for your help! I honestly do not know how to do it, I've tried but had no luck

No worries, I have one last and final question and I do not know if I should create a new thread for this or not. Since each item comes from the table, when I select one the options on the fields, "GU","Country", etc, which are not completed in my table, the table itself it gets updated and saves the information selected. Do you know a way to undo this saving that it is automatically done? I have tried to create a button with close and acSaveNo but it continues saving the info into the table.
Also tried if Me.Dirty= True then Me.Undo, but nothing happens.

Thanks in advance!

Mari