Dynamic cross-tab (and report)

[HansV]

With wildcards, you have to use Like (for inclusion) or Not Like (for exclusion). So try

WHERE (((TblCourse.CName) Not Like "SWP*") AND ...

[Egg 'n' Bacon]

That's done it!

Thanks Hans

[Gflanagan]

I copy and pasted the tables and queries from my main database. The main database is too large. I need help to code the report. My "result" column is max value and the data type will be a number or yes or no , with dates as columning headings. What does intX stand for? An integer?

[HansV]

Welcome to Eileen's Lounge!

intX is an Integer; it is used as the loop index for looping through the values of the Column Header field.

Your crosstab query has 3 Row Heading fields instead of 1, so the code will have to be modified a bit. I'll get back to you.

[Gflanagan]

Thank you so much :) Attached is a pdf of what my report looks like but I am having to manually change the control source from the drop down in Report View.

Department Technology Three Month Report.pdf

[HansV]

I have attached a modified version of the database. I haven't bothered about the layout of the report, hopefully you can adjust that yourself.

TestDynamic1.zip

[Gflanagan]

Wonderful Thank you

[Gflanagan]

Is there a way to keep users from saving over the Report? Is there a way to lock the report? I will be putting it on a share drive where all department will access it.

[HansV]

It's difficult to lock down an Access database completely, but you can do the following:

Select File > Options > Current Database
- Specify a form to be displayed when the database is opened in Display Form.
- Clear the check boxes for Use Access Special Keys, Enable Layout View, Enable design changes for tables in Datasheet View, Display Navigation Pane.
- If you provide a custom ribbon or toolbar, you can clear the check box for 'Allow Full Menus'.

You can bypass these startup options by holding down Shift while opening the database. It's possible to disable that too, but that requires more work.

[Gflanagan]

Ok Thank you

[Gflanagan]

There is one last thing I would like to add to the dynamic report. I added another parameter to my query and added a combo box for a "contact" to select there name. So the report has the contact name at the top with the a text box [Forms]![Dynamic]![cmbContact] and the department [txtDept].

One department per contact 1 to 1.
One "Contact" can have many "Groups"

I would like the groups to be listed at the top of the report as well. At first I thought a multiselect list box would make that work, however, with multiple items selected I would not be able to set a parameter in a text box in the report, it would probably take code?

DynamicNov.zip

[HansV]

Currently, Groups is a single-value field in the Scorecards table. Would you like to replace this with a separate table that lists the groups for each contact?

[Gflanagan]

I believe I was over thinking it. I am going to let the contacts print and preview their reports by "Group". Another department head set up the layout, so Technology has alot of groups. I may create an extra button where they can print and preview by department. Thank you again for all of the help

[HansV]

If you want to keep the combo box, you can simply add the department column to its row source, and add a text box txtDept to the form with control source

=[listGroups].[Column](2)

See the attached version:

DynamicNovReportbyGroup.zip

If you'd like to replace the combo box with a multi-select list box it becomes more complicated.

[Gflanagan]

Right thank you it worked :)

[Gflanagan]

"Save File" command button
So I have been searching. Here is what I would like to do. I would like to Output the report and name it the name of the Report with [Department Name] (this is on my form and the report) and then put the date they saved. I found some code like below. I know the circumstances are different because the report is not bound. So I did not know how it would recognize {Forms]![Dynamic]![txtdept] if it would see it on the report or the form? I realize PDF format does not work unless you have the add in, so I put " ". I hope this is common and maybe simple to create a naming convention.

folderpath = "Libraries\Documents"
folderpath = folderpath & Me.txtDept.Value & "_" & Format(Date, "yyyy_mm_dd") & ".pdf"
DoCmd.OutputTo acOutputReport, [Dynamic], "", folderpath, False


Here is the code I found that is close but does not quite work:


Private Sub Command112_Click()
Dim folderpath As String
On Error GoTo Err_Command112_Click

If IsDate(Me.txtStartDate) = False Then 'not a good date
MsgBox "You must enter a valid Start Date." & vbCrLf & "Please try again.", vbExclamation
Me.txtStartDate.SetFocus
Exit Sub
End If
If IsDate(Me.txtEndDate) = False Then 'not a good date
MsgBox "You must enter a valid End Date." & vbCrLf & "Please try again.", vbExclamation
Me.txtEndDate.SetFocus
Exit Sub
End If

If Nz(Me.txtDept, "") = "" Then 'no description given
MsgBox "You must enter a valid description." & vbCrLf & "Please try again.", vbExclamation
Me.txtDept.SetFocus
Exit Sub
End If

folderpath = "Libraries\Documents"
folderpath = folderpath & Me.txtDept.Value & "_" & Format(Date, "yyyy_mm_dd") & ".pdf"
DoCmd.OutputTo acOutputReport, [Dynamic], "", folderpath, False


End Sub

[HansV]

Which version of Access are you using?

[Gflanagan]

2007

[HansV]

The add-in to save as PDF was only needed in the original release of Office 2007 and in the first service pack SP1. The functionality became built-in in the second service pack SP2 and is still included in the latest service pack SP3. If you're still on the original version or SP1, I strongly advise you to update to SP3; apart from the save-as-PDF feature it includes many security fixes. See How to obtain the latest service pack for the 2007 Office suite.

You should then be able to use

Code: Select all

Private Sub Command112_Click()
    Dim FolderPath As String
    On Error GoTo Err_Command112_Click

    If IsDate(Me.txtStartDate) = False Then 'not a good date
        MsgBox "You must enter a valid Start Date." & vbCrLf & "Please try again.", vbExclamation
        Me.txtStartDate.SetFocus
        Exit Sub
    End If

    If IsDate(Me.txtEndDate) = False Then 'not a good date
        MsgBox "You must enter a valid End Date." & vbCrLf & "Please try again.", vbExclamation
        Me.txtEndDate.SetFocus
        Exit Sub
    End If

    If Nz(Me.txtDept, "") = "" Then 'no description given
        MsgBox "You must enter a valid description." & vbCrLf & "Please try again.", vbExclamation
        Me.txtDept.SetFocus
        Exit Sub
    End If

    FolderPath = CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\"
    FolderPath = FolderPath & Me.txtDept.Value & "_" & Format(Date, "yyyy_mm_dd") & ".pdf"
    DoCmd.OutputTo acOutputReport, "Dynamic", acFormatPDF, FolderPath, False
End Sub

Note that I changed the initial value of FolderPath - it uses the scripting function SpecialFolders to obtain the path to the Documents folder. and it adds a backslash \ after the path. Also, the name of the report is enclosed in quotes.

[Gflanagan]

Ok I see SpecialFolders (that is pretty neat) Thank you I love learning all these things and I think it makes the form/reports so much nicer (this is the best :) Thank you again and especially thank you for explaining about how the code works. The code worked perfect, I guess I have the update, great feeling when it works :)