SQL From STMT

[gdspeare]

I am getting an error message on they FROM statement of the below code. Anyone see why?

Public Sub fill()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\speared\My Documents\db1.mdb"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM [Test]", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.MoveLast
counter = .RecordCount
.MoveFirst
End With
ListBox1.ColumnCount = rs.Fields.Count
ListBox1.Column = rs.GetRows(counter)
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub

 

[gdspeare]

Follow Up

Alright...I figured out the SQL error, the code is below, but now I am curious about something else. I have a listbox embedded on my sheet and I get an Object Required Error, but I created a userform with a listbox and it works as i intended. Hmmm?? Why would that be?


Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\speared\My Documents\db1.mdb"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "(SELECT * FROM [David])", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' MsgBox "table is open" '''TEST TO SEE IF CODE WORKS TO HERE
With rs
.MoveLast
counter = .RecordCount
fieldcount = .Fields.Count
.MoveFirst
End With
'MsgBox "record count is " & "" & counter ''''TEST CODE
MsgBox "field count is " & "" & fieldcount ''''TEST CODE
''''THIS IS WHERE I AM GETTING THE OBJECT REQUIRED ERROR'''''
ListBox1.ColumnCount = fieldcount
ListBox1.Column = rs.GetRows(counter)
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

 

[Norie]

How did you create the listbox on the worksheet and where is the code located?

 

[gdspeare]

Thanks for the brain jog...my code was in a module and I not in the sheet itself and I was not calling the procedule.

 

[gdspeare]

I know that I am killing this topic, but now I am trying to use a combobox to set my search filter based on a combobox selection. I am getting an error that there is no value given for my parameter. This would be the parameter "rep". When I put in a salesperson name it works fine, but not with the combobox


Dim rep As Variant
ComboBox1.ListFillRange = "o16"

rep = ComboBox1.Value

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\Documents and Settings\speared\My Documents\db1.mdb"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "(SELECT name FROM [David] where (salesrep=rep))", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' MsgBox "table is open"
With rs