Generating report from fields chosen in a form

[kroenc17]

Hello all,

I have a form with two combo buttons - the first selects an employee, the second then shows the projects for which they are responsible. Once the user selects the desired project I need a button (or this could be an automatic event upon selection) that generates a report.

I've made a report with the data fields I want on it but I don't know how to set the data source properly. For one, you can not set the data source to a form - from what I can tell.



The data source for the second combo source is:
SELECT Projects.[Project Name], Projects.[Project Manager], Projects.[Project Manager].Value FROM Projects WHERE (((Projects.[Project Manager].Value)=[Forms]![Sean]![Combo7])) ORDER BY Projects.[Project Name];

the afterupdate event for the same combo box (if it's doing anything - I had to mess around for awhile to get the form to work properly) is:
Project Search Requery
which is a Macro. But I also don't see macros in the list of possible data sources for my report.

Please help!!


edit: SOLVED! my 'solution' bolded in reply 10

[pbaldy]

The record source for a report can either be a table or a query (a saved query or SQL entered directly there). In your case, it sounds like you'd want a query that got its criteria from the form, similar to your combo SQL.

FYI, you can have the report get its values from a form. In a textbox, you'd have this as the control source:

=Forms!FormName.TextboxName

[kroenc17]

THANKS! The textbox method works!

However, it only shows the name of the value for which I'm referencing. In my case, this is the Project Name selected on my Form. How can I have other fields of my choice (actually ALL fields from a table titled 'Weekly Status Call' for the chosen Project Name) populate on my report DEPENDANT on the chosen Project Name from the form, which I am able to show via the textbox linked to my combo button?

[pbaldy]

I'd use this method with OpenReport:

BaldyWeb wherecondition

[kroenc17]

Thanks again for your response.. Heres the expression I'm using

DoCmd.OpenReport "Sean", , , "Project Name = '" & Me.Combo2 & "'"

First I tried putting the expression in the macro my command button runs on my "OpenReport" action. I can not save the macro because I am prompted with the following error:
"Access can not parse the expression ... "

Then I tried just adding the expression to the On Click event of my command button instead of it running a macro and I get the error:
"Microsoft Access can not find the object 'DoCmd'. If 'DoCmd' is a new macro or macro group,make sure you have saved it and that you have typed its name correctly."

I'm just about done for the day so I guess this will wait until Monday, but if you have any suggestions or pointers as to what I'm doing wrong I would greatly appreciate it!

[pbaldy]

It sounds like you have that directly in the properties window instead of the VBA editor:

http://www.baldyweb.com/FirstVBA.htm

[kroenc17]

Right on - I added the VBA to the on click event for my command button, below is what's entered in VBA:

Private Sub Command5_Click()
DoCmd.OpenReport "Sean", , , "Project Name = " & Me.Combo2 & "'"
End Sub

When I open my form, select my employee and then the project, I click the button and it 'prints' the report to a .pdf on my desktop. Not sure why it's doing that because I don't see anything that looks like an option for this action but regardless, the data is still not fetched right onto the report pdf... I'll attach what it shows.

Thanks again for your help!

[kroenc17]

I did some reading that mentioned combobox's must be referred to with a column # as well, is this so/what I'm doing wrong?

[pbaldy]

You only need to refer to a column number if the applicable data is not in the bound column of the combo. One of the arguments of OpenReport determines what mode it opens in (Preview, Print, etc). The default if unspecified is to print to the default printer, which in your case I assume is a PDF printer? If you open the report from the Navigation Pane, does it open without the #Name errors?

[kroenc17]

Hah. Again, you're right. That makes complete sense and yes the default printer on this computer is PDF. I actually just managed to figure out how to make this work by making a new query, set the criteria for project name to my combo button in the form, realized I needed to add all the fields in the query that were going to be in the report - and bam! it works!

final questions:
1)How do I make my form automatically close when I click my command button? the OnClick at present is to run a macro.. so how do I attach a close form command to that as well?
2)If I choose an employee in my first combo box, and then select a second employee, the second combo box doesn't update - it still shows the projects for the first employee. How do I fix this?

[pbaldy]

1) Just add a line at the end of the macro using the Close action. Note that this may cause issues with your report, as the query you now base it on needs the form open. If all you're doing is printing, no big deal. If you open in preview mode, the report will open fine but if you then want to print you'll have an issue.

2) I would expect the after update event of the first combo to be requerying the second. Is that not the case?

[kroenc17]

1) Makes sense. Any way I can make the Open Report action go to the front (on top of the form)? At present the Form stays on top of the Report.

2) It was re-querying itself. Changed, thanks!

[pbaldy]

It sounds like the form has its pop-up/modal properties set incorrectly, or it was opened in dialog mode (an argument of OpenForm). Without those settings, a report will open on top of the form.

[kroenc17]

Thanks again baldy. You are a gentleman and a scholar.

[pbaldy]

No problemo. Some previous instructors would debate the scholar part. Some friends would probably question the gentleman part too.