|
Subject:
|
Help! trying to insert data into a table
|
|
Posted By:
|
ziwez0
|
Post Date:
|
1/8/2004 5:28:32 AM
|
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
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
1/8/2004 8:11:54 AM
|
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
|
|
Reply By:
|
ziwez0
|
Reply Date:
|
1/8/2004 9:11:01 AM
|
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
|
|
Reply By:
|
SerranoG
|
Reply Date:
|
1/8/2004 10:12:12 AM
|
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
|
