By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,064 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

Setting Row Source via VBA

P: n/a
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.

May 9 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.
May 9 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.