UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Report Generation From Form, Access 2010    
 
   
joeyc0323
post Mar 17 2017, 12:06 PM
Post#1



Posts: 66
Joined: 2-April 12



I have a database with a form which needs completed by each user. After completion the use can generate reports to send outside our organization. We have 8 form letters which gets populated by information from the form. I have a command button which will generate the form letter; however, I need to have eight separate command buttons (one for each letter). Is it possible to have one command button which will open a dialog box allowing the user to choose which of the 8 letters they would like to complete and then have the letter generated based on the one they choose? If so, how would I program this? I have done a lot of programming for this DBS in VBA, but I am not an VBA expert so any scripting help is greatly appreciated.

I am using Access 2010.

Thanks!
Go to the top of the page
 
theDBguy
post Mar 17 2017, 12:12 PM
Post#2


Access Wiki and Forums Moderator
Posts: 69,940
Joined: 19-June 07
From: SunnySandyEggo


Hi,

One approach you might consider is adding a Combobox on your form next to the button to generate the report. The combobox will list all the reports the user can select to generate. The button will then use the value from the combo to determine which report to generate.

Hope it helps...

--------------------
Go to the top of the page
 
joeyc0323
post Mar 17 2017, 12:27 PM
Post#3



Posts: 66
Joined: 2-April 12



I like that idea but I have no idea how to program it.
Go to the top of the page
 
theDBguy
post Mar 17 2017, 12:29 PM
Post#4


Access Wiki and Forums Moderator
Posts: 69,940
Joined: 19-June 07
From: SunnySandyEggo


Well, when you say "generate" report, did you mean to "open" it? If so, you can use the OpenReport method on a button's Click event.

--------------------
Go to the top of the page
 
joeyc0323
post Mar 17 2017, 01:42 PM
Post#5



Posts: 66
Joined: 2-April 12



I have coding in a command button currently to open the report template and fill in data based on bookmarks in the template:

Private Sub cmdMerge_Click()

Dim appWord As Word.Application
Dim doc As Word.Document
Dim MyFilter As String

MyFilter = "[Ticket_ Number] = " & Me.[Ticket_ Number]

'Avoid error 429, when Word isn't open.

On Error Resume Next

Err.Clear

'Set appWord object variable to running instance of Word.

Set appWord = GetObject(, "Word.Application")

If Err.Number <> 0 Then

'If Word isn't open, create a new instance of Word.

Set appWord = New Word.Application

End If

Set doc = appWord.Documents.Open("S:\PAsecureID_WORK IN PROGRESS\Word Templates\One_Student_Retired_ID.dotx", , True)

With doc

.FormFields("fldContact").Result = Me![Contact Name]

.FormFields("fldStudentName").Result = Me!Student_Name

.FormFields("fldIncorrect").Result = Me!Retired_PAsecureID

.FormFields("fldCorrect").Result = Me!Active_PAsecureID

.FormFields("fldAUN").Result = Me!cmbLEA_Name

.FormFields("fldLEAName").Result = Me!txtLEA_Name

.FormFields("fldSchoolYears").Result = Me![School_ Years_ Affected]

.FormFields("Full_Name").Result = Me!Full_Name

.FormFields("fldTitle").Result = Me!Title

.FormFields("fldDivision").Result = Me!Division

.FormFields("fldCompany").Result = Me!Company

.FormFields("fldAddress").Result = Me!Address

.FormFields("fldCity_State_Zip").Result = Me!City_State_Zip

.FormFields("fldPhone_TBL_Users").Result = Me!Phone_TBL_Users

.FormFields("fldFax").Result = Me!Fax

.FormFields("fldEmail_Address").Result = Me!Email_Address

.FormFields("fldWebsite").Result = Me!Website

.Visible = True

.Activate

End With

Set doc = Nothing

Set appWord = Nothing

Exit Sub

errHandler:

MsgBox Err.Number & ": " & Err.Description

End Sub

I would like to incorporate this same procedure into the command button/drop-down. I created my drop-down and have all my templates parsed out with the proper bookmarks, but I'm not sure how to link the drop-down with the command button and have the command button generate the report template chosen in the drop-down box.
Go to the top of the page
 
theDBguy
post Mar 17 2017, 01:44 PM
Post#6


Access Wiki and Forums Moderator
Posts: 69,940
Joined: 19-June 07
From: SunnySandyEggo


Hmm, looks like we may have a little terminology confusion here. You don't seem to be opening a "report" but more like generating a Word document, correct? I mean, do you have a report object in Access you're using for this procedure? If so, I didn't see it in your code.

--------------------
Go to the top of the page
 
joeyc0323
post Mar 17 2017, 02:22 PM
Post#7



Posts: 66
Joined: 2-April 12



I am generating a Word document from a network drive. Currently you click the command button (cmdMerge) and it opens a Word template (S:\PAsecureID_WORK IN PROGRESS\Word Templates\One_Student_Retired_ID.dotx" and fills in the bookmarked fields. This can then be saved, printed, or emailed to a user outside our agency.
Go to the top of the page
 
theDBguy
post Mar 17 2017, 02:27 PM
Post#8


Access Wiki and Forums Moderator
Posts: 69,940
Joined: 19-June 07
From: SunnySandyEggo


Okay, so when you said there are eight letters, are you talking about eight different templates?

--------------------
Go to the top of the page
 
joeyc0323
post Mar 17 2017, 03:01 PM
Post#9



Posts: 66
Joined: 2-April 12



Yes that is correct, eventually; we will only be using 5 at this point. Currently we only have one connected to the command button but I have now added a combo box with all the letters names. Now I need to link the combo box to the command button and change the command button to run whatever letter is chosen in the combo box.
Go to the top of the page
 
theDBguy
post Mar 17 2017, 03:18 PM
Post#10


Access Wiki and Forums Moderator
Posts: 69,940
Joined: 19-June 07
From: SunnySandyEggo


Hi,

If the names of the letters are different than their file names (and folders), then your combobox need to list both of them. You can hide the filepath column so as not to confuse the user. For example, let's say you have the following two templates:

S:\PAsecureID_WORK IN PROGRESS\Word Templates\One_Student_Retired_ID.dotx
S:\PAsecureID_WORK IN PROGRESS\Word Templates\Two_Teacher_Active_ID.dotx

Then, your combobox might have the following items:

Letter for Students | S:\PAsecureID_WORK IN PROGRESS\Word Templates\One_Student_Retired_ID.dotx
Letter for Teachers | S:\PAsecureID_WORK IN PROGRESS\Word Templates\Two_Teacher_Active_ID.dotx

Now, given the above combobox settings, you'll need to bind it to the filepath column. Then, you can modify your code to replace the following line:

Set doc = appWord.Documents.Open("S:\PAsecureID_WORK IN PROGRESS\Word Templates\One_Student_Retired_ID.dotx", , True)

with something like this:

Set doc = appWord.Documents.Open(Me.ComboboxName, , True)

Hope it helps...

--------------------
Go to the top of the page
 
joeyc0323
post Mar 20 2017, 12:49 PM
Post#11



Posts: 66
Joined: 2-April 12



I took your advice and create my combo box with two columns; one with the recognizable names for my users and the second is the full path on the network to the individual reports. I then changed my VBA code and replaced the line Set doc = appWord.Documents.Open("S:\PAsecureID_WORK IN PROGRESS\Word Templates\One_Student_Retired_ID.dotx", , True) with Set doc = appWord.Documents.Open(Me.cmbReports, , True).

My column one names are:
One Student Retired ID
One Student Retired ID NOPIMS
One Student Retired Multiple ID
Shared Existing ID
Shared Newly Created ID

My column two paths are:
S:\PAsecureID\Word Templates\One_Retired_ID.dotx
S:\PAsecureID\Templates\One_Retired_ID_NOPIMS.dotx
S:\PAsecureID\Word\One_Retired_Multiple_ID.dotx
S:\PAsecureID\Word\Shared_ExistingID.dotx
S:\PAsecureID\Word\Shared_Newly_CreatedID.dotx

I have it bound to column 1.

My entire new code is:
Private Sub cmdMerge_Click()

Dim appWord As Word.Application
Dim doc As Word.Document
Dim MyFilter As String

MyFilter = "[Ticket_ Number] = " & Me.[Ticket_ Number]

'Avoid error 429, when Word isn't open.

On Error Resume Next

Err.Clear

'Set appWord object variable to running instance of Word.

Set appWord = GetObject(, "Word.Application")

If Err.Number <> 0 Then

'If Word isn't open, create a new instance of Word.

Set appWord = New Word.Application

End If

Set doc = appWord.Documents.Open(Me.cmbReports, , True)

With doc

.FormFields("fldContact").Result = Me![Contact Name]

.FormFields("fldStudentName").Result = Me!Student_Name

.FormFields("fldIncorrect").Result = Me!Retired_PAsecureID

.FormFields("fldCorrect").Result = Me!Active_PAsecureID

.FormFields("fldAUN").Result = Me!cmbLEA_Name

.FormFields("fldLEAName").Result = Me!txtLEA_Name

.FormFields("fldSchoolYears").Result = Me![School_ Years_ Affected]

.FormFields("Full_Name").Result = Me!Full_Name

.FormFields("fldTitle").Result = Me!Title

.FormFields("fldDivision").Result = Me!Division

.FormFields("fldCompany").Result = Me!Company

.FormFields("fldAddress").Result = Me!Address

.FormFields("fldCity_State_Zip").Result = Me!City_State_Zip

.FormFields("fldPhone_TBL_Users").Result = Me!Phone_TBL_Users

.FormFields("fldFax").Result = Me!Fax

.FormFields("fldEmail_Address").Result = Me!Email_Address

.FormFields("fldWebsite").Result = Me!Website

.FormFields("fldDOB").Result = Me!DOB

.FormFields("fldRFN").Result = Me!txtRFN

.FormFields("fldSFN").Result = Me!txtSFN

.FormFields("fldSDOB").Result = Me!Shared_DOB

.FormFields("fldStudentName2").Result = Me!Student_Name

.FormFields("fldSFN2").Result = Me!txtSFN

.FormFields("fldCorrect2").Result = Me!Active_PAsecureID

.FormFields("fldIncorrect2").Result = Me!Retired.PAsecureID

.Visible = True

.Activate

End With

Set doc = Nothing

Set appWord = Nothing

Exit Sub

errHandler:

MsgBox Err.Number & ": " & Err.Description

End Sub


I've saved everything and tried selecting a report on the form and clicking the command button to run the report and nothing happens. I've tried changing the bound column to 2 as well to see if that changed my results. I have hidden the second column so it doesn't confuse my users.

Any ideas?

Thanks for all your help!
Joe
This post has been edited by joeyc0323: Mar 20 2017, 12:54 PM
Go to the top of the page
 
theDBguy
post Mar 20 2017, 01:09 PM
Post#12


Access Wiki and Forums Moderator
Posts: 69,940
Joined: 19-June 07
From: SunnySandyEggo


Hi Joe,

If the paths are in the second column of your combobox, then the Bound Column property value should be 2. Have you tried stepping through the code as it executes?

--------------------
Go to the top of the page
 
River59
post Mar 20 2017, 01:13 PM
Post#13



Posts: 1,139
Joined: 7-April 10
From: Detroit, MI


Refer to the combo box column property:

Set doc = appWord.Documents.Open(Me.cmbReports.Column(0), , True) ... or whichever column contains the full path remembering that the first column is always zero (0).

--------------------
Remember ... Armstrong, Aldrin and Collins flew to the moon and back with a computer system less complex than a modern, programmable toaster ...
Go to the top of the page
 
joeyc0323
post Mar 20 2017, 01:27 PM
Post#14



Posts: 66
Joined: 2-April 12



I set it as:

Set doc = appWord.Documents.Open(Me.cmbReports.Column(1), , True)

The column with the network path is the second column but is 1 because the first column is 0.

It will now open the second report on the list but no others.

My column are:
One Student Retired ID
One Student Retired ID NOPIMS
One Student Retired Multiple ID
Shared Existing ID
Shared Newly Created ID

I tried changing the column to 0 or 2 to see if that would change the results, but none of the reports will run then.

Thanks,
Joe
Go to the top of the page
 
joeyc0323
post Mar 20 2017, 02:40 PM
Post#15



Posts: 66
Joined: 2-April 12



I figured out the problem. When I was copying/pasting the network path, it added an extra space before the \ and the template name. It works now but I have to click on the generate command button twice for some reason.
Go to the top of the page
 
theDBguy
post Mar 20 2017, 03:07 PM
Post#16


Access Wiki and Forums Moderator
Posts: 69,940
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Glad to hear you figured it out. It might still be a good idea to step through the code to find out why you have to click the button twice. Cheers!

--------------------
Go to the top of the page
 
joeyc0323
post Mar 20 2017, 03:15 PM
Post#17



Posts: 66
Joined: 2-April 12



I'm not sure how exactly to step through the code. Could you please explain that to me?

Thanks again!
Joe
Go to the top of the page
 
theDBguy
post Mar 20 2017, 03:18 PM
Post#18


Access Wiki and Forums Moderator
Posts: 69,940
Joined: 19-June 07
From: SunnySandyEggo


Hi Joe,

See if this Wiki article helps...

--------------------
Go to the top of the page
 
joeyc0323
post Mar 21 2017, 09:36 AM
Post#19



Posts: 66
Joined: 2-April 12



Thanks!
Go to the top of the page
 
theDBguy
post Mar 21 2017, 10:00 AM
Post#20


Access Wiki and Forums Moderator
Posts: 69,940
Joined: 19-June 07
From: SunnySandyEggo


You're welcome. Hope it helps...

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd June 2017 - 05:08 AM