×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

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

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.

CODE -->

' ----------------------------------------------------------------
' 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 

TEST ROUTINE

CODE -->

' ----------------------------------------------------------------
' 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 

Partial Result

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.

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close