New "Harness Tracking" project, help needed!

[Tommy P]

Hi everybody, I'm new to the forum, I hope I get on alright! I'm an
level IT student, and I'm currently working on a project for trackin
components that are used in harnesses. I've set up tables for the dat
that is held, but the forms are proving to be a massive undertaking!
was wondering if anybody here might be able to point me (and m
lecturer!) in the right direction. So here goes...

[image: http://www.zekkari.com/tom/ERD.GIF]

The above image (hopefully) displays my entity relationship diagram
showing the relationships between the tables so far.

The top three tables represent the admin side of the database. Here
harness types can be established, and component types can b
established. The middle table links the two, allowing to associat
components with harnesses. This effectively allows the user to sho
what components go into making a particular type of harness.

The lower three tables represent the "everyday" usage of the system
The "Harnesses made" table holds data on what harnesses ar
manufactured by the business, including a unique serial number, and
harness type, which is grabbed from the Harness Type table. In
similar fashion, the "components received" refers to batches of stoc
ordered in by the business. Each batch has its own serial number, know
as a GI number, and a type, grabbed from the "component type" table.

I hope I'm explaining myself okay! The link between the two lowe
tables is the final link in the puzzle. This will hopefully allow th
user to show what batches of stock were used in a particular harnes
that they manufactured.

Now here's the tricky part! What I would like to do is use th
information from the top three tables to aid the user in the task o
selecting the appropriate stock used to make a harness. For example
they make a new harness and select its type. A subform will then sho
all the components that go in to that harness type, along with dro
down boxes, allowing the user to select the appropriate stock. Onl
batches of components with the right type should be available for eac
component choice. This should hopefully allow the end user to log wha
stock was used for a harness, with very little chance of errors.

The best example of what I would like to design that I can find i
this:

'Kit Builder' (http://store.over-clock.com/CustomBuilt_Kits.html)

Competely different subject matter, but the principal is there! Notic
how when it asks you for the CPU block type, you can only select a CP
block, not any other type of component. This system was done manually
believe, but it hopefully gives a more visual idea of what I want t
achieve with my system.

So any ideas on how to do this? My idea was to make a subform with th
relevant component type data displayed in a continuous forms layout
and then have a filtered drop down box, which filters for the dat
displayed on the form. I couldn't get this working however, so an
other suggestions would be most appreciated.

Thanks in advance for any help received, it will be most appreciated

 

[Gary Miller]

Tommy,

Welcome and good luck on your project.

A common way this is done in Access is through the use of
filtered and related combo or list boxes that use 'Lookup'
tables or queries as their recordsource. This is very good
GIGO prevention and is a very common practice. The combobox
wizard can help you build the lookup based on your tables.

I would suggest putting a couple of these on a form and come
back to us with the control names, table names and field
names and we can give you some specific code examples on how
to relate them to each other and probably some other tips as
well.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

Hi everybody, I'm new to the forum, I hope I get on alright! I'm an A
level IT student, and I'm currently working on a project for tracking
components that are used in harnesses. I've set up tables for the data
that is held, but the forms are proving to be a massive undertaking! I
was wondering if anybody here might be able to point me (and my
lecturer!) in the right direction. So here goes...

[image: http://www.zekkari.com/tom/ERD.GIF]

The above image (hopefully) displays my entity relationship diagram,
showing the relationships between the tables so far.

The top three tables represent the admin side of the database. Here,
harness types can be established, and component types can be
established. The middle table links the two, allowing to associate
components with harnesses. This effectively allows the user to show
what components go into making a particular type of harness.

The lower three tables represent the "everyday" usage of the system.
The "Harnesses made" table holds data on what harnesses are
manufactured by the business, including a unique serial number, and a
harness type, which is grabbed from the Harness Type table. In a
similar fashion, the "components received" refers to batches of stock
ordered in by the business. Each batch has its own serial number, known
as a GI number, and a type, grabbed from the "component type" table.

I hope I'm explaining myself okay! The link between the two lower
tables is the final link in the puzzle. This will hopefully allow the
user to show what batches of stock were used in a particular harness
that they manufactured.

Now here's the tricky part! What I would like to do is use the
information from the top three tables to aid the user in the task of
selecting the appropriate stock used to make a harness. For example,
they make a new harness and select its type. A subform will then show
all the components that go in to that harness type, along with drop
down boxes, allowing the user to select the appropriate stock. Only
batches of components with the right type should be available for each
component choice. This should hopefully allow the end user to log what
stock was used for a harness, with very little chance of errors.

The best example of what I would like to design that I can find is
this:

'Kit Builder' (http://store.over-clock.com/CustomBuilt_Kits.html)

Competely different subject matter, but the principal is there! Notice
how when it asks you for the CPU block type, you can only select a CPU
block, not any other type of component. This system was done manually I
believe, but it hopefully gives a more visual idea of what I want to
achieve with my system.

So any ideas on how to do this? My idea was to make a subform with the
relevant component type data displayed in a continuous forms layout,
and then have a filtered drop down box, which filters for the data
displayed on the form. I couldn't get this working however, so any
other suggestions would be most appreciated.

Thanks in advance for any help received, it will be most appreciated!