deyken
09-27-2010, 04:01 AM
Hi There,
I am trying to create a simple Excel Worksheet interface for a SQL Server 2008 Database. Essentially I am creating a "Refresh" function which will fill a Listbox (ActiveX version) with a Recordset fetched from said database. I pasted my code below for ease of reference.
My problem is on the line attampting to utilize the Listbox1.AddItem() method. My error Message reads: "Object required..." - My question: Should I need to reference a specific module, or is my syntax incorrect?
Function Refresh()
' This function will refresh the entire page & controls with updated (and filtered ) data from the Main DB.
Dim DB As New ADODB.Connection
DB.CursorLocation = adUseClient
DB.Open "Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=SHIPPING004\SQLEXPRESS;Database=BrolazShipping"
Dim Projects As New ADODB.Recordset
Dim Ships As New ADODB.Recordset
Dim i, j As Integer
If MsgBox("Would you like to refresh your Data? This could take a few minutes...", vbYesNo, "XL Brolaz Shipping System", 0, 0) = vbYes Then
' Begin refresh and load data into Listboxes
Projects.Open "Select * from tblPROJECTS ORDER BY PROJECT_CODE", DB, adOpenDynamic, adLockOptimistic
' Display Projects in Listbox
If Projects.RecordCount <> 0 Then
For i = 0 To Projects.RecordCount - 1
Sheet1.ListBox1.AddItem (Project.Fields(0).Value)
Next i
Else
MsgBox "No Projects found", vbCritical
End If
End If
End Function
I am trying to create a simple Excel Worksheet interface for a SQL Server 2008 Database. Essentially I am creating a "Refresh" function which will fill a Listbox (ActiveX version) with a Recordset fetched from said database. I pasted my code below for ease of reference.
My problem is on the line attampting to utilize the Listbox1.AddItem() method. My error Message reads: "Object required..." - My question: Should I need to reference a specific module, or is my syntax incorrect?
Function Refresh()
' This function will refresh the entire page & controls with updated (and filtered ) data from the Main DB.
Dim DB As New ADODB.Connection
DB.CursorLocation = adUseClient
DB.Open "Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=SHIPPING004\SQLEXPRESS;Database=BrolazShipping"
Dim Projects As New ADODB.Recordset
Dim Ships As New ADODB.Recordset
Dim i, j As Integer
If MsgBox("Would you like to refresh your Data? This could take a few minutes...", vbYesNo, "XL Brolaz Shipping System", 0, 0) = vbYes Then
' Begin refresh and load data into Listboxes
Projects.Open "Select * from tblPROJECTS ORDER BY PROJECT_CODE", DB, adOpenDynamic, adLockOptimistic
' Display Projects in Listbox
If Projects.RecordCount <> 0 Then
For i = 0 To Projects.RecordCount - 1
Sheet1.ListBox1.AddItem (Project.Fields(0).Value)
Next i
Else
MsgBox "No Projects found", vbCritical
End If
End If
End Function