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