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:StartDate 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.

[rajeevs]

Hi jforbes
Thank you for the help and support.
I tried the code just now. If line number 11

For iCount = 1 To Me.lstbx1.ListCount

it writes only the last item from the list.
I have modified that line to icount=0 like below then it writes two records because the list box selection was two items.

For iCount = 0 To Me.lstbx1.ListCount

I will try with more scenario and let you know the result.
One more question: if I have two different dates in the sdate and edate field and two items selected from list box how can I modify this to write the first record with sdate and item1 and second record with edate and item2. Because most of the situation the user will be selecting either consecutive dates or two different dates.
I will give it a try also with the code you have provided.
I thank you so much for the quick response

[jforbes]

Nice catch on the zero based index for the Listbox.

OK then, I assumed since you were using a loop on the Date before, that you expected to increment the Date as one Day at a time. If you want to allow the user to Select a Start Date and End Date and then select two items in the ListBox, then insert the First item with the Start Date and the Last Item with the End Date, this would be a slightly different scenario as the Date is no longer incremented by one Day. If I understand correctly, in this case, the Date would be Incremented by the amount of Days between the Start Date and End Date, divided by the amount of Items.
Increment = (EndDate-StartDate)/ItemCount

So if the User selects Three Items, then Selects a Start Date of the First of the Month (Day 1) and End Date of the 5th Day of the Month (Day 5), then the Three Items should be evenly distributed across those 5 Days ending up with:
Item 1 on Day 1
Item 2 on Day 3
Item 3 on Day 5

Also, if the User selects Six Items, then a Start Date of the First and an End Date of the Third, you should end up with:
Item 1 on Day 1
Item 2 on Day 1
Item 3 on Day 2
Item 4 on Day 2
Item 5 on Day 3
Item 6 on Day 3

Is my understanding correct?

[rajeevs]

Hi jforbes
Let me explain the concept of this recordset. Basically I am trying to put a choice of duty types to a table. Now the code is doing what i want to do with the entry. That is if there is a date range and same number of selections in the listbox it need to put date 1 list1 as record1 date2 list2 as record2 date3 list3 as record3 etc.
But my question is instead of daterange, i will have two dates in the date fields and two selections from listbox. How can i put record1 with date1 and list1 then record2 with date2 and list2.
Hope I explained well. Thank you for the efforts and your kindness

[jforbes]

This:

If there are 10 days then there will be 10 items selected from the listbox.

contradicts:

But my question is instead of daterange, i will have two dates in the date fields and two selections from listbox. How can i put record1 with date1 and list1 then record2 with date2 and list2.

The First/Previous Version would require one Day per Selected Item. The second is not bound by this one to one relationship. Instead, by implementing the formula:

Increment = (EndDate-StartDate)/ItemCount

The following code should distribute the Items over the Date Range:

    Dim Sdate1 As Date
    Dim edate1 As Date
    Dim iIncrement As Double
    Dim dTemp As Date
    Dim iCount As Integer
    Dim iItemCounter As Double
    Dim rs As Recordset
 
    Sdate1 = Me.date1
    edate1 = Me.date2
    If Me.lstbx1.ItemsSelected.Count > 1 Then iIncrement = (edate1 - Sdate1) / (Me.lstbx1.ItemsSelected.Count - 1)
    iItemCounter = 0
    Set rs = CurrentDb.OpenRecordset("Schedule", dbOpenDynaset)
 
    For iCount = 0 To Me.lstbx1.ListCount
        If Me.lstbx1.Selected(iCount) = True Then
            rs.AddNew
                dTemp = DateAdd("d", iItemCounter, Sdate1)
                rs!StartDate = dTemp
                rs!EndDate = dTemp
                rs!Action = Me.lstbx1.ItemData(iCount)
            rs.Update
            iItemCounter = iItemCounter + iIncrement
        End If
    Next iCount
 
    rs.Close
    Set rs = Nothing

[rajeevs]

Hi jforbes
Thank you for the reply. The first code is ok. My last question refers to a different scenerio. Only two dates and two selection in the list box. I want to write the first record with startdate and listbox item1. Next record will be Enddate and list box item 2.

[rajeevs]

Hi jforbes
I have posted a reply earlier without testing the code. But now I have tested the code and I feel that it is doing what i need.
Here, we are on weekend and I will test it on Monday thoroughly and let you know. I am so grateful to you and thank you for the patience and kindness.