Loop trough listbox to update table

[mari_hitz]

Hi everone,



Hope you are Ok. I have a database with a table that contains taks called "tblMain". Each entry is a task that is performed in my team. There are tasks that are done specifically for a type of visa, however there are tasks that are done the same regardless the visa, so I have to create the same task as many type of visas are. Since creating the several entries for same task for each visa is too much work, I want to create a form in which I can complete the rest of the fields for the task and in the visa type I had created a List Box. I wanted a code to create several entries for the same tasks and on the visa type to add the visa type. I had found codes in the web and I had managed to create one, however it creates one row and on visa type, all the visas types that I had selected and this is not what I want. I want as many rows/entries to be created as many visas types including on the field only one of the visa types selected and so on. The reason why I want this is because I have queries with Sums depending on the visa type. Do you know if what I am trying to achieve is possible?

Please find my code below

Private Sub cmdUpdate_Click()
 Dim valSelect As Variant, MyDB As DAO.Database, MyRS As DAO.Recordset
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("tblMain", dbOpenDynaset)
 
MyRS.MoveFirst
 
For Each valSelect In Me.Lista113.ItemsSelected
  MyRS.AddNew
    MyRS![Visa Type] = Me.Lista113.ItemData(valSelect)
    MyRS![GU] = GU
    MyRS![Country] = Country
    MyRS![Task] = Task
    MyRS![Team] = Team
    MyRS![Division] = Division
    MyRS![TransactionalTime] = TransactionalTime
    MyRS![ConstantTime] = ConstantTime
  MyRS.Update
Next valSelect
 
MyRS.Close
Set MyRS = Nothing
End Sub

[ItsMe]

The code you provided is adding a new record to tblMain for each item selected in your listbox. What is it that is not happening? What else is needed?

[mari_hitz]

The code you provided is adding a new record to tblMain for each item selected in your listbox. What is it that is not happening? What else is needed?

Hi Itsme, the problem is that adds a single row and on "Visa Type" adds all the types of visa I had choosen instead of creating as many lines as many types of Visas I have choosen with the same info.
There are tasks that takes the same time regardless of the visa type. So I don´t want a single row with all the visas types for this cases, I want the task to be repeated as many times as may visas is the task performed.
For example: "Analyze information" task takes 10 minutes regardless the type of visa. Assuming I have 5 visas types. I want 5 times the task "Analyze information" to be added with each field of the table completed with the same data, however, in the visa type field there should be a visa type. I don't know if I explained myself good.

[ItsMe]

What you are describing is a normalization issue. If you read your code, you will see that there is duplication of data.

    MyRS![Visa Type] = Me.Lista113.ItemData(valSelect)
    MyRS![GU] = GU
    MyRS![Country] = Country
    MyRS![Task] = Task
    MyRS![Team] = Team
    MyRS![Division] = Division
    MyRS![TransactionalTime] = TransactionalTime
    MyRS![ConstantTime] = ConstantTime

It seems to me that you need a junction table to store multiple Visas or a single Visa. Your table Main should have a PK. In another table that has a Primary Key of Autonumber, include two other columns, one column as Foreign Key for your tblMain and another column as Foreign Key for your visa ( MyRS![Visa Type] = Me.Lista113.ItemData(valSelect) )

[mari_hitz]

Thank you very much! That worked! You rock!