DAO exporting text from related table (1 Viewer)

[Hdata]

I am trying to export text to a 2010 word document from an access 2010 database with DAO. I have successfully been able to export text from the main table tblLandSales via variables (below) and then subsequently a document. I used the following code:

Dim objWord As Word.Application
Dim docm As Word.Document
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strLandSalesID As String
Dim strPropertyName As String
Dim strClass As String

strSQL = "SELECT * FROM qryLandSales " _
& "WHERE [LandSalesID]=" & [Me!LandSalesID]
rst.Open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockReadOnly
With rst
strLandSalesID = Nz(.Fields("[LandSalesID]"))
strPropertyName = Nz(.Fields("[PropertyName]"))
strClass = Nz(.Fields("[Class]"))

Now I want to export other data from a one to many related table where [fk_tblLandSalesID] is the foreign key in the related table and tblLandSalesID is the primary key in the main table.

Any suggestions and thanks in advance.

 

[pr2-eugin]

You need to JOIN the two tables, just need to edit the SQL. http://msdn.microsoft.com/en-us/library/office/bb208854(v=office.12).aspx

 

[Hdata]

Paul,
Thanks for your information and direction regarding joining my two tables in order to export my text. I tried to accomplish this yesterday and not sure why it did not work. I did not get an error, just no text was exported. I have Microsoft's Access 2010 Programmer's Reference book and I need to read chapters on 11 (DAO), 12 (ADO) and 13 (SQL) before I can ask the correct questions. Although I am currently using DAO for export, this technology does not support exporting lookup fields, maybe there is a an alternate method. Other issues may also come to light. So I should resume my questions in a few days or so.
Thanks for your efforts.

Hdata

 

[pr2-eugin]

Table level lookups is not advisable in DB design. I would advice you to follow the link to remove the Lookups : http://www.btabdevelopment.com/ts/removelookups

Then try again.

 

[Hdata]

I am trying to export text to a 2010 word document from an access 2010 database with DAO. I have successfully been able to export text from the main table tblLandSales via variables (below) and then subsequently a document. I used the following code:

Dim objWord As Word.Application
Dim docm As Word.Document
Dim rst As New ADODB.Recordset
Dim strSQL As String
Dim strLandSalesID As String
Dim strPropertyName As String
Dim strClass As String

strSQL = "SELECT * FROM qryLandSales " _
& "WHERE [LandSalesID]=" & [Me!LandSalesID]
rst.Open strSQL, CurrentProject.Connection, _
adOpenStatic, adLockReadOnly
With rst
strLandSalesID = Nz(.Fields("[LandSalesID]"))
strPropertyName = Nz(.Fields("[PropertyName]"))
strClass = Nz(.Fields("[Class]"))

Now I want to export other data from a one to many related table where [fk_tblLandSalesID] is the foreign key in the related table and tblLandSalesID is the primary key in the main table.

Any suggestions and thanks in advance.

My fix was to create a query based on the two tables (primary & subordinate table) then look at the SQL view and copy this code into my procedure and it worked fine. Thanks Paul. Next I want to export the multiple records from the sub table with a loop and eof clause. I may need help but I need to try it first.
Thanks again Paul. I hang on every word you say. Still working on my previous look-up table failure at the table level. Got to be another way.

 

[CJ_London]

this technology does not support exporting lookup fields

Nothing supports exporting lookup fields - one of the reasons it is not recommended