Bringing it all together - form as criteria for query to gen report 3

[Aterlatus]

Hi all,

I currently have a query and a report that work quite happily together, however it requires that I hard-code the criteria for the query beforehand. I'm trying to change this so that the report can be fired up by submitting the criteria (a contractor and an engineer) on a form.

I've built the form, and it happily reads a list of contractors and engineers from one of the tables. I just don't know where to go from here (integrating everything).

I'm still quite new at access programming (although not general db programming) so try and keep it simple if you can

Thanks,

Steve Sherlock

 

[chickey]

If I am getting the whole picture, you just want to have
the query criteria picked up from the form at the time
of running the report?

If so, say the form is called frmYourForm and the
form field (which I assume is a listbox) is
lbxYourContractor, and the field in the query (say
tblContractor) which you have hardcoded the criteria to
be "John Smith", then change the "John Smith" in the
tblContractor criteria to be:

Forms![frmYourForm]![lbxYourContractor]

Make sure that the bound column of the lbxYourContractor
is the contractor name text i.e. "John Smith" and not
the ContractorID.

If it is the ContractorID, then you need to use the
following reference method in the query criteria:

Forms![frmYourForm]![lbxYourContractor].column(1)

Column numbers start at 0 and reference the columns in the
listbox, so the above would be referencing the second
column in the listbox's underlying fields.

So, this will change the criteria of the tblContractor
field in the query to reference the lbxYourContractor
listbox on the form frmYourForm.

As long as the form is open and the lbxYourContractor
listbox has something selected, then everything should be ok.

Reply if you still have issues.

Regards...

 

[Aterlatus]

Okies - I'm actually using a combobox, but that should work the same (at least, it does in visual basic).

When I try and enter the following as the criteria for my query:

Forms![SelectContractor]![Engineer]

access turns it into:

[Forms]![SelectContractor]![Engineer]

And I get a standard pop-up box asking me for a value for Forms!SelectContractor!Engineer - any ideas?

Thanks

 

[Aterlatus]

Also, once I've got the query set up how do I get the query to run from the button click on the form? How does this then go on to generate the report?

Thanks

 

[chickey]

Have you checked your formname and your combobox name?
You are using [Forms]![SelectContractor]![Engineer]. Is
your form named SelectContractor? Is there a combobox named
Engineer on that form?

Once, you have this sorted, create a new report and base the
report on the query you have created. If you go to the
report properties, it should have the record source property
set as your queryname. Then any visible fields of the query
are eligible to be put on the report when you are creating
it.

Reply if you are still having problems.

Regards...

 

[Aterlatus]

OK, I think that once I've associated the form with the query/report things should start working a little better.

I've got the button placed and named, how do I go about getting it to run the query & fire up the report when I click the button?

Also, I need a count of the records found on the report page - how can I count the number of results in the query to stick it in this text box?

Thanks again,

Steve Sherlock

 

[chickey]

Hi Steve,

Easiest way is to create a button and use the wizard that
pops up to call the report.

Create a button on the form and the wizard will pop up.
Select Report operations and then select either "print
report" or "preview report". Then select the report to
print/preview.

This will create code behind the button to run the report
with the query attached (if you attached it to the report as
its record source as I indicated in my previous post).

To get the count of records in the query, if your report is
printing those records, then you can use the
Reports![ReportName]!{ReportTotalRecords] = int(Reports![ReportName]!{ReportTotalRecords]) + 1
method in the detail section code (onprint) i.e. which
will run the code for every detail record.

Another method would be to have a seperate query or code
which counts the records and use this as a source for
your "total" textbox on the report. You may need to do
a bit of coding for this (i.e. run the query, get the
count, assign the count to the textbox in the report
footer etc.)

Reply if you have any more problems.

Regards...

 

[Aterlatus]

Is it not possible to just set "Control Source" property to:

Query:[QueryName].count

Or something? Surely there's got to be an easier way such as this?

If not, could anyone supply more detailed suggestions for implementing the recordcount - the suggestion in the last post just gave me a parameter prompt.

Thanks for all the help so far everyone

 

[Rolliee]

Steve,

As an old programmer and a not so old ACCESS programmer, I will jump into your problem and show you how to do it with a form if you send me your email. After I make and send you the mdb, I will post it here so all can see.

rollie@bwsys.net