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!!

Cheers

 

[Trevor G]

If you want to generate a report based on the record in a form, you can use a MACRO

First create the report based on all of the records in the table

Then create a MACRO to open the report, in the macro you can set conditions, and using the build wizard you can set the condition to be

TableName.Field=FormName.Field

Then save, if you then test it, it will give you a single record that is shown in the form

 

[kroenc17]

Trevor,

Thanks for your reply.. when I create a macro (which I'm doing from my form), I'm using OpenReport as the action. For the conditions in the expression builder I'm confused as to what to enter.

The table and field for the second combo box on my form (which sets the specific criteria for the report I want to create) is either Project Manager or Project Manager.Value (Both from the same table: Projects).

In the query builder the Project Manager.Value contains "[Forms]![Sean]![Combo7]" in it's criteria, but that field AND the Project Manager are marked as shown in the query builder.

So I don't know what to enter for TableName.Field for the expression you quoted, and also I don't know what Field to use for the form part either. Right now I've got:

Projects.?=Sean.?

 

[Trevor G]

Can you upload a sample database it will be easier to look at.

 

[kroenc17]

It's for something at work, so I cant :/.

I've created a macro button on my form and I'm getting it to open a report with the fields on it I would like, but it's not filtering them via the criteria in the form.

The macro condition is: Forms![Sean]![Combo2].AfterUpdate

Combo2 is the second combo box which shows the project I want to generate the report off of.

In the report I've tried putting [Forms]![Sean] and choosing 'yes' for filter on load, but that generates no data.

 

[Trevor G]

Ok then,

Take a look at the attached I have created a form with a combo box and when you select it the record is shown in the form, so it is finding a record based on those available.

There is a simple command button to the right and when you click it, it will open a report based on the record in the form.

If this is what you want look behind the properties of the command button and it has a macro which as explained is using conditions to show the record in the report based on the record in the table and the record on the form.

I hope this will help you.

 

[kroenc17]

Trevor, again, Thank you for your help.

I copied your code and replaced it with the names from my database.

[Projects]![Project Name]=[Forms]![Sean]![Combo2]

Is what it turns out to. I used Combo2 for the combo box because my form differs from yours in that when you make the selection in the combo box it does not show it in another spot on the form. Your command button references the field that is generated after you make a selection in your combo box.

Using the above quoted Where condition in my macro, when I press the command button on the form I am prompted to enter a parameter for Projects!Project Name. If I type in the project I chose in the combo box, it still lists all projects - not the chosen one. If I take the table part of the expression out and only use

[Forms]![Sean]![Combo2]

it opens the report and again does not filter by the project name I chose in the combo box.

I'm thinking that because I'm running a requery macro afterupdate on each combo box it may have something to do with the problem, as I'm first selecting an employee, and then using that selection to list projects for which he is responsible in a second combo box.

Is there an easier way to do this? I'm extremely new to access..

Thanks for your help, again.

 

[Trevor G]

Sean,

Try this out, when you work with a combo box you have to refer to the column number within the combo.

My suggestion is as follows to see if you get a value from it.

On your form create a text box and name it txtReport, then behind the combo box and in the After Update event type in the following

me.txtReport.value = me.comboboxname.column(0)

Then save and change the view and select something from the combo box and see if a name is returned into the textbox, if so then adjust your query to look at the textbox and then you will be able to run your report from the text box.

The first column in the combo is zero (0) so if this is an id field you may have to change this to column(1) or what ever to find the item you want to work with.

Without seeing the database I can't do much more than this.

 

[Dairy Farmer]

Filters

Form = F_1
Combo Box = ComboProject

[ReportProject] = [Forms]![F_1]![ComboProject]

Or put it in the query and use conditions.

Remember the Combo AfterUpdate

Private Sub ComboProject_AfterUpdate()
 
    Dim rs As Object
    
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Project] = '" & Me![ComboProject] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

 

[kroenc17]

Trevor: Thanks again for the reply, going to take me a minute to try to implement what you suggested.

Dairy: I tried putting a filter on my report in the Data tab of the properties. In the filter I tried

[Sean]=Forms!Sean!Combo2

&

[ReportProject]=Forms!Sean!Combo2

I wasn't sure if 'ReportProject' was supposed to be renamed to the name of my project or not. I enabled 'filter on load', but with both of the above filters I just got a report with no data.

As for the query suggestion, I'm not sure which query you mean to put it in..

Thirdly (wow I'm bad at this), I already have a requery macro running AfterUpdate on the second combobox.. so could I use the VBA code you posted as the OnClick event for a command button?

 

[kroenc17]

Trevor,

I am not a worthy student. Your last went entirely over my head - maybe it's too early . I tried your method, created a text box, couldn't really add it to the after update on my combobox because theres already a macro running in there to requery. In the data properties for the combobox it says 'Bound Column: 1'. That have anything to do with it? I wish I could just send this whole thing to you... would any screenshots help?

Thanks, again!

 

[Dairy Farmer]

Here is an example of how I generate a report for a month. In your case it would be for a Project.

Table 1
ID
Date1
Qty

Query1
Date1
Qty
Month (mmmm yyyy from Date1)

Query2
Month (mmmm yyyy from Date1)
Sort (yyyymm from Date1)

Form1
ComboMonth (based on Query2)
Button (Open Report1)

Report1
Filter = [Date1] Between DateSerial(Year([Forms]![Form1]![ComboMonth]),Month([Forms]![Form1]![ComboMonth]),1) And DateSerial(Year([Forms]![Form1]![ComboMonth]),Month([Forms]![Form1]![ComboMonth])+1,0)
Month
Date
Qty

 

[kroenc17]

Dairy - that wasn't exactly the same thing I was doing but for some reason it clicked in my head what I needed to do! Made a new query, set the criteria to my combo button, realized I needed to add all the fields in the query that were going to be in the report - and bam! it works!

final question: 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?

 

[kroenc17]

I knew that wouldn't be the final question.. but here it is!:

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?

 

[Dairy Farmer]

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?

Put a CloseForm in your Macro or put a docmd.close in the buttons sub.