Need some access help

[diasflac]

I haven't done anything like this for 10 years and it seems to all be forgotten:

I will tell you what I have at the moment:

5 tables.

Supplier (Supplier Number, Supplier Name, Currency, Payment Terms, Contact Person, Contact Number and Supplier address)

Inco Terms (Inco terms, Inco terms description)

R&D (Name, Telephone number, Email address, Office Address)

Requestor (Name, Contact Number, Department, E-mail address, Job title, office address, WA or CP?)

RFQ (RFQ Reference number, RFQ Title, RFQ Date, Supplier Name, Requestor, RFQ Deadline, R&D Engineer Responsible, Inco Terms, Validity of Prices, Currency, Exchange rate, Opt out date, Date Response received, attackments).

I have 1 Form

Basically the user fills in fields of the RFQ table either typing in or matching details from the other tables and they save which generates a new reference number and saves the record.

What I also have is another button for "Print". I want this to fill in certain fields in an already created Word template. I have used mail-merge to do this however it only lets me select 1 table but some of the information I need is in other tables but needs to be looked up from fields in the RFQ table. I have linked the fields in each table via relationship yet they're not giving me the option to select them.

For example, I have the RFQ field Requestor which is the name of the requestor. I link this with the requestor table but in the Word Template I need the job title, address and telephone number looked up from that 1 record in the RFQ table.

Can anyone offer any assistance or point me in the right direction. I've read a general guide but it doesn't answer my query, a more specific guide would be a help in this case.

 

[pr2-eugin]

Hello diasflac, Welcome to AWF..

I am unable to see any relationships between the tables, if you have a well established relationship, you can make JOIN query and use that query as the source for the MailMerge document..

 

[diasflac]

Hello diasflac, Welcome to AWF..

I am unable to see any relationships between the tables, if you have a well established relationship, you can make JOIN query and use that query as the source for the MailMerge document..

Thank you for responding.

The RFQ table only has 1 reference to each table I have made a relationship between those. The problem with the Query is I only need the Word Template to display the values from the new record, to be honest I'm not actually sure mail-merge is the correct thing to use here.

I want the database form to work so a user will enter the information, save it and be given a reference number, then print it and get a letter generated that is ready to e-mail to the supplier without further modification as all the information on the letter is already in the database.

 

[pr2-eugin]

I am lost..

So let me throw this suggestion, see if this is something you are looking for.. First create a Query that will filter the result that you want to send out.. Say..

SELECT tblName1.FirstName, tblName2.JobTitle
FROM tblName1 INNER JOIN tblName2 ON tblName1.commonID = tblName2.sameID
WHERE tblName2.someReference = 'New Record'

Then save this query by giving it a name.. You can then export this query (in an Excel format) that will form the DataSource for the Word document..

 

[diasflac]

Apologies if I'm making little sense or sound like I'm being antagonizing it's not my intention, I just haven't used microsoft access in 10 years, I've explained this to my manage but what can you do?

The database as a whole is going to be used by multiple members of staff with absolutely no knowledge of microsoft access, it has to be easy to use and if it could be restricted to forms it would be.

You query suggestion does work but the feedback I've had from my manager was that it needs to produce the word document from the form screen.

The idea is, The user fills in the form and presses save which adds a new record and gives it a reference number. I have a command button next to it with a macro I'm yet to test which uses a word template to fill in all the information on the document from the current record on display.

The problem I have is the information filled in the form is only part of the information, the user will select the supplier name but all the contact information is stored on the supplier table not the RFQ table. The user will then select an R&D Engineer who's contact information is also stored on it's own table.

I tried to do the mail merge set up but it only lets me select 1 table or query. Unless make the RFQ table contain everything forcing the user to fill in a lot of information I can't do it this way.

All I want is the user to click print and it open a word document from the template with everything filled in, this is the bit I'm stuck on. The only reason I don't want to use a query is because it adds to the work the user will need to know the database won't be as easy to use. Even if I use a query, will that only mail merge for 1 record?

I hope that makes much sense, I apologise if I'm still being unclear.

 

[pr2-eugin]

Trust me I know what you mean, with managers.. Ha ha.. Well even this can be automated.. All you need is to Create the Query as I suggested.. Get the Reference number from the Form..

SELECT tblName1.FirstName, tblName2.JobTitle
FROM tblName1 INNER JOIN tblName2 ON tblName1.commonID = tblName2.sameID
WHERE tblName2.someReference =[COLOR=Red][B] Forms![FormName]![RefernceControlName][/B][/COLOR]

Then use the button's On Click event,

Private Sub printBtn_Click()
    DoCmd.OutputTo acOutputQuery, "yourQueryName", acFormatXLSX, "C:\yourQueryName.xlsx"
    MergeToWord "C:\pathToTemplate.doc", "C:\yourQueryName.xlsx"
End Sub

Where MergeToWord is a custom function that I randomly searched on the internet, and edited it a bit..

Public Sub MergeToWord(strDocName As String, sourceName As String)
    Dim objApp As Word.Application, tmpSheetName As String, saveAsName As String
    Dim splitPos As Long

    'Open Mailmerge Document, Start Word
    Set objApp = CreateObject("Word.Application")
    DoCmd.Hourglass True
    
    'This step will obtain the sheet name from the EXCEL file.
    tmpSheetName = Mid(sourceName, InStrRev(sourceName, "\") + 1)
    tmpSheetName = Mid(tmpSheetName, 1, InStr(tmpSheetName, ".") - 1)
    
    'This is the File Path & File Name that needs to be Saved as.
    saveAsName = "C:\MailMergeDoc.docx"
    
    With objApp
        .Visible = True                             'Make it visible
        .Documents.Open strDocName                  'Open the Mailmerge Document
        .ActiveDocument.MailMerge.OpenDataSource Name:=sourceName, ReadOnly:=True, LinkToSource:=True, SQLStatement:="SELECT * FROM [" & tmpSheetName & "$]"
        .ActiveDocument.MailMerge.Execute Pause:=False
        .ActiveDocument.SaveAs saveAsName
        .ActiveDocument.Close
        .Quit SaveChanges:=wdDoNotSaveChanges       'Avoid Saving over your template & Close All WORD Documents
    End With
exitOnErr:
    Set objApp = Nothing
    Exit Sub
ErrorHandler:
    MsgBox "Some error occured !! " & vbNewLine & Err.Number & " : " & Err.Description
    DoCmd.Hourglass False 'Cursor back to normal
    Resume exitOnErr
End Sub

Copy the MergeToWord in a Module, and Compile it, to see if you have any error.. and then see if this works for you.. This needs a reference to Microsoft Word xx.x Object Library, under Tools->Reference

 

[diasflac]

Everything works except for the print button command.

On MergeToWord it says "Compile error expected process or function, not module"

 

[pr2-eugin]

Make sure the Module is not named as MergeToWord, name the module something different than the function name.. maybe something like.. commonModule or allModCode

 

[diasflac]

Sorry to keep bothering you with every little error, not sure why this is happening whether it's down to being Access 2003 or not.

In the module the line :

Dim objApp As Word.Application, tmpSheetName As String, saveAsName As String

It says user defined type not defined... It compiled fine before I changed it's name.

 

[pr2-eugin]

As mentioned earlier,

This needs a reference to Microsoft Word xx.x Object Library, under Tools->Reference

 

[diasflac]

Hi Paul,

Thank you very much, everything works now exactly as it should except for:

I have already manually input the mergefields from the query, yet 4 fields come up with a box saying the mergefields are invalid, I can either remove them or replace them with valid fields. The valid fields are actually the same ones I originally put into the document?

When I click the print button I get asked what format to use for the spreadsheet, obviously the answer is excel 97-2003, is there anything that can be added to remove the so it chooses that option automatically.

I really appreciate the help.

 

[pr2-eugin]

Try adding the Merge fields manually for the first time.. Save it.. Then when the code runs, it will not come across any problem..

The reason it comes up with a choice for Excel is because in the code I provided it would export to XLSX format.. Change it to, acFormatXLS..

 

[diasflac]

Try adding the Merge fields manually for the first time.. Save it.. Then when the code runs, it will not come across any problem..

The reason it comes up with a choice for Excel is because in the code I provided it would export to XLSX format.. Change it to, acFormatXLS..

Hi Paul,

The XLSX change worked, I've narrowed down the merge fields to be in the query, it seems when I add too many fields into it, it renames some of the columns with TableName.Column Name - It's only on these fields and changes which ones if I re-do the query. I should be able to solve that.

I've modified the module a little because the hourglass kept sticking so I've removed the hourglass funtion. The closing and save as were being a bit of a nightmare (Closing soon after merging and opening multiple documents).

Now there are two final things you'll be glad to know.

When the merge starts it asks to select tables but there is nothing there, you have to go into options then also check system tables for the query to show up.

The second things is, a seperate document with the save as name comes up after the document has done merging which just says mailmerge error - merge field is locked, repeated over and over. I am hoping this is just because of the unrecognised fields so I'll let you know once I've fixed that.

Thanks again.

 

[diasflac]

I've created another form, well a copy of the form in regards to this thread. This is simply to view and amend an RFQ. It has a print button too, however even though the code is the same as it's using the same query and form data I get this error message.

"Run-time error '2302':

Open Switchboard can't save the output data to the file you've selected"

Then it highlights the line:

DoCmd.OutputTo acOutputQuery, "RFQ Query", acFormatXLS, "H\Request for Quotation\Test.xls"

Switchboard is the opening form which pops up to select what you want to do, I don't see how it has anything to do with it. The code is exactly the same because it refers to exactly the same function, queries and tables...

 

[pr2-eugin]

You are missing the colon after the Drive letter...

DoCmd.OutputTo acOutputQuery, "RFQ Query", acFormatXLS, "H[COLOR=Red][B]:[/B][/COLOR]\Request for Quotation\Test.xls"

 

[diasflac]

Now I feel silly.

Now the database is completed I've begun testing everything. There are a couple of problems with the export to word.

The code uses all results of the query and mailmerges all of them in the word document, even if I select a different record you just see the first record. Is there anyway to have it specify you only want to mail merge the record on the form when you click print?

 

[pr2-eugin]

This should be performed before the file is exported.. i.e. the Query should do all filters and then the file should be exported.. Once the data has been exported, doing what you wish to do would be too late, and would more of a manual work..

 

[diasflac]

How do you tell the query to filter to the result you either just created or were viewing the form?

Sorry for pestering, my deadline to have something that works is tomorrow 10am, I should ask for a pay rise

 

[pr2-eugin]

I do not understand.. reading through your posts previously suggests that, you already have this filter set.. Has the requirement changed since then?

 

[diasflac]

I do not understand.. reading through your posts previously suggests that, you already have this filter set.. Has the requirement changed since then?

I'll be honest, I never checked your module, it worked so I left it at that. It all seemed to work but I only had one record to use as an example, it's not until I've finished all the other forms and done a last run through to make sure everything is working as it should that I've realised there is no filter. There are two forms now, one create and one to view and amend. So it would have to filter to the result you're currently viewing in one of them. You cannot have both open at once because I have a code in place to stop that ever happening.