Collecting data

[Steven811]

Hi complete novice here

I have a form that is made up of one parent form and 4 subforms. I want to use the recordset data on the screen in either Word or a report and I am not sure how to collect it as it is from several tables.

The code that I have works well with one record source and I'm unsure how to modify it to cope with more than one. (encl)

I want to use a cmd button and produce a hard copy of the record set data in either a word doc or report

Are there any other issues that I should be considering?

Thanks in advance.

Steven811 - novice

Function MergeIt()

Dim objWord As Word.Document
Dim strSQL As String
Dim strSQL2 As String

Set objWord = GetObject("C:\MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
strSQL2 = Forms("forCustomerDetails").Form.Controls("CompanyName").Value
strSQL = "SELECT * FROM [tblCustomerDetails]WHERE [tblCustomerDetails.CompanyName] = '" & strSQL2 & "'"
'MsgBox (strSQL)
objWord.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\Admin\Desktop\EC_EX\DbFiles\ec-ex.mdb", _
LinkToSource:=True, _
Connection:="TABLE tblCustomerDetails", _
SQLStatement:=strSQL
' Execute the mail merge.
objWord.MailMerge.Execute
objWord.Close
End Function

 

[dan-cat]

To combine more than one table within a SQL string you will need to learn the SQL 'JOIN' syntax.

As you are just starting out I would use the query design grid (QBE) that access provides and then switch to 'SQL' view once you have designed your query. This will give you the SQL code of the query you have just built.
From there you can practice and learn how to build more complex SQL strings.

I think some people never bother to learn SQL at all but just rely on the QBE to do the donkey work for them. It won't get them any street creds though