Update table based on form- create multiple rows

[mari_hitz]

Hi everyone,

Hope you are all great. I have a question. I have a table with the following columns: Task, Visa type, time it takes to perform the task. There are several taks that are performed for all visa types. I want to create a form to enter data to the table in which for the field visa type I have a list box that can allow multiple values, however, I do not to create a single line with the task and on visa type all the types of visas selected. I want to create a line for each type of visa with the information introduced.
I don't know if this is possible, the reason for which I want for the form to create several rows depending on the visas types is because then I have a query that sums all the types of visas. Can this be possible? I don't want the people to introduce manually directly to the table the data and also that for the same taks they have to enter manually 50 rows with values. I want it to be more simple and easier.

Thanks!

 

[madEG]

I presume you are using a list box to show the available via types, right?

Use VBA to loop through each of those visa types selected as listbox's items, and for each item found selected, insert a new record with that visa type and the task and time (assuming task and time are maybe text boxes?)

That should do

Good luck!

 

[mari_hitz]

Hi madEG,
Thanks for your reply! Actually, I was trying to build it up myself and I would like to know if you can give me a hand with the code. I am attaching the database for your reference. I am not able to do it nothing works

 

[mari_hitz]

Hi.

Can someone help me please? I have found several codes in the net and I found that that works but adds me one single line with several visas types. Can you tell me how to work this code for the same to add as many lines as visas are? Thanks!

  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