PDA

View Full Version : UserForm- Select records from Access



yn19832
03-27-2007, 06:58 AM
I have designed an UserForm in Excel and tried to select data from Access (tblIndex) . The UserForm contains a ListBox "ListCountry" and a ComboBox "ListType" to select country and type, and a ListBox named "ListCT" with two columns for selected country and type, and two TextBox (TextDateSta and TextDateEnd) for the user to Type in start date and end date.

A problem came out, for example, when I add "Sentiment Canada" "Sentiment Chile" to the listbox named "ListCT", it just copy the sentiment chile's data twice to excel and the date is a mess as following:

29251.00 Chile Sentiment 33
29280.00 Chile Sentiment 26
29311.00 Chile Sentiment 13
29341.00 Chile Sentiment 14
1/31/1980Chile Sentiment 33
29280.00 Chile Sentiment 26
29311.00 Chile Sentiment 13
4/30/1980Chile Sentiment 14

I guess there should be sth wrong with the loop, for which I want it to select all the records in "ListCT", but I could not figure it out, I would appreciate your advice. codes are:

Num = LiqForm.ListCT.ListCount
For i = 0 To Num - 1

TypeArray = LiqForm.ListCT.List(i, 0)
CountryArray = LiqForm.ListCT.List(i, 1)
StaDate = LiqForm.TextBoxSta.Text
EndDate = LiqForm.TextBoxEnd.Text

strSELECT = "SELECT tblIndex.* "
strFROM = "FROM tblIndex "
strWHERE = "WHERE tblIndex.Type='" & TypeArray & "' AND " & _
"tblIndex.Country='" & CountryArray & "' AND " & _
"tblIndex.Calendar>=#" & StaDate & "# AND tblIndex.Calendar<=#" & EndDate & "# "
strSQL = strSELECT & strFROM & strWHERE
Debug.Print strSQL
Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)
Next i


Thanks in advance!

geekgirlau
03-27-2007, 09:24 PM
Can you post a copy of the workbook?