Working with contracts through Access

[fleutius]

Hello.

I am creating a database for our company, and I am trying to figure out if it is possible to manage our contracts through the database.
We deal with entertainers in clubs, and we normally work through standardised contracts for each club.
In other words, what I am hoping for is the following:

Having a standard contract for each customer, where the information about the entertainer is written to the document directly through access.
I am hoping to create a button in the menu, where I can choose which club i want to create the contract from, and then just type the primary key for the entertainer.

At the same time, i want to be able to pick the start date, and end date of the contract, and be able to preview the contract, before it is saved as a pdf document.
If possible, i would like the club table, to maintain some data about the contract for later use.

I am, however, fairly new to Access, but I am hoping that the request is not completely crazy.


Please let me know if more information is needed, and I will provide it to the best of my ability

[ranman256]

I too use contracts. I have tClients table, tContracts table
tContrats has:
------------
[ContractID] as autonum
[ClientID] (FK) from the tClient table
[StartDate]
[EndDate]
[ContractType]
..misc fields


Make some reports in tReports
choose a report , and a client and make pdfs.

[fleutius]

That whole setup looks good, however i might be just a bit too new into Access to understand it fully

Since I have all of the standard contracts i need allready in Word format, it will be easy for me to insert the merge fields into them, and save them as templates for each customer.

However I am getting stuck on what seems to be a simple problem.

In the contract i need the following information to be merged from my access database
SceneName
RealName
DateOfBirth

These 3 comes from my DanserT table, where i store all information regarding the artists.
I then have a CostumerT table with all costumer informations, where i will (hopefully) be storing the standard contract template.

I am guessing that i will need a Table for the contracts, where the specific Danser from DanserT will be chosen, and where a Start and End date for the contract will be selected.

My questions then arrive:
Would i be able to make a form, where i could select the above things, insert them into the template, and save the document?
Currently my Primary key for my CostumerT table is ClubID wich is an Autonumber, however i wish to choose the club based on the club name (Which I cannot guarantee is Unique), can i add a relationship without using the Primary Key?
Same goes for my DanserT table.

Any other good tips in regards to the relationsships needed to make this work

[ranman256]

Put a combo box on the form (have the wizard on..) it will ask you where to get the data for the combo.
Pick the table and the fields. Make sure you pick the Club name to be in the combo too.
If you havent gone too fare building this db, you dont need autonum as the key...use the club name. (unless you get a franchise then you wont be able to store duplicate clubnames , hence autonum)

But once you get the form to pick the client and other stuff, access can call Word template , and build the merge.
The merge would use a query that looks at the form and the items you picked.

[ranman256]

You can build the merge using the query (here it is qsCheers)

Sub MergeIt ()
' MergeIt Macro
Dim wrd As Word.Application
Dim vFile
vFile = "\\myServer\amer\IT\DEAR mailmerge.docx"
Set wrd = CreateObject("Word.Application")
With wrd
   .Documents.Open vFile
   .Visible = True
   
'set the data connection
    .ActiveDocument.MailMerge.OpenDataSource Name:= _
        "\\myserver\amer\db1.mdb", ConfirmConversions:= _
        False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
        Connection:="QUERY qsCheers", SQLStatement:="SELECT * FROM [qsCheers]", _
        SQLStatement1:="", SubType:=wdMergeSubTypeOther
        
'run merge
    With .ActiveDocument.MailMerge
        .Destination = wdSendToNewDocument
        .SuppressBlankLines = True
        With .DataSource
            .FirstRecord = wdDefaultFirstRecord
            .LastRecord = wdDefaultLastRecord
        End With
        .Execute Pause:=False
    End With
End With
Set wrd = Nothing
End Sub