Need help creating a report that auto fills information based on client name

[barbados2010]

Hi all, I am in the middle of building a database for my real estate/property management company. What I need to be able to do is have Access create a report and the report generated would be a contract. I will have a few spaces blank on the contract where a person would write their name, date etc... I can't manually enter a clients name so I need access to create the contract and automatically add the clients name/info into the field. Would this be achieved by a primary/foreign key?

Is there a way to have access automate this? Please let me know the best way to do this. I'm using Access 2007.

 

[Samantha]

I would use code to merge to MSWord to accomplish it. Here's my code, start by building a template and storing it in the same file with your database.

Private Sub MergeBttn_Click()
'Declare variables for storing strings.
Dim AddyLineVar As String, SalutationVar As String
Dim DeliveryAdd As String, PmName As String, CustCompany As String
Dim ProjectDescriptionLine As String

'Start building AddyLineVar, by dealing with blank last name fields.
If IsNull([sfrmContacts].[Form]![Last]) Then
AddyLineVar = [Company]
'Just set salutation to generic.
SalutationVar = "Sir or Madam"
Else
AddyLineVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last])

'Add Company on after name.
If Not IsNull([Company]) Then
AddyLineVar = AddyLineVar & vbCrLf & [Company]
End If

'Salutation will be customer's last name
SalutationVar = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![Last]) & ", "
End If

'Start building DeliveryAdd, by dealing with blank email fields.
If IsNull([sfrmContacts].[Form]!) Then
DeliveryAdd = "Fax: " & [sfrmContacts].[Form]![BusinessFax]
Else
DeliveryAdd = "E-mail: " & [sfrmContacts].[Form]![Email]
End If
'Add line break and Address lines.
AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![Address])
'Tack on line break then city, state, and zip.
AddyLineVar = AddyLineVar & vbCrLf & ([sfrmContacts].[Form]![City]) & ", "
AddyLineVar = AddyLineVar & ([sfrmContacts].[Form]![State]) & " " & ([sfrmContacts].[Form]![ZipCode])
CustCompany = ([sfrmContacts].[Form]![Title]) & " " & ([sfrmContacts].[Form]![First]) & " " & ([sfrmContacts].[Form]![Last]) & vbCrLf & [Company]

'Set PMName to first and last name
PmName = [sfrmPMTitles].[Form]![FirstName] & " " & [sfrmPMTitles].[Form]![LastName]

'Declare an instance of MS Word.
Dim Wrd As New Word.Application
Set Wrd = CreateObject("Word.Application")

'Specify the path and name to the word document.
Dim MergeDoc As String
MergeDoc = Application.CurrentProject.Path
MergeDoc = MergeDoc & "\WordFormLetter.dotx"

'Open the word document template, make it visible.
Wrd.Documents.Add MergeDoc
Wrd.Visible = True


'Replace each bookmark with current data.
With Wrd.ActiveDocument.Bookmarks
.Item("ProjectDescription").Range.Text = Me.ProjectDescription
.Item("AddressLines").Range.Text = AddyLineVar
.Item("Salutation").Range.Text = SalutationVar
.Item("Phone").Range.Text = sfrmContacts.Form!Phone
.Item("JobNumber").Range.Text = JobNumber
.Item("PMInitials").Range.Text = Manager
.Item("Typist").Range.Text = [Entered_By]
.Item("JobNumber2").Range.Text = JobNumber
.Item("Phone2").Range.Text = sfrmContacts.Form!Phone
.Item("BusinessFax2").Range.Text = DeliveryAdd
.Item("Delivery").Range.Text = DeliveryAdd
.Item("ProjectDescription2").Range.Text = ProjectDescription
.Item("ProjectManager").Range.Text = PmName
.Item("PMTitle").Range.Text = [sfrmPMTitles].[Form]![Title]
.Item("CustCompany").Range.Text = CustCompany
.Item("ProjectManager2").Range.Text = PmName
.Item("PMTitle2").Range.Text = [sfrmPMTitles].[Form]![Title]
.Item("ContractAmount").Range.Text = FormatCurrency(([sfrmContractAmount].[Form]![ContractAmount]), 2)
End With

MsgBox "Your Document is Ready." & vbCrLf & "Please re-name your document and save to the job file.", vbOKOnly, "Successful"

End Sub

 

[godsspeed]

if youre not familiar with VBA, start this in MS word with the MailMerge tool.
here is a post i made a little bit ago explaining to someone else how to setup the word mailmerge doc for thier needs, should be fairly similar for you. ei: rather than selecting recepients from an excel spreadsheet, you'd select them from an access db.http://www.access-programmers.co.uk/forums/showthread.php?t=232143

 

[barbados2010]

What would the process be if doing it with VBA?

 

[Samantha]

Using VBA you would create your form and add a button with an on-click event. Then you would enter your code into VBA. (at some point in this you have to make a template in office setting the bookmarks where the varible will change according to your database fields)
It may help to buy a programming book that will explain in further detail I have "MS Office Access 2010 Programmers Reference" it's been the best 30$ spent. I'm not sure how much work it is to set up the mail merge or if it has to be everytime, or if you can just create one "contract" for one selected reciepient. So that may be a good option depending on your needs, good luck