Create a Report from a query and then personalise the report for each client - possible?

[Moss100]

Hello,

I have a table which holds 'Terms and Conditions' for a contract.

I have created a Query from this and then a Report. So far so good.

I now want to add into that report some personal information for each client.

So I have a query for the report called - qryTandC

and

a query for the cleints called - qryClient

There is no relationship between the two tables.

Is it possible to create a personal report for each client using this approach?

If not what's the best way to approach this?

Many thanks
Mark

 

[jedraw]

Are the Terms and Conditions consistent for all Contracts? That is, is it boiler plate with customization for a few fields. If so, then you may get some insight from this code/sample.

' ----------------------------------------------------------------
' Procedure Name: testStr
' Purpose: To manipulate a standard text string to incorporate first and last names based on another source (table/query)
'  Sort of boiler plate/place holders with custom areas for  letters
' Procedure Kind: Function
' Procedure Access: Public
' Parameter FName (String): FirstName
' Parameter LName (String): LastName
' Return Type: String
' Author: Jack
' Date: 05-Feb-19
'
'using a standard text layout with 2 unique strings ("XK?A" and "XK?Z")
'representing place holders for FName and LName and substituting these
'with "real values" input as parameters to this function.
' ----------------------------------------------------------------
Function testStr(FName As String, LName As String) As String
10  On Error GoTo testStr_Error
    Dim str As String, working As String
20  str = "Enclosed please find the above-referenced XK?A XK?Z's fingerprint card and " & vbCrLf _
        & "attached Virginia criminal record that has been researched by the FBI " & vbCrLf _
        & "and the Virginia State Police in pursuant to Virginia Code §37.2-416  (a), (b), and/or ©.  " & vbCrLf _
        & "As a result of this information and an additional review done by the " & vbCrLf _
        & "DBHDS’ Background Investigations Unit (BIU) the above-referenced XK?A XK?Z " & vbCrLf _
        & "is ELIGIBLE for employment in a direct consumer care position with your organization."
30  working = str
40  working = Replace(working, "XK?A", FName)
50  working = Replace(working, "XK?Z", LName)
60  testStr = working

70  On Error GoTo 0
80  Exit Function

testStr_Error:

90  MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure testStr, line " & Erl & "."

End Function

[highlight #5C3566]TEST ROUTINE[/highlight]

' ----------------------------------------------------------------
' Procedure Name: testit
' Purpose: Test routine to exercise the TestStr function.
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 05-Feb-19
' ----------------------------------------------------------------
Sub testIt()

10  On Error GoTo testit_Error
    Dim i As Integer
    Dim x(3, 1) As String
20  x(0, 0) = "John"
30  x(0, 1) = "vant'Goor"
40  x(1, 0) = "   Mary Anne"
50  x(1, 1) = "Seashell-Smith   "
60  x(2, 0) = "Antonio"
70  x(2, 1) = "Ethan O'lamine"
80  x(3, 0) = "Sue"
90  x(3, 1) = "Hislegzoff"

100 For i = 0 To 3

110     Debug.Print testStr(Trim(x(i, 0)), Trim(x(i, 1)))
120     Debug.Print vbCrLf & "        *****************  " & vbCrLf
130 Next i

140 On Error GoTo 0
150 Exit Sub

testit_Error:

160 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure testit, line " & Erl & "."

End Sub

[highlight #75507B]Partial Result[/highlight]

Enclosed please find the above-referenced John vant'Goor's fingerprint card and
attached Virginia criminal record that has been researched by the FBI
and the Virginia State Police in pursuant to Virginia Code §37.2-416 (a), (b), and/or ©.
As a result of this information and an additional review done by the
DBHDS’ Background Investigations Unit (BIU) the above-referenced John vant'Goor
is ELIGIBLE for employment in a direct consumer care position with your organization.

 

[dhookom]

If there is no relationship between terms and conditions and clients, can we assume the terms and conditions are the same for every client? Otherwise how we would know which terms and conditions applied to which clients.

I expect you could use a subreport based on t and c and add it to a client report.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016