UserForm, Copy Data

[yn19832]

I have designed an UserForm in Excel, the aim is to copy selected data from Access (tblIndex) to Excel. The form of the Access database is as following:
Country Type Date Index
....... ...... ...... ......

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. What I want to do is to copy the records with selected Country, Type, and Index from the start date and end date. I suppose I should use SQL like:

strSELECT = "SELECT tblIndex.*"
strFROM = "SELECT tblIndex"

strWHERE, I do not know how to define the condition here. The clause below is what I mean, but it looks weird and it does not work.

Num = LiqForm.ListCT.ListCount
For i = 0 To Num - 1
strWHERE = "WHERE tblIndex.Type = LiqForm.ListCT.List(i,0) AND _
tblIndex.Country = LiqForm.ListCT.List(i,1) AND _
tblIndex.Date > LiqForm.TextBoxSta.Text AND tblIndex.Date < LiqForm.TextBoxEnd.Text"

Next i

strSQL = strSELECT & strFROM & strWHERE
rec= db.OpenRecordset(strSQL)

Besides, after this, how can I copy the selected records to the Excel sheet?

I am really a beginner in VBA and I would be grateful if anyone can give me some advice.

[Ken Puls]

Hi there,

I have an article on my site to Pull data from Access to Excel, that you may want to read.

With regards to your SQL, I'm guessing that it should be something more like:
[vba] strWHERE = "WHERE tblIndex.Type = " & LiqForm.ListCT.List(i,0) & " AND " _
"tblIndex.Country = " & LiqForm.ListCT.List(i,1) & " AND " _
"tblIndex.Date > " & LiqForm.TextBoxSta.Text & " AND tblIndex.Date < " & LiqForm.TextBoxEnd.Text[/vba]

Please keep in mind, though, that this is untested on my end. They key here is that you're passing a string, and you need to compile it as such. anything you send between the quotes will be sent to access, including your references to the Excel objects. You need to close of the quotes temporarily, then reference the Excel objects, then re-open the quotes and continue on with building the rest of your string. Does that make sense?

[yn19832]

Thank you very much.

I am afraid I do not completely understand what you mean, I am really a beginner. The problem now is how to select the records I need, I mean
with the WHERE clause, I think you understand what I want to say with the following code:

[VBA]
strWHERE = "WHERE tblIndex.Type = " & LiqForm.ListCT.List(i,0) &" AND _
tblIndex.Country = " & LiqForm.ListCT.List(i,1)&" AND _
tblIndex.Date >" & LiqForm.TextBoxSta.Text &" AND tblIndex.Date < " & LiqForm.TextBoxEnd.Text &"
[/VBA]

It seems that just turn it into string is not enough. Could you pls help me with this?

[yn19832]

what I want to ask is how to refer to the value, for example, for the field country, I want to refer to the value in the first column of the list box "ListCT", and for the date, I want to refer to all the dates between the value of the "textboxsta" and the value of the "textboxend"

many many thanks

[Ken Puls]

Would you mind uploading a sanitized version of your file? I'd like to see your listbox in action. I can then just make sure that whatever code I give you works on the source file as well.

Thanks!

[yn19832]

Many thanks

[Ken Puls]

Hi there,

Try this. I'm not 100% sure I got your index right, (couldn't populate the userform, obviously.)

[vba] strSQL = "SELECT tblIndex.* FROM tblIndex " & _
"WHERE tblIndex.Type = " & LiqForm.ListCT.Value & " AND " & _
"tblIndex.Country = " & LiqForm.ListCT.Value & " AND " & _
"tblIndex.Date > = #" & LiqForm.TextBoxSta.Text & "# AND tblIndex.Date <=#" & LiqForm.TextBoxEnd.Text & "#"[/vba]

For reference, I couldn't test, but would usually use a BETWEEN SQL statement. You can get the syntax for that from Access.

HTH,

[yn19832]

Hi there,

Try this. I'm not 100% sure I got your index right, (couldn't populate the userform, obviously.)

[vba] strSQL = "SELECT tblIndex.* FROM tblIndex " & _
"WHERE tblIndex.Type = " & LiqForm.ListCT.Value & " AND " & _
"tblIndex.Country = " & LiqForm.ListCT.Value & " AND " & _
"tblIndex.Date > = #" & LiqForm.TextBoxSta.Text & "# AND tblIndex.Date <=#" & LiqForm.TextBoxEnd.Text & "#"[/vba]

For reference, I couldn't test, but would usually use a BETWEEN SQL statement. You can get the syntax for that from Access.

HTH,

Thank you very much for your help, and I think the following codes shoud work, but it does not. I would appreciate any advice.

[vba]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.Date > = #" & StaDate & "# AND tblIndex.Date <=#" & EndDate & "#"
strSQL = strSELECT & strFROM & strWHERE
Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)
Next i[/vba]

Basically, I want to set a recordset with all the selected items, date and the corresponding index in the Access Database. I am a starter in VBA and Access, and this project is in urgent need. I am stuck here and could anyone give me some advice?

[geekgirlau]

Can't test it at this end, but what format are your dates in? Usually I find in an SQL string you need to have something like

[vba]tblIndex.Date > = #" & Format(StaDate,"mm/dd/yyyy") & "#[/vba]

[moa]

Hi yn19832.

Can't say I know a lot about recordsets but it looks like you are only setting rec to the last item in the list since you are overwriting it in each successive step of the For statement. Is this what you want to do?

[yn19832]

Thank you for your reply. In fact, I want to copy all the selected records and the revised codes are as following:

[VBA]

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)

If Not rec.EOF Then
rec.MoveLast
rec.MoveFirst
intRecord = rec.RecordCount
varArray = rec.GetRows(intRecord)

intFieldCount = UBound(varArray, 1)
intRowCount = UBound(varArray, 2)

'Make Sure Sheet1 is Activate

Sheets("Sheet1").Activate

'Determine the next empty row

NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1

'Set worksheet range

If i = 0 Then
Set TheRange = Range(Cells(1, 1), Cells(intRowCount + 1, intFieldCount + 1))
Else
Set TheRange = Range(Cells(NextRow, 1), Cells(NextRow + intRowCount, intFieldCount + 1))
End If

'Copy the record to Excel

TheRange.Value = Application.WorksheetFunction.Transpose(varArray)

[/VBA]

It works in a strange way, sometimes when you enter two countries, it turned out to be one, sometimes it turmed out to be two. When you enter three countries, it turns out to be two or one, and foe each of them, the records are doubled. For example, when I enter " Australia Sentiment" "Austria Sentiment" from 01/01/1980 to 05/01/1980, it is like following:

29251.00 Australia Sentiment 1/5/1900
2/29/1980 Australia Sentiment 8.7776
29311.00 Australia Sentiment 26.1377
29341.00 Australia Sentiment 22.6108
29251.00 Australia Sentiment 5.6739
29280.00 Australia Sentiment 8.7776
29311.00 Australia Sentiment 26.1377
29341.00 Australia Sentiment 22.6108
29251.00 Austria Sentiment 53.1886
29280.00 Austria Sentiment 52.9153
29311.00 Austria Sentiment 53.0859
29341.00 Austria Sentiment 55.3118
29251.00 Austria Sentiment 53.1886
2/29/1980 Austria Sentiment 52.9153
29311.00 Austria Sentiment 53.0859
29341.00 Austria Sentiment 55.3118