Multiple Listbox and multiple items to ACCESS Table

[rajeevs]

Hi
I have 2 date fields (date1 and date2)and 3 listbox (Listb1, LIstb2, and ListB3) in an ACCESS form.
What I am trying to do is select dates in the date fields, select 2 items from each listbox and trying to save it to a table. I need the date1,Listb1 item1, Listb2 item1 and Listb3 item1 to be saved as one record and date2,Listb1 item2, Listb2 item2 and Listb3 item2 to be saved as another record.
The code I am using is as below:

Dim rs As Recordset
 Set rs = CurrentDb.OpenRecordset("tbl", dbOpenDynaset)
 
 With rs
For Each varItem In Me.Listb1.ItemsSelected
Dim Sdate1 As Date
Dim edate1 As Date
Dim n As Date
Dim c As Long
Sdate1 = Me.date1
edate1 = Me.date2
    For n = Sdate1 To edate1
           c = c + 1
 
 rs.AddNew
 rs!StartDate = Me.date1
 rs!EndDate = Me.date2
 rs!Listb1item1 = Me.Listb1.ItemData(varItem) 
 rs!istb2item1 = Me.listb2.Column(0) 
 rs!lstb3item1 = Me.listb3.Column(0) 
 rs!rptfld = Me.Listb1.ItemData(varItem)   'this is another field to hold the same data of rs!Listb1item1
 
rs.Update
 
Next n
Next varItem
 
End With
 
rs.Close
Set rs = Nothing
 

But this code is not doing the way I want it. Any help will be appreciated. Thank you

[jimatqsi]

rajeevs, could you be more specific about the problem? Just saying it doesn't work is not very informative. What is happening?

I have never tried a for/next loop based on incrementing a date. Does that work? You might want to stop the debugger to check if n is incrementing correctly.

Jim

[rajeevs]

Hi jimatqsi
Sorry for the delay. I was off duty during the last two days.
That was a copied code from somewhere.
What I want to do is : there will be two date selections in the form (from and to dates) and 3 listboxes which can be used to select multiple Items (listboxes as listbox1, listbox2 and listbox3). If the duration of dates selected is 5 then the count of items will be equal to that count.
eg: if date1 is 01-01-15 and date2 is 05-01-15 the user will select 5 items from the listboxes. After the selections I like to put 5 records into the table as :
record1 with date 01-01-15 listbox1 item1 listbox2 item1 listbox3 item1. Record 2 will be with date 01-01-05 and listbox1 item2, listbox2 item2 and listbox3 item2.
Record3 with date 03-01-15 and listbox items 3
record 4 with date 04-01-15 and listbox items 4 and record 5 with date 05-01-15 and listbox items 5
Hope I explained well. Any help to modify the code according to my requirement will be appreciated.
Thank you

[rajeevs]

Hi

I have modified the code as below. Now my problem is the first record writes correctly but the second record writes everything correctly except the date. In the second record I need the edate1 to be written.
Any help?

Dim rs As Recordset
 Set rs = CurrentDb.OpenRecordset("tbl", dbOpenDynaset)
 
With rs
 For Each varItem In Me.lstbx1.ItemsSelected
 
Dim Sdate1 As Date
Dim edate1 As Date
Dim n As Date
Sdate1 = Me.SDate
edate1 = Me.EDate
 
  For n = Sdate1 To Sdate1
 
 rs.AddNew
 
 rs!StartDate = n
 rs!EndDate = n
 rs!fld1 = Me.lstbx1.ItemData(varItem)  
 'there are other fields as well which I have removed from this code
 rs.Update
 
 
Next n
 
Next varItem
 
End With
 
 rs.Close
 Set rs = Nothing

[jforbes]

I'm not sure what happens when you include a Dim Statement inside of a For Each Loop. You might try moving the Dim Statements in Lines 7-9 of post #4 to the Top of the Code. I would also move Lines 10 and 11 along with the Dim Statements as you really only need to get these values once.

Also, Line 13 sets up a loop where the start and end are the same. If this is correct, then you really don't need a loop as it will just run once.

[rajeevs]

Hi jforbes

I have modified the code earlier also and now the modified one i am posting. But this code is creating 4 records if the list box selection is 2 items and startdate to end date is 2 days

Dim rs As Recordset
 Set rs = CurrentDb.OpenRecordset("tbl", dbOpenDynaset)
 Dim n As Date
 
 
With rs
For Each varItem In Me.lstbox.ItemsSelected
 
For n = Me.SDate To Me.EDate
 
 rs.AddNew
 
 rs!StartDate = n
 rs!EndDate = n
 rs!lsboxfld= Me.lsbox.ItemData(varItem)
 
rs.Update
 
Next n
 
Next varItem
 
 
End With
 
 rs.Close
 Set rs = Nothing
 
 

Suppose if i have sdate as 01-01-15 and edate as 02-01-15 and listbox 2 items selected (Say apple and orange) then there will be 4 records added to the tbl as
record1: StartDate 01-01-15 item apple enddate 01-01-15.
record2: StartDate 02-01-15 item apple enddate 02-01-15.
record3: StartDate 01-01-15 item orange enddate 01-01-15.
record4: StartDate 02-01-15 item orange enddate 02-01-15.
But my desired result need to be as below:
REcord1:StartDa te 01-01-15 item apple enddate 01-01-15.
record2: StartDate 02-01-15 item orange enddate 02-01-15.
Hope I have explained well. please advice how to modify the code to get this result

[jforbes]

Interesting. I still don't know enough of the rules to help.

What do you do if there are 10 days between the Start Date and End Date and only 2 Items Selected? Do you insert Ten Records, five with the first Item and five with the second item? Or do you insert Two Records, one on the start date with the First Item and one on the End Date with the Last Item?

What do you do if there are 10 items selected and only 2 days between the Start and End Dates?

[rajeevs]

Hi jforbes
Thank you for the kind reply. If there are 10 days then there will be 10 items selected from the listbox. That is the way it is restricted. So for each date between start date and end date there need to be one record for each item selected. record 1 will have date1 and item1 record2 will have date2 and item2 like that.
Hope i explained well on the logic. Please help

[jforbes]

I think I understand now what you are attempting to do. I think the first place you are getting stuck is you only need one Loop structure to do what you want, and adding two loops is just messing things up. Since you are only inserting one date per selected item, it would be easiest to loop on the selected items and insert a date for each one found. So in that case, you don't even need to know the End Date as it will be the Start Date plus a day for each item selected. This is at least my understanding.

Given this understanding, you could do something like the following. It only has one loop and inserts one record for each item that is selected. Also, I just mocked this up, so I'm not 100% sure it's syntax is correct:

    Dim Sdate1 As Date
    Dim dTemp As Date
    Dim iCount As Integer
    Dim iItemCount As Integer
    Dim rs As Recordset
 
    Sdate1 = Me.sDate
    iItemCount = 0
    Set rs = CurrentDb.OpenRecordset("Schedule", dbOpenDynaset)
 
    For iCount = 1 To Me.lstbx1.ListCount
        If Me.lstbx1.Selected(iCount) = True Then
            rs.AddNew
                dTemp = DateAdd("d", iItemCount, Sdate1)
                rs!StartDate = dTemp
                rs!EndDate = dTemp
                rs!Action = Me.lstbx1.ItemData(iCount)
            rs.Update
            iItemCount = iItemCount + 1
        End If
    Next iCount
 
    rs.Close
    Set rs = Nothing
 

[rajeevs]

Hi jforbes
Thank you for the prompt reply. I think your code will solve my issue. I haven't tried that yet. as soon as I saw your reply I thought of replying first. I will try the code and let you know the result.