Trying something with Record Set

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can Someone tell How I can reference these to a NamedRange or can't this be
done
I Get the error # 3265 Item cannot be found in collection

VENDOR_ID
VEND_PART_NUM
VEND_DESC

Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work

'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID
'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM
'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC
 
Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work

Try it this way (where I assume the name of your range is NamedRange1...

Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value

Rick
 
Sheets(wsName).Range("A" & i) = rs1!Range("DBDataField1").Value 'Same Error

Sheets(wsName).Range("A" & i) = Range("DBDataField1").Value
and this will list VENDOR_ID where it should be 44

Maybe its not ment to be

Rick Rothstein (MVP - VB) said:
Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work
Click to expand...

Try it this way (where I assume the name of your range is NamedRange1...

Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value

Rick
Click to expand...
 
Here's more of the code

On Error GoTo ErrorHandling
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim wsName As String
Dim i As Integer

wsName = [DestWkstName].Value
Sheets(wsName).Cells.ClearContents

i = [DestStartCellRef].Value

'Determine the ref number of the selected query
'and record it in the LU_OptSelected range name
With Application.Names("LU_OptSelected")
intOptRef = CInt(Left(Right(.RefersTo, 3), 2))
End With

'Post a status message to the QryMaster sheet
[QryOptList].Offset(RowOffset:=intOptRef, ColumnOffset:=1).Value _
= "Running Query"

Application.ScreenUpdating = False

'Use for Access (jet)

strConn = "Provider=" & [DBDataProvider] & ";" _
& "Data Source=" & [DBDataSource] & ""
'Use for jet
strSQL1 = [SQLCode] & ";"

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
'Heres where I'm trying to also use a named range
Sheets(wsName).Range("A" & i) = rs1!Range("DBDataField1").Value
'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID
'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM
'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close

Rick Rothstein (MVP - VB) said:
Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work
Click to expand...

Try it this way (where I assume the name of your range is NamedRange1...

Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value

Rick
Click to expand...
 
I think you need

Dim RecSetColumnName As String
RecSetColumnName = Range("NameOfCellOnWorksheet").Value
Range("A1").Value = RecSet.Fields(RecSetColumnName).Value

It is a matter of personal preference and coding style, but I would move
away for the shortcut notation that uses the [ ] characters and write out
the objects you are really using. It makes code much easier to read, debug,
and maintain.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




Mike said:
Here's more of the code

On Error GoTo ErrorHandling
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim wsName As String
Dim i As Integer

wsName = [DestWkstName].Value
Sheets(wsName).Cells.ClearContents

i = [DestStartCellRef].Value

'Determine the ref number of the selected query
'and record it in the LU_OptSelected range name
With Application.Names("LU_OptSelected")
intOptRef = CInt(Left(Right(.RefersTo, 3), 2))
End With

'Post a status message to the QryMaster sheet
[QryOptList].Offset(RowOffset:=intOptRef, ColumnOffset:=1).Value _
= "Running Query"

Application.ScreenUpdating = False

'Use for Access (jet)

strConn = "Provider=" & [DBDataProvider] & ";" _
& "Data Source=" & [DBDataSource] & ""
'Use for jet
strSQL1 = [SQLCode] & ";"

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
'Heres where I'm trying to also use a named range
Sheets(wsName).Range("A" & i) = rs1!Range("DBDataField1").Value
'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID
'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM
'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close

Rick Rothstein (MVP - VB) said:
Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work
Click to expand...

Try it this way (where I assume the name of your range is NamedRange1...

Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value

Rick
Click to expand...
Click to expand...
 
Thanks Chip and Rick for your Time but still no luck
I'm going to give up. Everything was going good up until the
rs1!Range("FieldName").Value

I had got the Idea from Ron Coderre QryMaster

Thanks anyway guys

Chip Pearson said:
I think you need

Dim RecSetColumnName As String
RecSetColumnName = Range("NameOfCellOnWorksheet").Value
Range("A1").Value = RecSet.Fields(RecSetColumnName).Value

It is a matter of personal preference and coding style, but I would move
away for the shortcut notation that uses the [ ] characters and write out
the objects you are really using. It makes code much easier to read, debug,
and maintain.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




Mike said:
Here's more of the code

On Error GoTo ErrorHandling
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim wsName As String
Dim i As Integer

wsName = [DestWkstName].Value
Sheets(wsName).Cells.ClearContents

i = [DestStartCellRef].Value

'Determine the ref number of the selected query
'and record it in the LU_OptSelected range name
With Application.Names("LU_OptSelected")
intOptRef = CInt(Left(Right(.RefersTo, 3), 2))
End With

'Post a status message to the QryMaster sheet
[QryOptList].Offset(RowOffset:=intOptRef, ColumnOffset:=1).Value _
= "Running Query"

Application.ScreenUpdating = False

'Use for Access (jet)

strConn = "Provider=" & [DBDataProvider] & ";" _
& "Data Source=" & [DBDataSource] & ""
'Use for jet
strSQL1 = [SQLCode] & ";"

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
'Heres where I'm trying to also use a named range
Sheets(wsName).Range("A" & i) = rs1!Range("DBDataField1").Value
'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID
'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM
'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close

Rick Rothstein (MVP - VB) said:
Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work

Try it this way (where I assume the name of your range is NamedRange1...

Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value

Rick
Click to expand...
Click to expand...
Click to expand...
 
Scratch It I Got Thanks So Much Chip

Chip Pearson said:
I think you need

Dim RecSetColumnName As String
RecSetColumnName = Range("NameOfCellOnWorksheet").Value
Range("A1").Value = RecSet.Fields(RecSetColumnName).Value

It is a matter of personal preference and coding style, but I would move
away for the shortcut notation that uses the [ ] characters and write out
the objects you are really using. It makes code much easier to read, debug,
and maintain.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




Mike said:
Here's more of the code

On Error GoTo ErrorHandling
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim wsName As String
Dim i As Integer

wsName = [DestWkstName].Value
Sheets(wsName).Cells.ClearContents

i = [DestStartCellRef].Value

'Determine the ref number of the selected query
'and record it in the LU_OptSelected range name
With Application.Names("LU_OptSelected")
intOptRef = CInt(Left(Right(.RefersTo, 3), 2))
End With

'Post a status message to the QryMaster sheet
[QryOptList].Offset(RowOffset:=intOptRef, ColumnOffset:=1).Value _
= "Running Query"

Application.ScreenUpdating = False

'Use for Access (jet)

strConn = "Provider=" & [DBDataProvider] & ";" _
& "Data Source=" & [DBDataSource] & ""
'Use for jet
strSQL1 = [SQLCode] & ";"

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
'Heres where I'm trying to also use a named range
Sheets(wsName).Range("A" & i) = rs1!Range("DBDataField1").Value
'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID
'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM
'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close

Rick Rothstein (MVP - VB) said:
Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work

Try it this way (where I assume the name of your range is NamedRange1...

Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value

Rick
Click to expand...
Click to expand...
Click to expand...
 
Don't give up. You'll learn a lot following through to a successful
conclusion. I think that if you get rid of all the shortcut references,
those using the [ ] and ! characters and replace them with the verbose
object names and properties, you'll see right away what is causing the
problem. And then the next time you have to go down this road (and there is
always a next time), you'll have a well written procedure template into
which you just plug the the right object names and you'll be done. The
verbose syntax is means more typing this around, but you'll find advantages
later for debugging and code reuse.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


Mike said:
Thanks Chip and Rick for your Time but still no luck
I'm going to give up. Everything was going good up until the
rs1!Range("FieldName").Value

I had got the Idea from Ron Coderre QryMaster

Thanks anyway guys

Chip Pearson said:
I think you need

Dim RecSetColumnName As String
RecSetColumnName = Range("NameOfCellOnWorksheet").Value
Range("A1").Value = RecSet.Fields(RecSetColumnName).Value

It is a matter of personal preference and coding style, but I would move
away for the shortcut notation that uses the [ ] characters and write out
the objects you are really using. It makes code much easier to read,
debug,
and maintain.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




Mike said:
Here's more of the code

On Error GoTo ErrorHandling
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim wsName As String
Dim i As Integer

wsName = [DestWkstName].Value
Sheets(wsName).Cells.ClearContents

i = [DestStartCellRef].Value

'Determine the ref number of the selected query
'and record it in the LU_OptSelected range name
With Application.Names("LU_OptSelected")
intOptRef = CInt(Left(Right(.RefersTo, 3), 2))
End With

'Post a status message to the QryMaster sheet
[QryOptList].Offset(RowOffset:=intOptRef, ColumnOffset:=1).Value _
= "Running Query"

Application.ScreenUpdating = False

'Use for Access (jet)

strConn = "Provider=" & [DBDataProvider] & ";" _
& "Data Source=" & [DBDataSource] & ""
'Use for jet
strSQL1 = [SQLCode] & ";"

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
'Heres where I'm trying to also use a named range
Sheets(wsName).Range("A" & i) = rs1!Range("DBDataField1").Value
'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID
'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM
'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close

:

Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work

Try it this way (where I assume the name of your range is
NamedRange1...

Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value

Rick
Click to expand...
Click to expand...
Click to expand...
 
Chip
One more thing Please
Say if dataField4 is Null Or empty how can the code still run
Sheets(wsName).Range("A" & i).Value = rs1.Fields(dataField1).Value
Sheets(wsName).Range("B" & i).Value = rs1.Fields(dataField2).Value
Sheets(wsName).Range("C" & i).Value = rs1.Fields(dataField3).Value
Sheets(wsName).Range("D" & i).Value = rs1.Fields(dataField4).Value

Chip Pearson said:
Don't give up. You'll learn a lot following through to a successful
conclusion. I think that if you get rid of all the shortcut references,
those using the [ ] and ! characters and replace them with the verbose
object names and properties, you'll see right away what is causing the
problem. And then the next time you have to go down this road (and there is
always a next time), you'll have a well written procedure template into
which you just plug the the right object names and you'll be done. The
verbose syntax is means more typing this around, but you'll find advantages
later for debugging and code reuse.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


Mike said:
Thanks Chip and Rick for your Time but still no luck
I'm going to give up. Everything was going good up until the
rs1!Range("FieldName").Value

I had got the Idea from Ron Coderre QryMaster

Thanks anyway guys

Chip Pearson said:
I think you need

Dim RecSetColumnName As String
RecSetColumnName = Range("NameOfCellOnWorksheet").Value
Range("A1").Value = RecSet.Fields(RecSetColumnName).Value

It is a matter of personal preference and coding style, but I would move
away for the shortcut notation that uses the [ ] characters and write out
the objects you are really using. It makes code much easier to read,
debug,
and maintain.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




Here's more of the code

On Error GoTo ErrorHandling
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim wsName As String
Dim i As Integer

wsName = [DestWkstName].Value
Sheets(wsName).Cells.ClearContents

i = [DestStartCellRef].Value

'Determine the ref number of the selected query
'and record it in the LU_OptSelected range name
With Application.Names("LU_OptSelected")
intOptRef = CInt(Left(Right(.RefersTo, 3), 2))
End With

'Post a status message to the QryMaster sheet
[QryOptList].Offset(RowOffset:=intOptRef, ColumnOffset:=1).Value _
= "Running Query"

Application.ScreenUpdating = False

'Use for Access (jet)

strConn = "Provider=" & [DBDataProvider] & ";" _
& "Data Source=" & [DBDataSource] & ""
'Use for jet
strSQL1 = [SQLCode] & ";"

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
'Heres where I'm trying to also use a named range
Sheets(wsName).Range("A" & i) = rs1!Range("DBDataField1").Value
'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID
'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM
'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close

:

Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work

Try it this way (where I assume the name of your range is
NamedRange1...

Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value

Rick
Click to expand...
Click to expand...
Click to expand...
 
Say if dataField4 is Null Or empty how can the code still run

I haven't tested this lately, but if memory serves, Excel will convert a
database NULL to the text string "null" when it puts the value in a cell.
Whether that is good design is open to debate. A database Empty will be
simply a blank cell with no content. If you store a database Null value in a
Variant data type before transferring it to a worksheet cell , e.g.,

Dim V As Variant
V = RecSet.Fields("SomeField").Value

and SomeField is Null, the variable V will contain a Null value (E.g.,

Debug.Print IsNull(V)

prints TRUE)

but when that value is transfered to a worksheet cell, it is converted to
the String "Null".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


Mike said:
Chip
One more thing Please
Say if dataField4 is Null Or empty how can the code still run
Sheets(wsName).Range("A" & i).Value =
rs1.Fields(dataField1).Value
Sheets(wsName).Range("B" & i).Value =
rs1.Fields(dataField2).Value
Sheets(wsName).Range("C" & i).Value =
rs1.Fields(dataField3).Value
Sheets(wsName).Range("D" & i).Value =
rs1.Fields(dataField4).Value

Chip Pearson said:
Don't give up. You'll learn a lot following through to a successful
conclusion. I think that if you get rid of all the shortcut references,
those using the [ ] and ! characters and replace them with the verbose
object names and properties, you'll see right away what is causing the
problem. And then the next time you have to go down this road (and there
is
always a next time), you'll have a well written procedure template into
which you just plug the the right object names and you'll be done. The
verbose syntax is means more typing this around, but you'll find
advantages
later for debugging and code reuse.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


Mike said:
Thanks Chip and Rick for your Time but still no luck
I'm going to give up. Everything was going good up until the
rs1!Range("FieldName").Value

I had got the Idea from Ron Coderre QryMaster

Thanks anyway guys

:

I think you need

Dim RecSetColumnName As String
RecSetColumnName = Range("NameOfCellOnWorksheet").Value
Range("A1").Value = RecSet.Fields(RecSetColumnName).Value

It is a matter of personal preference and coding style, but I would
move
away for the shortcut notation that uses the [ ] characters and write
out
the objects you are really using. It makes code much easier to read,
debug,
and maintain.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




Here's more of the code

On Error GoTo ErrorHandling
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim wsName As String
Dim i As Integer

wsName = [DestWkstName].Value
Sheets(wsName).Cells.ClearContents

i = [DestStartCellRef].Value

'Determine the ref number of the selected query
'and record it in the LU_OptSelected range name
With Application.Names("LU_OptSelected")
intOptRef = CInt(Left(Right(.RefersTo, 3), 2))
End With

'Post a status message to the QryMaster sheet
[QryOptList].Offset(RowOffset:=intOptRef, ColumnOffset:=1).Value _
= "Running Query"

Application.ScreenUpdating = False

'Use for Access (jet)

strConn = "Provider=" & [DBDataProvider] & ";" _
& "Data Source=" & [DBDataSource] & ""
'Use for jet
strSQL1 = [SQLCode] & ";"

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
'Heres where I'm trying to also use a named range
Sheets(wsName).Range("A" & i) =
rs1!Range("DBDataField1").Value
'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID
'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM
'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close

:

Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work

Try it this way (where I assume the name of your range is
NamedRange1...

Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value

Rick
Click to expand...
Click to expand...
Click to expand...
 
Chip I turned all to Variant
This is the formula that is in DBDataFields5
=INDIRECT(LU_OptSelected) INDIRECT(D33) returns 0
=IF(INDIRECT(LU_OptSelected) INDIRECT(D33)=0,"",INDIRECT(LU_OptSelected)
INDIRECT(D33)) Returns ""

If the DBDataField5 = 0 Runs but returns dataField1 Value (Wierd)
If the DBDataField5 = "" 'Error
Not sure what i'm missing maybe the LIGHT will come on later

Dim dataField1 As Variant, dataField2 As Variant, dataField3 As Variant, _
dataField4 As Variant, dataField5 As Variant

dataField1 = Range("DBDataField1").Value
dataField2 = Range("DBDataField2").Value
dataField3 = Range("DBDataField3").Value
dataField4 = Range("DBDataField4").Value
dataField5 = Range("DBDataField5").Value

Sheets(wsName).Range("A" & i).Value = rs1.Fields(dataField1).Value
Sheets(wsName).Range("B" & i).Value = rs1.Fields(dataField2).Value
Sheets(wsName).Range("C" & i).Value = rs1.Fields(dataField3).Value
Sheets(wsName).Range("D" & i).Value = rs1.Fields(dataField4).Value
Sheets(wsName).Range("E" & i).Value = rs1.Fields(dataField5).Value

Chip Pearson said:
Say if dataField4 is Null Or empty how can the code still run
Click to expand...

I haven't tested this lately, but if memory serves, Excel will convert a
database NULL to the text string "null" when it puts the value in a cell.
Whether that is good design is open to debate. A database Empty will be
simply a blank cell with no content. If you store a database Null value in a
Variant data type before transferring it to a worksheet cell , e.g.,

Dim V As Variant
V = RecSet.Fields("SomeField").Value

and SomeField is Null, the variable V will contain a Null value (E.g.,

Debug.Print IsNull(V)

prints TRUE)

but when that value is transfered to a worksheet cell, it is converted to
the String "Null".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


Mike said:
Chip
One more thing Please
Say if dataField4 is Null Or empty how can the code still run
Sheets(wsName).Range("A" & i).Value =
rs1.Fields(dataField1).Value
Sheets(wsName).Range("B" & i).Value =
rs1.Fields(dataField2).Value
Sheets(wsName).Range("C" & i).Value =
rs1.Fields(dataField3).Value
Sheets(wsName).Range("D" & i).Value =
rs1.Fields(dataField4).Value

Chip Pearson said:
Don't give up. You'll learn a lot following through to a successful
conclusion. I think that if you get rid of all the shortcut references,
those using the [ ] and ! characters and replace them with the verbose
object names and properties, you'll see right away what is causing the
problem. And then the next time you have to go down this road (and there
is
always a next time), you'll have a well written procedure template into
which you just plug the the right object names and you'll be done. The
verbose syntax is means more typing this around, but you'll find
advantages
later for debugging and code reuse.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


Thanks Chip and Rick for your Time but still no luck
I'm going to give up. Everything was going good up until the
rs1!Range("FieldName").Value

I had got the Idea from Ron Coderre QryMaster

Thanks anyway guys

:

I think you need

Dim RecSetColumnName As String
RecSetColumnName = Range("NameOfCellOnWorksheet").Value
Range("A1").Value = RecSet.Fields(RecSetColumnName).Value

It is a matter of personal preference and coding style, but I would
move
away for the shortcut notation that uses the [ ] characters and write
out
the objects you are really using. It makes code much easier to read,
debug,
and maintain.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




Here's more of the code

On Error GoTo ErrorHandling
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim wsName As String
Dim i As Integer

wsName = [DestWkstName].Value
Sheets(wsName).Cells.ClearContents

i = [DestStartCellRef].Value

'Determine the ref number of the selected query
'and record it in the LU_OptSelected range name
With Application.Names("LU_OptSelected")
intOptRef = CInt(Left(Right(.RefersTo, 3), 2))
End With

'Post a status message to the QryMaster sheet
[QryOptList].Offset(RowOffset:=intOptRef, ColumnOffset:=1).Value _
= "Running Query"

Application.ScreenUpdating = False

'Use for Access (jet)

strConn = "Provider=" & [DBDataProvider] & ";" _
& "Data Source=" & [DBDataSource] & ""
'Use for jet
strSQL1 = [SQLCode] & ";"

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
'Heres where I'm trying to also use a named range
Sheets(wsName).Range("A" & i) =
rs1!Range("DBDataField1").Value
'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID
'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM
'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close

:

Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not work

Try it this way (where I assume the name of your range is
NamedRange1...

Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value

Rick
Click to expand...
Click to expand...
Click to expand...
 
Mike,
If the DBDataField5 = 0 Runs but returns dataField1 Value (Wierd)
Click to expand...

That is because Fields is a 0-based "array" (not really a real Array or
Collection, but works more or less like a combination of the two). That is,
RecSet.Fields(0) is the same as RecSet.Fields("FirstFieldName"). You can
access fields either by their Name --- RecSet.Fields("MyFieldName") -- or by
their position in the record set -- RecSet.Fields(1). The position number 0
refers to the first field in the record set.

The following can be used to return the last field in the RecSet if you
don't happen to know its name:

With RecSet
V = .Fields(.Fields.Count - 1)
End With
If the DBDataField5 = "" 'Error
Click to expand...

That is because there is no Field whose name is an empty string. You'll get
a "Field Name Not Found In RecordSet" error or something to that effect.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)



Mike said:
Chip I turned all to Variant
This is the formula that is in DBDataFields5
=INDIRECT(LU_OptSelected) INDIRECT(D33) returns 0
=IF(INDIRECT(LU_OptSelected) INDIRECT(D33)=0,"",INDIRECT(LU_OptSelected)
INDIRECT(D33)) Returns ""

If the DBDataField5 = 0 Runs but returns dataField1 Value (Wierd)
If the DBDataField5 = "" 'Error
Not sure what i'm missing maybe the LIGHT will come on later

Dim dataField1 As Variant, dataField2 As Variant, dataField3 As
Variant, _
dataField4 As Variant, dataField5 As Variant

dataField1 = Range("DBDataField1").Value
dataField2 = Range("DBDataField2").Value
dataField3 = Range("DBDataField3").Value
dataField4 = Range("DBDataField4").Value
dataField5 = Range("DBDataField5").Value

Sheets(wsName).Range("A" & i).Value =
rs1.Fields(dataField1).Value
Sheets(wsName).Range("B" & i).Value =
rs1.Fields(dataField2).Value
Sheets(wsName).Range("C" & i).Value =
rs1.Fields(dataField3).Value
Sheets(wsName).Range("D" & i).Value =
rs1.Fields(dataField4).Value
Sheets(wsName).Range("E" & i).Value =
rs1.Fields(dataField5).Value

Chip Pearson said:
Say if dataField4 is Null Or empty how can the code still run
Click to expand...

I haven't tested this lately, but if memory serves, Excel will convert a
database NULL to the text string "null" when it puts the value in a cell.
Whether that is good design is open to debate. A database Empty will be
simply a blank cell with no content. If you store a database Null value
in a
Variant data type before transferring it to a worksheet cell , e.g.,

Dim V As Variant
V = RecSet.Fields("SomeField").Value

and SomeField is Null, the variable V will contain a Null value (E.g.,

Debug.Print IsNull(V)

prints TRUE)

but when that value is transfered to a worksheet cell, it is converted to
the String "Null".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


Mike said:
Chip
One more thing Please
Say if dataField4 is Null Or empty how can the code still run
Sheets(wsName).Range("A" & i).Value =
rs1.Fields(dataField1).Value
Sheets(wsName).Range("B" & i).Value =
rs1.Fields(dataField2).Value
Sheets(wsName).Range("C" & i).Value =
rs1.Fields(dataField3).Value
Sheets(wsName).Range("D" & i).Value =
rs1.Fields(dataField4).Value

:

Don't give up. You'll learn a lot following through to a successful
conclusion. I think that if you get rid of all the shortcut
references,
those using the [ ] and ! characters and replace them with the verbose
object names and properties, you'll see right away what is causing the
problem. And then the next time you have to go down this road (and
there
is
always a next time), you'll have a well written procedure template
into
which you just plug the the right object names and you'll be done.
The
verbose syntax is means more typing this around, but you'll find
advantages
later for debugging and code reuse.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


Thanks Chip and Rick for your Time but still no luck
I'm going to give up. Everything was going good up until the
rs1!Range("FieldName").Value

I had got the Idea from Ron Coderre QryMaster

Thanks anyway guys

:

I think you need

Dim RecSetColumnName As String
RecSetColumnName = Range("NameOfCellOnWorksheet").Value
Range("A1").Value = RecSet.Fields(RecSetColumnName).Value

It is a matter of personal preference and coding style, but I would
move
away for the shortcut notation that uses the [ ] characters and
write
out
the objects you are really using. It makes code much easier to
read,
debug,
and maintain.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




Here's more of the code

On Error GoTo ErrorHandling
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim wsName As String
Dim i As Integer

wsName = [DestWkstName].Value
Sheets(wsName).Cells.ClearContents

i = [DestStartCellRef].Value

'Determine the ref number of the selected query
'and record it in the LU_OptSelected range name
With Application.Names("LU_OptSelected")
intOptRef = CInt(Left(Right(.RefersTo, 3), 2))
End With

'Post a status message to the QryMaster sheet
[QryOptList].Offset(RowOffset:=intOptRef,
ColumnOffset:=1).Value _
= "Running Query"

Application.ScreenUpdating = False

'Use for Access (jet)

strConn = "Provider=" & [DBDataProvider] & ";" _
& "Data Source=" & [DBDataSource] & ""
'Use for jet
strSQL1 = [SQLCode] & ";"

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
'Heres where I'm trying to also use a named range
Sheets(wsName).Range("A" & i) =
rs1!Range("DBDataField1").Value
'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID
'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM
'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close

:

Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not
work

Try it this way (where I assume the name of your range is
NamedRange1...

Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value

Rick
Click to expand...
Click to expand...
Click to expand...
 
Mike,
If the DBDataField5 = 0 Runs but returns dataField1 Value (Wierd)
Click to expand...

That is because Fields is a 0-based "array" (not really a real Array or
Collection, but works more or less like a combination of the two). That is,
RecSet.Fields(0) is the same as RecSet.Fields("FirstFieldName"). You can
access fields either by their Name --- RecSet.Fields("MyFieldName") -- or by
their position in the record set -- RecSet.Fields(1). The position number 0
refers to the first field in the record set.

The following can be used to return the last field in the RecSet if you
don't happen to know its name:

With RecSet
V = .Fields(.Fields.Count - 1)
End With
If the DBDataField5 = "" 'Error
Click to expand...

That is because there is no Field whose name is an empty string. You'll get
a "Field Name Not Found In RecordSet" error or something to that effect.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)



Mike said:
Chip I turned all to Variant
This is the formula that is in DBDataFields5
=INDIRECT(LU_OptSelected) INDIRECT(D33) returns 0
=IF(INDIRECT(LU_OptSelected) INDIRECT(D33)=0,"",INDIRECT(LU_OptSelected)
INDIRECT(D33)) Returns ""

If the DBDataField5 = 0 Runs but returns dataField1 Value (Wierd)
If the DBDataField5 = "" 'Error
Not sure what i'm missing maybe the LIGHT will come on later

Dim dataField1 As Variant, dataField2 As Variant, dataField3 As
Variant, _
dataField4 As Variant, dataField5 As Variant

dataField1 = Range("DBDataField1").Value
dataField2 = Range("DBDataField2").Value
dataField3 = Range("DBDataField3").Value
dataField4 = Range("DBDataField4").Value
dataField5 = Range("DBDataField5").Value

Sheets(wsName).Range("A" & i).Value =
rs1.Fields(dataField1).Value
Sheets(wsName).Range("B" & i).Value =
rs1.Fields(dataField2).Value
Sheets(wsName).Range("C" & i).Value =
rs1.Fields(dataField3).Value
Sheets(wsName).Range("D" & i).Value =
rs1.Fields(dataField4).Value
Sheets(wsName).Range("E" & i).Value =
rs1.Fields(dataField5).Value

Chip Pearson said:
Say if dataField4 is Null Or empty how can the code still run
Click to expand...

I haven't tested this lately, but if memory serves, Excel will convert a
database NULL to the text string "null" when it puts the value in a cell.
Whether that is good design is open to debate. A database Empty will be
simply a blank cell with no content. If you store a database Null value
in a
Variant data type before transferring it to a worksheet cell , e.g.,

Dim V As Variant
V = RecSet.Fields("SomeField").Value

and SomeField is Null, the variable V will contain a Null value (E.g.,

Debug.Print IsNull(V)

prints TRUE)

but when that value is transfered to a worksheet cell, it is converted to
the String "Null".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


Mike said:
Chip
One more thing Please
Say if dataField4 is Null Or empty how can the code still run
Sheets(wsName).Range("A" & i).Value =
rs1.Fields(dataField1).Value
Sheets(wsName).Range("B" & i).Value =
rs1.Fields(dataField2).Value
Sheets(wsName).Range("C" & i).Value =
rs1.Fields(dataField3).Value
Sheets(wsName).Range("D" & i).Value =
rs1.Fields(dataField4).Value

:

Don't give up. You'll learn a lot following through to a successful
conclusion. I think that if you get rid of all the shortcut
references,
those using the [ ] and ! characters and replace them with the verbose
object names and properties, you'll see right away what is causing the
problem. And then the next time you have to go down this road (and
there
is
always a next time), you'll have a well written procedure template
into
which you just plug the the right object names and you'll be done.
The
verbose syntax is means more typing this around, but you'll find
advantages
later for debugging and code reuse.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


Thanks Chip and Rick for your Time but still no luck
I'm going to give up. Everything was going good up until the
rs1!Range("FieldName").Value

I had got the Idea from Ron Coderre QryMaster

Thanks anyway guys

:

I think you need

Dim RecSetColumnName As String
RecSetColumnName = Range("NameOfCellOnWorksheet").Value
Range("A1").Value = RecSet.Fields(RecSetColumnName).Value

It is a matter of personal preference and coding style, but I would
move
away for the shortcut notation that uses the [ ] characters and
write
out
the objects you are really using. It makes code much easier to
read,
debug,
and maintain.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)




Here's more of the code

On Error GoTo ErrorHandling
Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String, strConn
Dim wsName As String
Dim i As Integer

wsName = [DestWkstName].Value
Sheets(wsName).Cells.ClearContents

i = [DestStartCellRef].Value

'Determine the ref number of the selected query
'and record it in the LU_OptSelected range name
With Application.Names("LU_OptSelected")
intOptRef = CInt(Left(Right(.RefersTo, 3), 2))
End With

'Post a status message to the QryMaster sheet
[QryOptList].Offset(RowOffset:=intOptRef,
ColumnOffset:=1).Value _
= "Running Query"

Application.ScreenUpdating = False

'Use for Access (jet)

strConn = "Provider=" & [DBDataProvider] & ";" _
& "Data Source=" & [DBDataSource] & ""
'Use for jet
strSQL1 = [SQLCode] & ";"

Set cnn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
cnn.Open strConn
rs1.Open strSQL1, cnn, adOpenForwardOnly, adLockReadOnly
Do While rs1.EOF = False
'Heres where I'm trying to also use a named range
Sheets(wsName).Range("A" & i) =
rs1!Range("DBDataField1").Value
'Sheets(wsName).Range("A" & i) = rs1!VENDOR_ID
'Sheets(wsName).Range("B" & i) = rs1!VEND_PART_NUM
'Sheets(wsName).Range("C" & i) = rs1!VEND_DESC
i = i + 1
rs1.MoveNext
Loop
rs1.Close
cnn.Close

:

Sheets(wsName).Range("A" & i) = rs1![NamedRange1] 'will not
work

Try it this way (where I assume the name of your range is
NamedRange1...

Sheets(wsName).Range("A" & i).Value = Range("NamedRange1").Value

Rick
Click to expand...
Click to expand...
Click to expand...
 
Back
Top