Lookup data and create a Report

[Guest]

I am creating a Freight Receiving database for our small business. I have
all of the tables and data entry forms created but am having some trouble
with my final step.
I need to be able to type in a Job # (which is data we enter in a form and
is stored in a table) and have access create a form/report showing all Jobs
with that job # along with other information related to that job # such as
freight pieces received and total weight. All of this information is stored
within our table but i want to run a report so I can see just the information
related to specific job #'s.

Any insight would be great.

 

[Jeff Boyce]

In Access, forms are for displaying (add/edit) data, where reports are for
printing out data.

If this were my project, I'd probably create a new form that gave me an
unbound combobox for selecting the Job#, and a command button that opened a
report.

I'd create a query that returned the information I wanted to see, and base
the report on that query, rather than directly on a table.

Finally, I'd probably modify the query to use the Job# selected on the form
as a selection criterion. That way, after selecting a Job#, the command
button would launch the report, which would launch the query, which would
look back at the form to find out which Job# to use.

?Confused yet?<g>!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thanks Jeff, I am at the point now where I have the query created to display
my manifest information as I want. However, I need to make this simple for
our employees and can't figure out how to make a form that simply has a
lookup box where you type in the job # and press enter and it pulls up the
manifest from the query displaying only the shipments containing that
particular job #. Let me know if you have anymore valuable information.

Thanks!

 

[Jeff Boyce]

Going through all the steps needed to create a user-friendly, functional
form may be beyond the scope of these (volunteer) newsgroups. That seems
like more of a general request than a question about a specific
issue/problem.

If I were (generically) approaching this, I would:
* first make sure I had a well-normalized underlying data structure (see
hints below)
* create a new form (design view)
* add a combobox in the header
* base the combobox on a query that returns job#s (not the shipments)
NOTE: this requires a table that lists job#s and job# information, not
shipment info.
* base the form itself on another query that uses the form's combobox to
get a parameter, i.e., the job#, and that returns that job#'s job# info (not
shipment info).
* create an AfterUpdate event procedure for the combobox that requeries
the form
* create another form, displaying shipment info
NOTE: you said "shipments containing that job#". That implies that jobs
and shipments are related one-to-many. This requires at least two tables,
tblJob, tblShipment, with each Shipment record requiring a foreign key
pointing back to the Job that owns it.
* use this new form as a subform in the first form created above
* use Access' connection between mainform (the first one) and subform (the
second) to make sure that each time a new job record was displayed in the
main form, the subform would display that job#'s shipments.

.... and that's just the general outline!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP