Private Sub cmdAdd_Click()
Dim rs as new ADODB.recordsetI have two list boxes called "lstdates" which contain 600 dates and a list box called "lstboats" which contain 60 boat names, i want to be able to select multiple dates and multiple boats and insert them into a table called tblboatstodates fieldss boatid and checkdateid, the following code:

Dim varBoatID
Dim varCheckDateID



    rs.Open "tblBoatstoDates", CurrentProject.Connection, adOpenStatic, adLockOptimistic
    For Each varBoatID In lstBoats.ItemsSelected
        For Each varCheckDateID In lstDates.ItemsSelected

            rs.AddNew

            rs!BoatID = varBoatID
            rs!Checkdateid = varCheckDateID
            rs.Update
        Next
    Next
    rs.Close


End Sub

the code works but is inputting the order they appear

Example: say if i had 10 dates in the list box"lstdates" ranging from 01 Jan 04 -- 10 Jan 04

 (this dates come from a table called tblcheckdate1 and the unique key is DateID)
01 Jan 04 = DATEID 26
02 Jan 04 = DATEID 27
03 Jan 04 = DATEID 28
04 Jan 04 = DATEID 29
05 Jan 04 = DATEID 30
06 Jan 04 = DATEID 31
..etc

BUT as 01 Jan 04 appears first in the list its value =0 when inputting into tblboatstodates
02 Jan 04 =1
03 Jan 04 =2

this is the same for lstboats

So what exactly is the problem? Do you want 01 Jan 04 to be DateID 26 and not be zero in the table? I'm not sure what the problem is.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

the dates come from a table called tblcheckdate1 it has two fields :: checkdateid and checkdate

Example

checkdateid | checkdate
--------------------------
26 01 Jan 04
27 02 Jan 04

the problem is that 01 Jan 04 appears in the list box (lstdates) as the first record, so when i press cmdadd

0 gets entered into tblboatstodates.checkdateid where it should be 26

-
David

On your listbox that lists the dates, make the rowsource of the list box not only the dates but the CheckDateID as well. Make the listbox show two columns (Column Count property = 2): CheckDateID and Date. Set the bound column to Column 1 (CheckDateID). Set the column width of Column 1 to Zero (i.e. invisible) and set the column width of Column 2 to whatever you want. Set the list width = to the width of Column 2. When the user picks a date, Access will take the CORRECT CheckDateID and store it in your table.

If your list box is called, for example, lstMyListBox then CheckDateID will be equal to lstMyListBox.Column(0) and the date will be equal to lstMyListBox.Column(1)


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division