Setting Row Source via VBA

[bg_ie]

Hi,

I have the following code which produces an excel doc with the
required info -

Dim recordSetTest As DAO.Recordset
Set recordSetTest = CurrentDb.OpenRecordset("Select tblTest.Test,
tblTest.Name, tblTest.TestStatus from tblTest")

excelRowPosition = 1
recordSetTest.MoveFirst
Do While Not recordSetTest.EOF
excelWorkSheet.Cells(excelRowPosition, 1) =
recordSetTest.Fields("Test")
excelWorkSheet.Cells(excelRowPosition, 2) =
recordSetTest.Fields("Name")
excelWorkSheet.Cells(excelRowPosition, 3) =
recordSetTest.Fields("TestStatus")

excelRowPosition = excelRowPosition + 1
recordSetTestCase.MoveNext 'move to next record in recordset
Loop

The problem is that TestStatus stores an index to a row in a second
table, where I store TestStatus values. This table has two fields -
Index and a text description of the status. Therefore, I would like to
print this status discription in the excel document rather than the
index, for each row in Test. How might I go about doing this?

Thanks for your help,

Barry.

[Tom van Stiphout]

On 9 May 2007 07:20:25 -0700, bg***@yahoo.com wrote:

Rather than selecting from a table, select from a new query you will
create, where you join the two tables on the TestStatus field. Your
code will remain virtually unchanged:
Set recordSetTest = CurrentDb.OpenRecordset("Select * from qryTest")

Also be sure to do the same in the Relationships window, and enforce
the relationship.

-Tom.

>Hi,

I have the following code which produces an excel doc with the
required info -

Dim recordSetTest As DAO.Recordset
Set recordSetTest = CurrentDb.OpenRecordset("Select tblTest.Test,
tblTest.Name, tblTest.TestStatus from tblTest")

excelRowPosition = 1
recordSetTest.MoveFirst
Do While Not recordSetTest.EOF
excelWorkSheet.Cells(excelRowPosition, 1) =
recordSetTest.Fields("Test")
excelWorkSheet.Cells(excelRowPosition, 2) =
recordSetTest.Fields("Name")
excelWorkSheet.Cells(excelRowPosition, 3) =
recordSetTest.Fields("TestStatus")

excelRowPosition = excelRowPosition + 1
recordSetTestCase.MoveNext 'move to next record in recordset
Loop

The problem is that TestStatus stores an index to a row in a second
table, where I store TestStatus values. This table has two fields -
Index and a text description of the status. Therefore, I would like to
print this status discription in the excel document rather than the
index, for each row in Test. How might I go about doing this?

Thanks for your help,

Barry.