I'm wondering how BEST to accomplish this.

I have a EXCEL workbook which populates data to, and retrieves data from SQL
Server.

I currently have VBA code which effectively sends the data from the EXCEL
worksheet to SQL Server tables -- no problem with this.

I currently have VBA code which effectively retrieves data from SQL Server
and displays that data back into the worksheet -- no problem with this
either.

I'm currently using a Command Button (on-click event) and the code below to
query SQL Server to determine the Max Version Number and add 1 to that
value. This value is then passed to a worksheet cell, and rolled up to SQL
Server along with the rest of the data to be submitted -- no problem with
this either.

Here's my "GenerateVersionNumber" code ...
===============================
Public Sub cmdGenerateVersionNumber_Click()
' Create a connection object.

Dim cnExcel As ADODB.Connection

Set cnExcel = New ADODB.Connection



' Provide the connection string.

Dim strConn As String



'Make Version the active sheet & Clear Data

'ThisWorkbook.Worksheets("Version").Range("A2:A150 ").Clear



'Use the SQL Server OLE DB Provider.

strConn = "PROVIDER=SQLOLEDB;"



'Connect to the XXXX database on the XXXX Server.

strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=XXXX;" & _

"User Id=xxxx;" & _

"Password=xxxx"

'Now open the connection.

cnExcel.Open strConn



On Error Resume Next



' Create a recordset object.

Dim ProdID As String

Dim sqlCommand As String

Dim rsExcel As ADODB.Recordset

Set rsExcel = New ADODB.Recordset

ProdID = InputBox("Enter Product ID.")



sqlCommand = "SELECT IsNull (max(Version), 0) + 1 FROM Products WHERE
[ProductID] = '" + ProdID + "'"

With rsExcel

' Assign the Connection object.

.ActiveConnection = cnExcel

' Extract the required records.

.Open sqlCommand

' Copy the records into cell B2 on Sheet15 (Product Tracking Overall)

Sheet15.Range("D2").CopyFromRecordset rsExcel



' Tidy up

.Close

End With



cnExcel.Close

Set rsExcel = Nothing

Set cnExcel = Nothing

End Sub

==============================

Here's what I'm tring to do ....

I don't want the Sale Team to have to enter ProductID more than once.
ProductID is a field that needs to be entered on a worksheet, and it's also
a Query Parameter which I send to SQL Server to generate my One-Up Version
Number. How can I modify my "GenerateVersionNumber" sub (shown above) to
not only query SQL Server with the ProductID parameter entered in the Input
Box, but to also send the value entered in the Input Box to a particular
cell on a selected worksheet?



Thanks in advance for any assistance/advice offered.

Try this:

Worksheets("Sheet1").Range("A1").value = ProdID

Replace "Sheet1" w/whatever sheet is appropriate and "A1" with a cell w/in
that ws.

HTH

Cheers,
socratis

"Doctorjones_md" wrote:

I'm wondering how BEST to accomplish this.

I have a EXCEL workbook which populates data to, and retrieves data from SQL
Server.

I currently have VBA code which effectively sends the data from the EXCEL
worksheet to SQL Server tables -- no problem with this.

I currently have VBA code which effectively retrieves data from SQL Server
and displays that data back into the worksheet -- no problem with this
either.

I'm currently using a Command Button (on-click event) and the code below to
query SQL Server to determine the Max Version Number and add 1 to that
value. This value is then passed to a worksheet cell, and rolled up to SQL
Server along with the rest of the data to be submitted -- no problem with
this either.

Here's my "GenerateVersionNumber" code ...
===============================
Public Sub cmdGenerateVersionNumber_Click()
' Create a connection object.

Dim cnExcel As ADODB.Connection

Set cnExcel = New ADODB.Connection



' Provide the connection string.

Dim strConn As String



'Make Version the active sheet & Clear Data

'ThisWorkbook.Worksheets("Version").Range("A2:A150 ").Clear



'Use the SQL Server OLE DB Provider.

strConn = "PROVIDER=SQLOLEDB;"



'Connect to the XXXX database on the XXXX Server.

strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=XXXX;" & _

"User Id=xxxx;" & _

"Password=xxxx"

'Now open the connection.

cnExcel.Open strConn



On Error Resume Next



' Create a recordset object.

Dim ProdID As String

Dim sqlCommand As String

Dim rsExcel As ADODB.Recordset

Set rsExcel = New ADODB.Recordset

ProdID = InputBox("Enter Product ID.")



sqlCommand = "SELECT IsNull (max(Version), 0) + 1 FROM Products WHERE
[ProductID] = '" + ProdID + "'"

With rsExcel

' Assign the Connection object.

.ActiveConnection = cnExcel

' Extract the required records.

.Open sqlCommand

' Copy the records into cell B2 on Sheet15 (Product Tracking Overall)

Sheet15.Range("D2").CopyFromRecordset rsExcel



' Tidy up

.Close

End With



cnExcel.Close

Set rsExcel = Nothing

Set cnExcel = Nothing

End Sub

==============================

Here's what I'm tring to do ....

I don't want the Sale Team to have to enter ProductID more than once.
ProductID is a field that needs to be entered on a worksheet, and it's also
a Query Parameter which I send to SQL Server to generate my One-Up Version
Number. How can I modify my "GenerateVersionNumber" sub (shown above) to
not only query SQL Server with the ProductID parameter entered in the Input
Box, but to also send the value entered in the Input Box to a particular
cell on a selected worksheet?



Thanks in advance for any assistance/advice offered.

Thanks Socratis -- your suggestion worked like a champ! :)
=========================================
"Socratis" wrote in message
...
Try this:

Worksheets("Sheet1").Range("A1").value = ProdID

Replace "Sheet1" w/whatever sheet is appropriate and "A1" with a cell w/in
that ws.

HTH

Cheers,
socratis

"Doctorjones_md" wrote:

I'm wondering how BEST to accomplish this.

I have a EXCEL workbook which populates data to, and retrieves data from
SQL
Server.

I currently have VBA code which effectively sends the data from the EXCEL
worksheet to SQL Server tables -- no problem with this.

I currently have VBA code which effectively retrieves data from SQL
Server
and displays that data back into the worksheet -- no problem with this
either.

I'm currently using a Command Button (on-click event) and the code below
to
query SQL Server to determine the Max Version Number and add 1 to that
value. This value is then passed to a worksheet cell, and rolled up to
SQL
Server along with the rest of the data to be submitted -- no problem
with
this either.

Here's my "GenerateVersionNumber" code ...
===============================
Public Sub cmdGenerateVersionNumber_Click()
' Create a connection object.

Dim cnExcel As ADODB.Connection

Set cnExcel = New ADODB.Connection



' Provide the connection string.

Dim strConn As String



'Make Version the active sheet & Clear Data

'ThisWorkbook.Worksheets("Version").Range("A2:A150 ").Clear



'Use the SQL Server OLE DB Provider.

strConn = "PROVIDER=SQLOLEDB;"



'Connect to the XXXX database on the XXXX Server.

strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=XXXX;" & _

"User Id=xxxx;" & _

"Password=xxxx"

'Now open the connection.

cnExcel.Open strConn



On Error Resume Next



' Create a recordset object.

Dim ProdID As String

Dim sqlCommand As String

Dim rsExcel As ADODB.Recordset

Set rsExcel = New ADODB.Recordset

ProdID = InputBox("Enter Product ID.")



sqlCommand = "SELECT IsNull (max(Version), 0) + 1 FROM Products WHERE
[ProductID] = '" + ProdID + "'"

With rsExcel

' Assign the Connection object.

.ActiveConnection = cnExcel

' Extract the required records.

.Open sqlCommand

' Copy the records into cell B2 on Sheet15 (Product Tracking Overall)

Sheet15.Range("D2").CopyFromRecordset rsExcel



' Tidy up

.Close

End With



cnExcel.Close

Set rsExcel = Nothing

Set cnExcel = Nothing

End Sub

==============================

Here's what I'm tring to do ....

I don't want the Sale Team to have to enter ProductID more than once.
ProductID is a field that needs to be entered on a worksheet, and it's
also
a Query Parameter which I send to SQL Server to generate my One-Up
Version
Number. How can I modify my "GenerateVersionNumber" sub (shown above) to
not only query SQL Server with the ProductID parameter entered in the
Input
Box, but to also send the value entered in the Input Box to a particular
cell on a selected worksheet?



Thanks in advance for any assistance/advice offered.

hi,
after ProdID = InputBox("Enter Product ID.") you could put something like
this...

Range("A1").Value = ProdID
or
Cells(1, 1).Value = ProdID

regards
FSt1

"Doctorjones_md" wrote:

I'm wondering how BEST to accomplish this.

I have a EXCEL workbook which populates data to, and retrieves data from SQL
Server.

I currently have VBA code which effectively sends the data from the EXCEL
worksheet to SQL Server tables -- no problem with this.

I currently have VBA code which effectively retrieves data from SQL Server
and displays that data back into the worksheet -- no problem with this
either.

I'm currently using a Command Button (on-click event) and the code below to
query SQL Server to determine the Max Version Number and add 1 to that
value. This value is then passed to a worksheet cell, and rolled up to SQL
Server along with the rest of the data to be submitted -- no problem with
this either.

Here's my "GenerateVersionNumber" code ...
===============================
Public Sub cmdGenerateVersionNumber_Click()
' Create a connection object.

Dim cnExcel As ADODB.Connection

Set cnExcel = New ADODB.Connection



' Provide the connection string.

Dim strConn As String



'Make Version the active sheet & Clear Data

'ThisWorkbook.Worksheets("Version").Range("A2:A150 ").Clear



'Use the SQL Server OLE DB Provider.

strConn = "PROVIDER=SQLOLEDB;"



'Connect to the XXXX database on the XXXX Server.

strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=XXXX;" & _

"User Id=xxxx;" & _

"Password=xxxx"

'Now open the connection.

cnExcel.Open strConn



On Error Resume Next



' Create a recordset object.

Dim ProdID As String

Dim sqlCommand As String

Dim rsExcel As ADODB.Recordset

Set rsExcel = New ADODB.Recordset

ProdID = InputBox("Enter Product ID.")



sqlCommand = "SELECT IsNull (max(Version), 0) + 1 FROM Products WHERE
[ProductID] = '" + ProdID + "'"

With rsExcel

' Assign the Connection object.

.ActiveConnection = cnExcel

' Extract the required records.

.Open sqlCommand

' Copy the records into cell B2 on Sheet15 (Product Tracking Overall)

Sheet15.Range("D2").CopyFromRecordset rsExcel



' Tidy up

.Close

End With



cnExcel.Close

Set rsExcel = Nothing

Set cnExcel = Nothing

End Sub

==============================

Here's what I'm tring to do ....

I don't want the Sale Team to have to enter ProductID more than once.
ProductID is a field that needs to be entered on a worksheet, and it's also
a Query Parameter which I send to SQL Server to generate my One-Up Version
Number. How can I modify my "GenerateVersionNumber" sub (shown above) to
not only query SQL Server with the ProductID parameter entered in the Input
Box, but to also send the value entered in the Input Box to a particular
cell on a selected worksheet?



Thanks in advance for any assistance/advice offered.