Report from parameter querys

  • Thread starter Thread starter Mattheau
  • Start date Start date
M

Mattheau

I have an extensive parts list and only some parts apply
to each individual unit, after my parameter search comes
up with my results, I still have columns and columns of
0's in the one's that do not apply to the part.

Question I wish only to display on the report the spaces
that pertain to the part, and have the report change each
time I see it with the different info displayed and easy
to print.

Can anyone help??
 
Your question doesn't give much to work with, but I wonder about your
database design. If I understand correctly, each unit uses only a few of the
many parts in your database, but several different units use the same part.
If so, you need three tables: Units, Parts, and PartsUsed, something like
this:
UnitsTable
UnitID (autonumber primary key, or PK)
Description
etc.

Parts Table
PartID (autonumber PK)
Description
PartNumber
etc.

PartsUsed
PartsUsedID (autonumber PK)
UnitID (foreign key, or FK; data type = Number)
PartID (FK; data type = Number)

First make the tables, then in the relationships window drag UnitID from the
Units table to the corresponding field in the PartsUsed table. Repeat for
PartID.

There is more, but I don't know for sure this is what you need, so I won't
add any more until I hear back from you. What I have outlined will establish
table realationships, which is where database design begins. I suspect you
have elements of flat database design in your project. Post your table
structure if you have additional questions.
 
You have hit it on the head. I have created two tables
so far, the first being the part table that tells me how
many of each part to use, as well as the complete number
of parts.

The second is telling me where these parts are on the
pallets in the warehouse.

I want to search by Unit #(with output showing me which
parts I need and how many for each unit as well as just
the corresponding pallets. My problem being my final
output is not going onto one page so far, as well as
having all the blank pallets as well as the relevant
pallets listed on the report. How do I just list the
relevant pallets with the corresponding parts with the
corresponding units.

Thankyou so much!
 
I don't see why you need to hide so many fields that are not relevant to a
particular unit rather than just not including those fields in the report in
the first place. However, maybe you could do accomplish what you want by
adding this code to the report Detail section On Format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Visible = Not IsNull(ctl)
End If

Next ctl

End Sub

For this to work you will need to make sure the labels are bottom-aligned
with the text boxes, that text boxes are not next to each other (they need to
be one above the other), and that the Can Shrink property of each text box is
set to Yes.

You seem to have at least three distinct entities here: Units, Parts, and
Location. Can two pallets be in the same location? If so, you do not want
to include location in the same table with the part. Can a pallet be moved?
If it is, do you have to change the part record?

A good general principle for table design is that you should be able to
describe the table's function in a single sentence without using the word
"and". Part and location do not belong together. The parts requirement for
a unit and the parts inventory are probably two different things.

Do your tables have primary keys? How are the tables related? When you
look up a unit, how are the required parts displayed? If a detail about a
part changes (price?), how many records do you need to edit? What you are
describing is a good use of a relational database, but I can't imagine how
you could construct a relational database containing only two tables that
meets your needs. If you take the time to set it up properly you will reap
the benefits for a long time to come.
 
Back
Top