Creating a report based on a query

[Guest]

I have a table (JOBS) that I have details of who does what,where and when.
The lead person is selected from another table called LEAD.
I want to create a SEARCH form from which the user selects a lead person
they want to report on. When they select it I would like to have a
LEAD_REPORT based on that selection using some of the JOBS fields.
Is there a way that I can do this without programming?
Thanks

 

[Guest]

I hope this helps. I am assuming that you know how to design Access queries.

Create a form (we will name it frmLeadPersonChooser) with an UNBOUND
ComboBox (we will name it cboLeadPerson) where you can select the LeadPerson
you want. Use a simple query as the -- RowSource -- of the ComboBox. The
query could be as simple as this:

SELECT LeadPerson
FROM tblLEAD

Then create your Report, using another Query as the Form's -- Record
Source. --

The query SQL should be something like this:

SELECT tblLEAD.LeadPerson, tblJOBS.Field1, tblJOBS.Field2, tblJOBS.Field3
FROM tblLEAD INNER JOIN tblJOBS ON tblLEAD.SameField = JOBS.SamefField

To make your Report display the LeadPerson from your ComboBox add Criteria
to your query like this:

WHERE ((LEAD.LeadPerson)=[Forms]![frmLeadPersonChooser]![cboLeadPerson]);

Be sure to get all of the brackets and the form name exactly right.

The finished query should look something like this:

SELECT tblLEAD.LeadPerson, tblJOBS.Field1, tblJOBS.Field2, tblJOBS.Field3
FROM tblLEAD INNER JOIN tblJOBS ON tblLEAD.SameField = JOBS.SamefField
WHERE ((LEAD.LeadPerson)=[Forms]![frmLeadPersonChooser]![cboLeadPerson]);

Next, add all the fields you want to show on the Report by dragging them
from your field list to your Report.

After that, you will have to add just a little code to your Report but we
will keep it quite simple. There are two subs:

' Open the Form when the Report Opens to choose the LeadPerson
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmLeadPersonChooser", , , , , acDialog
End Sub

And:

' Close the form when you close the Report
Private Sub Report_Close()
DoCmd.Close acForm, "frmLeadPersonChooser"
End Sub

Save your Report.

That's it. When you open the Report the form should automatically open with
the ComboBox. Select the LeadPerson you want and the Report should then
appear with the information you want.

Hope that helps.

 

[Guest]

Thank you for the quick response. I will try this and let you know how it went.

I hope this helps. I am assuming that you know how to design Access queries.

Create a form (we will name it frmLeadPersonChooser) with an UNBOUND
ComboBox (we will name it cboLeadPerson) where you can select the LeadPerson
you want. Use a simple query as the -- RowSource -- of the ComboBox. The
query could be as simple as this:

SELECT LeadPerson
FROM tblLEAD

Then create your Report, using another Query as the Form's -- Record
Source. --

The query SQL should be something like this:

SELECT tblLEAD.LeadPerson, tblJOBS.Field1, tblJOBS.Field2, tblJOBS.Field3
FROM tblLEAD INNER JOIN tblJOBS ON tblLEAD.SameField = JOBS.SamefField

To make your Report display the LeadPerson from your ComboBox add Criteria
to your query like this:

WHERE ((LEAD.LeadPerson)=[Forms]![frmLeadPersonChooser]![cboLeadPerson]);

Be sure to get all of the brackets and the form name exactly right.

The finished query should look something like this:

SELECT tblLEAD.LeadPerson, tblJOBS.Field1, tblJOBS.Field2, tblJOBS.Field3
FROM tblLEAD INNER JOIN tblJOBS ON tblLEAD.SameField = JOBS.SamefField
WHERE ((LEAD.LeadPerson)=[Forms]![frmLeadPersonChooser]![cboLeadPerson]);

Next, add all the fields you want to show on the Report by dragging them
from your field list to your Report.

After that, you will have to add just a little code to your Report but we
will keep it quite simple. There are two subs:

' Open the Form when the Report Opens to choose the LeadPerson
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmLeadPersonChooser", , , , , acDialog
End Sub

And:

' Close the form when you close the Report
Private Sub Report_Close()
DoCmd.Close acForm, "frmLeadPersonChooser"
End Sub

Save your Report.

That's it. When you open the Report the form should automatically open with
the ComboBox. Select the LeadPerson you want and the Report should then
appear with the information you want.

Hope that helps.

I have a table (JOBS) that I have details of who does what,where and when.
The lead person is selected from another table called LEAD.
I want to create a SEARCH form from which the user selects a lead person
they want to report on. When they select it I would like to have a
LEAD_REPORT based on that selection using some of the JOBS fields.
Is there a way that I can do this without programming?
Thanks

 

[Guest]

Let me know how it goes or if there is anything you don't understand. If you
need some additional help, don't hesitate to ask. I can give step by step
instructions with any part of the process.

You can also learn more about this at this site:
http://office.microsoft.com/en-us/access/HA011170771033.aspx

Thank you for the quick response. I will try this and let you know how it went.

I hope this helps. I am assuming that you know how to design Access queries.

Create a form (we will name it frmLeadPersonChooser) with an UNBOUND
ComboBox (we will name it cboLeadPerson) where you can select the LeadPerson
you want. Use a simple query as the -- RowSource -- of the ComboBox. The
query could be as simple as this:

SELECT LeadPerson
FROM tblLEAD

Then create your Report, using another Query as the Form's -- Record
Source. --

The query SQL should be something like this:

SELECT tblLEAD.LeadPerson, tblJOBS.Field1, tblJOBS.Field2, tblJOBS.Field3
FROM tblLEAD INNER JOIN tblJOBS ON tblLEAD.SameField = JOBS.SamefField

To make your Report display the LeadPerson from your ComboBox add Criteria
to your query like this:

WHERE ((LEAD.LeadPerson)=[Forms]![frmLeadPersonChooser]![cboLeadPerson]);

Be sure to get all of the brackets and the form name exactly right.

The finished query should look something like this:

SELECT tblLEAD.LeadPerson, tblJOBS.Field1, tblJOBS.Field2, tblJOBS.Field3
FROM tblLEAD INNER JOIN tblJOBS ON tblLEAD.SameField = JOBS.SamefField
WHERE ((LEAD.LeadPerson)=[Forms]![frmLeadPersonChooser]![cboLeadPerson]);

Next, add all the fields you want to show on the Report by dragging them
from your field list to your Report.

After that, you will have to add just a little code to your Report but we
will keep it quite simple. There are two subs:

' Open the Form when the Report Opens to choose the LeadPerson
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmLeadPersonChooser", , , , , acDialog
End Sub

And:

' Close the form when you close the Report
Private Sub Report_Close()
DoCmd.Close acForm, "frmLeadPersonChooser"
End Sub

Save your Report.

That's it. When you open the Report the form should automatically open with
the ComboBox. Select the LeadPerson you want and the Report should then
appear with the information you want.

Hope that helps.

I have a table (JOBS) that I have details of who does what,where and when.
The lead person is selected from another table called LEAD.
I want to create a SEARCH form from which the user selects a lead person
they want to report on. When they select it I would like to have a
LEAD_REPORT based on that selection using some of the JOBS fields.
Is there a way that I can do this without programming?
Thanks

 

[Guest]

I am having trouble with this. Probably a little more complex than what I am
used to. When I run the "simple" query the drop-down list appears and I can
select the name but then nothing else happens. If I open the Report the
drop-down appears again but the report does not have the fields populated.
Does the "WHERE" command belong in the RowSource of the report? I am also now
having problems of our Intranet coming up with a "Select Data Source" dialog
box when I try to open the report.
I did not get any errors when I entered/ran the code. I guess I really don't
understand the statements and what they are trying to do. I have printed out
various help subjects but am having trouble deciphering why you wrote what
you did - so I can troubleshoot.
It seems like I should have a macro connected to the simple query that once
selected it would populate a field with that name then the report would base
it off that name.
Confused......

Let me know how it goes or if there is anything you don't understand. If you
need some additional help, don't hesitate to ask. I can give step by step
instructions with any part of the process.

You can also learn more about this at this site:
http://office.microsoft.com/en-us/access/HA011170771033.aspx

Thank you for the quick response. I will try this and let you know how it went.

I hope this helps. I am assuming that you know how to design Access queries.

Create a form (we will name it frmLeadPersonChooser) with an UNBOUND
ComboBox (we will name it cboLeadPerson) where you can select the LeadPerson
you want. Use a simple query as the -- RowSource -- of the ComboBox. The
query could be as simple as this:

SELECT LeadPerson
FROM tblLEAD

Then create your Report, using another Query as the Form's -- Record
Source. --

The query SQL should be something like this:

SELECT tblLEAD.LeadPerson, tblJOBS.Field1, tblJOBS.Field2, tblJOBS.Field3
FROM tblLEAD INNER JOIN tblJOBS ON tblLEAD.SameField = JOBS.SamefField

To make your Report display the LeadPerson from your ComboBox add Criteria
to your query like this:

WHERE ((LEAD.LeadPerson)=[Forms]![frmLeadPersonChooser]![cboLeadPerson]);

Be sure to get all of the brackets and the form name exactly right.

The finished query should look something like this:

SELECT tblLEAD.LeadPerson, tblJOBS.Field1, tblJOBS.Field2, tblJOBS.Field3
FROM tblLEAD INNER JOIN tblJOBS ON tblLEAD.SameField = JOBS.SamefField
WHERE ((LEAD.LeadPerson)=[Forms]![frmLeadPersonChooser]![cboLeadPerson]);

Next, add all the fields you want to show on the Report by dragging them
from your field list to your Report.

After that, you will have to add just a little code to your Report but we
will keep it quite simple. There are two subs:

' Open the Form when the Report Opens to choose the LeadPerson
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmLeadPersonChooser", , , , , acDialog
End Sub

And:

' Close the form when you close the Report
Private Sub Report_Close()
DoCmd.Close acForm, "frmLeadPersonChooser"
End Sub

Save your Report.

That's it. When you open the Report the form should automatically open with
the ComboBox. Select the LeadPerson you want and the Report should then
appear with the information you want.

Hope that helps.

:

I have a table (JOBS) that I have details of who does what,where and when.
The lead person is selected from another table called LEAD.
I want to create a SEARCH form from which the user selects a lead person
they want to report on. When they select it I would like to have a
LEAD_REPORT based on that selection using some of the JOBS fields.
Is there a way that I can do this without programming?
Thanks

 

[Guest]

I think I am in this too deep but I don't mind fighting through it and
learning. I am going to rephrase my original request and see if that helps.

I have a table called LEAD. In that table I have the following entries under
the field NAMES:
Moe
Larry
Curley


They have id's linked to another table called: JOBS. In that table I have
fields:
JobDescription
Comments
DueDate
Names (Which is from the LEAD table)

Reports are made based on the LEAD person. The user wants to be able to
select from a drop-down list the LEAD they wish to report on showing the
JobDescription, Comments and DueDate.

In reality, of course, there are hundreds of names in the drop-down list. I
am really stumped. I thought I almost had it with your suggestion. Maybe I
mis-typed or left something out. I did not get any errors but nothing really
happened. Maybe if I stop thinking about it then it will come to me this
weekend.
Thanks for any help.

I am having trouble with this. Probably a little more complex than what I am
used to. When I run the "simple" query the drop-down list appears and I can
select the name but then nothing else happens. If I open the Report the
drop-down appears again but the report does not have the fields populated.
Does the "WHERE" command belong in the RowSource of the report? I am also now
having problems of our Intranet coming up with a "Select Data Source" dialog
box when I try to open the report.
I did not get any errors when I entered/ran the code. I guess I really don't
understand the statements and what they are trying to do. I have printed out
various help subjects but am having trouble deciphering why you wrote what
you did - so I can troubleshoot.
It seems like I should have a macro connected to the simple query that once
selected it would populate a field with that name then the report would base
it off that name.
Confused......

Let me know how it goes or if there is anything you don't understand. If you
need some additional help, don't hesitate to ask. I can give step by step
instructions with any part of the process.

You can also learn more about this at this site:
http://office.microsoft.com/en-us/access/HA011170771033.aspx

Thank you for the quick response. I will try this and let you know how it went.


:

I hope this helps. I am assuming that you know how to design Access queries.

Create a form (we will name it frmLeadPersonChooser) with an UNBOUND
ComboBox (we will name it cboLeadPerson) where you can select the LeadPerson
you want. Use a simple query as the -- RowSource -- of the ComboBox. The
query could be as simple as this:

SELECT LeadPerson
FROM tblLEAD

Then create your Report, using another Query as the Form's -- Record
Source. --

The query SQL should be something like this:

SELECT tblLEAD.LeadPerson, tblJOBS.Field1, tblJOBS.Field2, tblJOBS.Field3
FROM tblLEAD INNER JOIN tblJOBS ON tblLEAD.SameField = JOBS.SamefField

To make your Report display the LeadPerson from your ComboBox add Criteria
to your query like this:

WHERE ((LEAD.LeadPerson)=[Forms]![frmLeadPersonChooser]![cboLeadPerson]);

Be sure to get all of the brackets and the form name exactly right.

The finished query should look something like this:

SELECT tblLEAD.LeadPerson, tblJOBS.Field1, tblJOBS.Field2, tblJOBS.Field3
FROM tblLEAD INNER JOIN tblJOBS ON tblLEAD.SameField = JOBS.SamefField
WHERE ((LEAD.LeadPerson)=[Forms]![frmLeadPersonChooser]![cboLeadPerson]);

Next, add all the fields you want to show on the Report by dragging them
from your field list to your Report.

After that, you will have to add just a little code to your Report but we
will keep it quite simple. There are two subs:

' Open the Form when the Report Opens to choose the LeadPerson
Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "frmLeadPersonChooser", , , , , acDialog
End Sub

And:

' Close the form when you close the Report
Private Sub Report_Close()
DoCmd.Close acForm, "frmLeadPersonChooser"
End Sub

Save your Report.

That's it. When you open the Report the form should automatically open with
the ComboBox. Select the LeadPerson you want and the Report should then
appear with the information you want.

Hope that helps.

:

I have a table (JOBS) that I have details of who does what,where and when.
The lead person is selected from another table called LEAD.
I want to create a SEARCH form from which the user selects a lead person
they want to report on. When they select it I would like to have a
LEAD_REPORT based on that selection using some of the JOBS fields.
Is there a way that I can do this without programming?
Thanks

 

[Hunter57]

I think I am in this too deep but I don't mind fighting through it and
learning. I am going to rephrase my original request and see if that helps.

I am so very sorry, but I accidently left something out of the
process. It is very simple, but important. I am sorry you have been
having so much trouble with this but it really is worth it if you
learn how to do it. Before long you will be using multiple parameters
for your forms. That should really impress the boss!

I can send you a small sample DB if you send me an email at
(e-mail address removed) (Take out the X's).
Then you can import something if you want or just see how it works.

Perhaps I should have described in more detail to you what happens
with this process. This is how the proceedures go (I Think!) when it
works correctly.

1. You Click to open your Report.
2. Before the Report Opens, the VBA code opens your Form with the
combo box.
3. You select the name you want from the combo box.
4. You click the Preview Report button.
5. The Query that is the Control Source for the Report reads the
Criteria (that is the "WHERE ((LEAD.NAMES = [Forms]! etc." code) from
your combo box, which is the name you selected.
6. The Report then opens and the data you selected appears on the
Report.

Please excuse me if I make it too simple, but I want to make sure you
are able accomplish this task, especially since I left out a step.

How to make this happen. Trust me, it will work (eventually).

Here is the step I left out. On the Form with your combo box you need
to add a command button with just a little code you can paste right
in.

EDIT THE FORM
1. First open your Form in design view.
2. Add a Command button to the Form from the Toolbox.
3. Let the Caption read "Preview Report". Right click on the button
and select Properties. Click on the Form tab and type in Preview
Report on the Caption line.
4. Next Click on the Other tab and type in btnPreviewRpt on the "Name"
line.
5. Next Click on the Event tab and then to the far right of the "On
Click" line.
6. Then click on Code Builder in the pop-up window.
7. Then paste this line in the middle of the sub proceedure:
Me.Visible = False
Your Proceedure should look like this:

Private Sub btnPreviewRpt_Click()
Me.Visible = False
End Sub

Another Step. This is optional, but good to have on your form.

Add a "Cancel" Command button to the Form. Add a button from the
toolbox to the Form. Click "Form Operations" and "Close Form" in the
Wizard Window. Click Next. I type in "Cancel" for my Caption and name
my button btnCancel.


EDIT THE REPORT
It seems as though you are having problems with the Query that is the
Record Source for your Report. The information you gave was helpful
so I may be able to get close to what you need with the Query and you
can correct the form and field names.

1. Open your Report in Design view.
2. RIGHT Click on the little square in the upper left corner of your
Report. (If you don't see it, click View and then Ruler on the
MenuBar.)
3. Select Properties from the drop-down menu.
4. Click on the Data tab.
5. Add or edit your Query.
Look to the right of the Record Source to see if your Query or SQL
code is there. If not, go to the right side of the white area. If
you have saved your query click there and select it from the drop down
menu.
If you have not saved it then go past the end of the white line on
the right side and click there. The Query Design Grid appears. You
can use the design grid to create your query if you want. Add your
two tables and your fields to the grid.

Below the NAMES cell add the following to the Criteria row:
[Forms]![frmNAMEChooser]![cboNAMES]

Replace frmNAMESChooser with the name of your Form.
Make sure that cboNAMES is the Name of your combo box and not just
the name of your field! To check it, open your Form in Design View.
Right Click on your combo box. Select the "Other" tab. The name there
is what should go in the last set of brackets. It should be like
this: [Forms]![yourformname]![yourcomboboxname].

Or you can do it in SQL View. You can save your query and use it
there or just add all the SQL code to the Control Source line. The
SQL should look something like this:

SELECT LEAD.ID, LEAD.NAMES, JOBS.JobDescription, JOBS.Comments,
JOBS.DueDate
FROM LEAD INNER JOIN JOBS ON LEAD.ID = JOBS.ID
WHERE ((LEAD.NAMES)=[Forms]![frmNAMESChooser]![cboNAMES]);

You should be able to substitute your field and form names for the
ones I used.

I recommend that you save your query, then make a copy and work on the
copy until you have it the way you want.

If it does not work after you do all of the above, then you probably
need to take a look at the VBA in your Report. That should not be
hard to correct. Just let me know how it goes.

I know some of this is new to you so if you have any questions, just
ask away or you can send them by email if you want.

Hunter57