SQLStatement in Connection Statement

[Macjnr]

Hi,

I have MS Access tool used for mail merge. Mail Merge is done using different letter templates e.g all records in the DB with Code A1 go to a letter template A1, Code A2 records are mail merged to template A2 etc.

To perform the mail merge I have created as many queries in Access as there are letter codes and there lies the problem. All the queries are identical except for the name and the filter criteria which is the letter code e.g A1 or A2

Is it possible to have one query that I can pass the letter code via vba ?

Below is the piece of code that calls the 10 queries

Private Sub Command0_Click()
/*some lines of code has been left out*/
Generate_letter "A1", "A1"
Generate_letter "A2", "A2"
Generate_letter "A3", "A3"
Generate_letter "A4", "A4"
Generate_letter "A5", "A5"
Generate_letter "A6", "A6"
Generate_letter "A7", "A7"
Generate_letter "A8", "A8"

End Sub


Public Function Generate_letter(fCriteria As String, XXX As String)
/*some lines of code has been left out*/
With oMainDoc.MailMerge
.MainDocumentType = wdFormLetters
sDBPath = AssignDbPath

.OpenDataSource Name:=sDBPath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatRTF, _
Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0; " & _
"User ID=Admin;" & _
"Password='';" & _
"Data Source=" & sDBPath & ";" & _
"Mode=Read;", _
SQLStatement:="SELECT * FROM `" & fCriteria & "`", SQLStatement1:="", _
Subtype:=wdMergeSubTypeAccess
End With

/*some lines of code has been left out*/
End Function

Appreciate all ideas in advance

Thank you
Chris

 

[namliam]

if it is always going to be A# you can use For...

Dim i as Integer
For I = 1 to 9
    Generate_letter "A"& i, "A1" & i
next i

But it may be better to setup a config table to contain these "types" ? Then us a "Recordset" (Search for DAO.Recordset to find some samples) to loop throught the records and generate the merges.

Good luck !

 

[Macjnr]

Thanks Nam

No it is not always A#. I put that for simplicity when asking for help.

The actual codes are as shown below

Generate_letter "LTRCOL", "LTRCOL"
Generate_letter "LTRCPE", "LTRCPE"
Generate_letter "LTRCFE", "LTRCFE"
Generate_letter "LTRCM1", "LTRCM1"
Generate_letter "LTRCM2", "LTRCM2"
Generate_letter "LTRCH1", "LTRCH1"
Generate_letter "LTRCH2", "LTRCH2"
Generate_letter "LTRCH3", "LTRCH3"
Generate_letter "LTLEGC", "LTLEGC"
Generate_letter "LTUCHQ", "LTUCHQ"

'
'ACE - Loan Clearance
Generate_letter "LTNCLR", "LTNCLR"
'
' 'PLA - Loan Pre Delinquent
Generate_letter "LTPOTH", "LTPOTH"
Generate_letter "LTSCHI", "LTSCHI"
Generate_letter "LTRPLA", "LTRPLA"

' PLF - Loan Front End
Generate_letter "LTSCH3", "LTSCH3"
Generate_letter "LTSCHP", "LTSCHP"
Generate_letter "LTGUAI", "LTGUAI"

' PLM - Loan Mid Range
Generate_letter "LTSDS2", "LTSDS2"
Generate_letter "LTLEGP", "LTLEGP"

' PH1 - Loan Hard Core 1
Generate_letter "LTLEGP", "LTLEGP"
Generate_letter "LTRPH1", "LTRPH1"

' PH2 - Loan Hard Core 2
Generate_letter "LTNBLK", "LTNBLK"

' PWO - Loan Recovery
Generate_letter "LTCOAR", "LTCOAR"
Generate_letter "LTFODR", "LTFODR"
Generate_letter "LTLEGR", "LTLEGR"
Generate_letter "LTNATC", "LTNATC"
Generate_letter "LTNATR", "LTNATR"
Generate_letter "LTREDL", "LTREDL"

Generate_letter "LTHNIV", "LTHNIV"
Generate_letter "LTODNO", "LTODNO"
Generate_letter "LTODDE", "LTODDE"
Generate_letter "LTODXP", "LTODXP"

 

[namliam]

Thanks Nam

No it is not always A#. I put that for simplicity when asking for help.

The actual codes are as shown below

Then go with the table and recordset suggestion.