Dynamic List Box using VBA

[Doctorjones_md]

I wasn't really sure how to phrase this one -- this isn't your Typical
Drop-Down List.

I have a SELECT statement which queries SQL Server 2005 and displays the
data in Excel.

Let's say that a database lists products that have been delivered to 4
different cities, on 3 seperate days -- without knowing (in advance) the
delivery dates, how could I create an input/drop-down field to allow the
user to specify a particular date? I envision something like this:

VBA code produces a list box of delivery dates based on a particular product
and city. The user selects a delivery date from the list-boxe, and this
selection gets passed to a SQL Select Statement to return the data to an
EXCEL spreadsheet. I just don't know of any way to do this ...

Below is the code I'm using --
===================
Option Explicit
Sub DataExtractSpecific()
' Create a connection object.
Dim cnExcel As ADODB.Connection
Set cnExcel = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the DWS_Sales database on the Products Server.
strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=Products;" & _
"User Id=xxxxxxx;" & _
"Password=xxxxxx"


'Now open the connection.
cnExcel.Open strConn

On Error Resume Next

' Create a recordset object.
Dim OppNumber As String
Dim sqlCommand As String
'Dim CloseDate As Date
Dim rsExcel As ADODB.Recordset
Set rsExcel = New ADODB.Recordset
OppNumber = InputBox("Please Re-Enter Product Number for the 2nd lookup
query.")
'CloseDate = InputBox("Enter the Purchase Date -- like 2/26/2007 11:44")
sqlCommand = "SELECT * FROM Tracking_Specific WHERE [Product Number] = " +
ProdNumber
With rsExcel
' Assign the Connection object.
.ActiveConnection = cnExcel
' Extract the required records.
.Open sqlCommand
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A3").CopyFromRecordset rsExcel

' Tidy up
.Close
End With

cnExcel.Close
Set rsExcel = Nothing
Set cnExcel = Nothing

End Sub
===============
Many Thanks (in advance) for any assistance on this.

Shane

 

[Bob Phillips]

Do you have to do a subsequent SQL query, or could you just filter the data
that you already have?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[doctorjones_md]

Bob,

It appears to me (but I'm open to alternative suggestions) that I'll need to
perform an initial SQL query to create the list box, then pass the selection
from this list box to a subsequent SELECT statement for the final query.
I'm using a Date/Time stamp for the delivery date, so there isn't a way for
the user to know the exact delivery Date/Time without selecting it from a
list box.

I'm not certain that what I'm trying to do here is even possible -- any
thoughts on this?

Thanks for your prompt reply.

Shane
==================================

Do you have to do a subsequent SQL query, or could you just filter the
data that you already have?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

I wasn't really sure how to phrase this one -- this isn't your Typical
Drop-Down List.

I have a SELECT statement which queries SQL Server 2005 and displays the
data in Excel.

Let's say that a database lists products that have been delivered to 4
different cities, on 3 seperate days -- without knowing (in advance) the
delivery dates, how could I create an input/drop-down field to allow the
user to specify a particular date? I envision something like this:

VBA code produces a list box of delivery dates based on a particular
product and city. The user selects a delivery date from the list-boxe,
and this selection gets passed to a SQL Select Statement to return the
data to an EXCEL spreadsheet. I just don't know of any way to do this
...

Below is the code I'm using --
===================
Option Explicit
Sub DataExtractSpecific()
' Create a connection object.
Dim cnExcel As ADODB.Connection
Set cnExcel = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the DWS_Sales database on the Products Server.
strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=Products;" &
_
"User Id=xxxxxxx;" & _
"Password=xxxxxx"


'Now open the connection.
cnExcel.Open strConn

On Error Resume Next

' Create a recordset object.
Dim OppNumber As String
Dim sqlCommand As String
'Dim CloseDate As Date
Dim rsExcel As ADODB.Recordset
Set rsExcel = New ADODB.Recordset
OppNumber = InputBox("Please Re-Enter Product Number for the 2nd lookup
query.")
'CloseDate = InputBox("Enter the Purchase Date -- like 2/26/2007 11:44")
sqlCommand = "SELECT * FROM Tracking_Specific WHERE [Product Number] = "
+
ProdNumber
With rsExcel
' Assign the Connection object.
.ActiveConnection = cnExcel
' Extract the required records.
.Open sqlCommand
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A3").CopyFromRecordset rsExcel

' Tidy up
.Close
End With

cnExcel.Close
Set rsExcel = Nothing
Set cnExcel = Nothing

End Sub
===============
Many Thanks (in advance) for any assistance on this.

Shane

 

[Bob Phillips]

If you do issue another query, you could issue it with SQL such as

sqlCommand = "SELECT * FROM Tracking_Specific " & _
"WHERE [Product Number] = " & ProdNumber & " AND " &
_
" [Date] = #" & Range("A1").Text & "#"

where A1 is the cell with the drop-down date selected.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Bob,

It appears to me (but I'm open to alternative suggestions) that I'll need
to perform an initial SQL query to create the list box, then pass the
selection from this list box to a subsequent SELECT statement for the
final query. I'm using a Date/Time stamp for the delivery date, so there
isn't a way for the user to know the exact delivery Date/Time without
selecting it from a list box.

I'm not certain that what I'm trying to do here is even possible -- any
thoughts on this?

Thanks for your prompt reply.

Shane
==================================

Do you have to do a subsequent SQL query, or could you just filter the
data that you already have?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

I wasn't really sure how to phrase this one -- this isn't your Typical
Drop-Down List.

I have a SELECT statement which queries SQL Server 2005 and displays the
data in Excel.

Let's say that a database lists products that have been delivered to 4
different cities, on 3 seperate days -- without knowing (in advance) the
delivery dates, how could I create an input/drop-down field to allow the
user to specify a particular date? I envision something like this:

VBA code produces a list box of delivery dates based on a particular
product and city. The user selects a delivery date from the list-boxe,
and this selection gets passed to a SQL Select Statement to return the
data to an EXCEL spreadsheet. I just don't know of any way to do this
...

Below is the code I'm using --
===================
Option Explicit
Sub DataExtractSpecific()
' Create a connection object.
Dim cnExcel As ADODB.Connection
Set cnExcel = New ADODB.Connection

' Provide the connection string.
Dim strConn As String

'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"

'Connect to the DWS_Sales database on the Products Server.
strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=Products;" &
_
"User Id=xxxxxxx;" & _
"Password=xxxxxx"


'Now open the connection.
cnExcel.Open strConn

On Error Resume Next

' Create a recordset object.
Dim OppNumber As String
Dim sqlCommand As String
'Dim CloseDate As Date
Dim rsExcel As ADODB.Recordset
Set rsExcel = New ADODB.Recordset
OppNumber = InputBox("Please Re-Enter Product Number for the 2nd lookup
query.")
'CloseDate = InputBox("Enter the Purchase Date -- like 2/26/2007 11:44")
sqlCommand = "SELECT * FROM Tracking_Specific WHERE [Product Number] = "
+
ProdNumber
With rsExcel
' Assign the Connection object.
.ActiveConnection = cnExcel
' Extract the required records.
.Open sqlCommand
' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A3").CopyFromRecordset rsExcel

' Tidy up
.Close
End With

cnExcel.Close
Set rsExcel = Nothing
Set cnExcel = Nothing

End Sub
===============
Many Thanks (in advance) for any assistance on this.

Shane