Code to create several rows in table not working

[mari_hitz]

Hi everyone,

Hope you are right. I have an issue with a code that I have in a form which adds rows in a table as many times as categories chosen from a list. However, the code is not working correctly: it adds the information and creates a row with a category in blank, in addition to the rest of the rows with one of the chosen categories. I would like for this not to happen, to add only as many rows as the categories chosen.

My code is the following:

Private Sub cmdUpdate_Click()
 Dim valSelect As Variant, MyDB As DAO.Database, MyRS As DAO.Recordset
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Tasks", dbOpenDynaset)
 
MyRS.MoveFirst
 
For Each valSelect In Me.listVisaType.ItemsSelected
  MyRS.AddNew
    MyRS![VisaID] = Me.listVisaType.ItemData(valSelect)
    MyRS![GuID] = GuID
    MyRS![CountryID] = CountryID
    MyRS![Transactional Time] = TimetoPerformtheTask
    MyRS![TeamID] = TeamID
    MyRS![TaskName] = TaskName
  MyRS.Update
Next valSelect
 
MyRS.Close
Set MyRS = Nothing
End Sub

Can you help me please? Thanks!!!

 

[jdraw]

Can you show some sample data to highlight the issue?
Where does Category fit in you code?
What is the complete structure of table Tasks?

 

[namliam]

I dont understand your myrs.movefirst in the code....

Regardless...
For Each valSelect In Me.listVisaType.ItemsSelected

Should only return the actually selected values

 

[mari_hitz]

Hi guys,

Thanks for your reply.
My table contains all the tasks that are done in my team and has the following fields: Task name, GU, Country, Category, Transactional Time, Constant Time.

The form that contains the code has this fields to be completed, however on category I have a list that contains all categories. When I want to insert a task that it is done for several types of categories, I had the task and then I select the different categories for which that task is performed. Then the code should add as many rows and lines as categories I have chosen, all with the same info. I don't know if I explained myself good. So if I chose 5 categories the code adds me 6 rows, 5 correctly created with the information and the category chosen and one line with all the information and with the category field in blank. How can I make for this not to happen? Is my code wrong?

Regards!

 

[namliam]

I dont understand your myrs.movefirst in the code....

Regardless...
For Each valSelect In Me.listVisaType.ItemsSelected

Should only return the actually selected values

Only thing I can think of is your form is bound to the same table as well, which is creating the blank....

You could try adding a Debug.print "Adding " & Me.listVisaType.ItemData(valSelect)
to the for next loop (probably best just after the addnew line....
That should confirm the code isnt causing your issue.

 

[mari_hitz]

Hi namliam,

Thanks for your response! Could you please indicate me how do I add the debug to the code?

Regards Thanks for your help!

 

[namliam]

You could try adding a Debug.print "Adding " & Me.listVisaType.ItemData(valSelect)
to the for next loop (probably best just after the addnew line....
That should confirm the code isnt causing your issue.

Is that really that hard to understand as to where you should add that piece of code? REALLY !!! :banghead:

Private Sub cmdUpdate_Click()
 Dim valSelect As Variant, MyDB As DAO.Database, MyRS As DAO.Recordset
 
Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset("Tasks", dbOpenDynaset)
 
MyRS.MoveFirst
 
For Each valSelect In Me.listVisaType.ItemsSelected
  MyRS.AddNew
    Debug.print "Adding "  & Me.listVisaType.ItemData(valSelect)
    MyRS![VisaID] = Me.listVisaType.ItemData(valSelect)
    MyRS![GuID] = GuID
    MyRS![CountryID] = CountryID
    MyRS![Transactional Time] = TimetoPerformtheTask
    MyRS![TeamID] = TeamID
    MyRS![TaskName] = TaskName
  MyRS.Update
Next valSelect
 
MyRS.Close
Set MyRS = Nothing
End Sub

Hope that this allows you to copy/paste with pride