Looking for suggestion or help

I'm trying to make what should be a simple DB, but have done got
myself confused. I'm trying to keep track of the contents of several
kits that use common components. Each component has a unique number
and each kit has a unique number. I would like to be able to pull up
the kit number them choose the individual components that are included
in that kit and keep that record so if any components are changed it
would update the kit.

re: Looking for suggestion or help

S Wilson,
It is a simple database. It is called a bill of materials. There are two
basic schemas. The first uses a single table with an added column to track
what belongs to what. So:
ITEM_ID | ITEM_NO | ITEM_NAME | ITEM_DESCRIPTION | . . . (and whatever else
you need) ITEM_KIT_ID. Kit ID would be the row id of the item that
represents the kit.

For example:
ITEM_ID | ITEM_NO | ITEM_NAME | ITEM_DESCRIPTION | ITEM_KIT_ID
----------+------------+----------------+------------------------+-----------------
1 | 10 | Duck | Donald Duck
|
2 | 20 | Duck Nose | Donald Duck's Nose
| 1

I can know a couple things from this list. First Item 1 is a finished good
because it has no kit number (it doesn't belong to something else) Second,
Item 2 is part of Item 1 because if its kit id.

The limitation in this design is that because it is a one-to-many
relationship it doesn't easily handle complex builds where some parts are
components of some other parts. It would also be a pain to track
consumables like shrinkwrap, glue or assembly hardware like screws. So, to
fix this a variation on this design is needed. You need to add a table that
tracks which components are part of which other components--your Bill of
Materials table or BOM. So the Item Master table above changes slightly to:

ITEM_ID | ITEM_NO | ITEM_NAME | ITEM_DESCRIPTION | . . . (and whatever else
you need)

Kit ID is moved to the BOM table:
ITEM_ID | KIT_ID | FINISHED_GOOD. . . (and whatever else you need related to
this item and kit)

Finished good is a true/false flag included for reporting performance so you
don't need a sub-query to look for null kit id (meaning finished good) It
also allows your management to flag things as finished goods as it suits the
business even though the finished good may be a component of something else.

Bills of materials are a classic example of working with hierarchical data.
There are lots of others, including geneology and organizational charts.
There is a third solution to this problem I didn't list because it is
somewhat unorthodox and also because as a teacher I think students should be
left with something to do.

"Swilson513" <swilson513@aol.com> wrote in message
news:4fadc0dd.0411130848.2c583be3@posting.google.c om...[color=blue]
> I'm trying to make what should be a simple DB, but have done got
> myself confused. I'm trying to keep track of the contents of several
> kits that use common components. Each component has a unique number
> and each kit has a unique number. I would like to be able to pull up
> the kit number them choose the individual components that are included
> in that kit and keep that record so if any components are changed it
> would update the kit.[/color]

re: Looking for suggestion or help

Swilson513 wrote:[color=blue]
> I'm trying to make what should be a simple DB, but have done got
> myself confused. I'm trying to keep track of the contents of several
> kits that use common components. Each component has a unique number
> and each kit has a unique number. I would like to be able to pull up
> the kit number them choose the individual components that are included
> in that kit and keep that record so if any components are changed it
> would update the kit.[/color]

You have three objects that each get their own table:

component (unique number, description, what else)
kit (unique number, description)
composition (kit number, component number, possibly amount)

Set up your relationships--well, in the database--such that there is a
one-to-many from both component and kit to composition. These
relationships should be enforced, and I advise cascaded updates.

re: Looking for suggestion or help

Bas Cost Budde <b.costbudde@heuvelqop.nl> wrote in message news:<cn5dtv$87b$1@news2.solcon.nl>...[color=blue]
> Swilson513 wrote:[color=green]
> > I'm trying to make what should be a simple DB, but have done got
> > myself confused. I'm trying to keep track of the contents of several
> > kits that use common components. Each component has a unique number
> > and each kit has a unique number. I would like to be able to pull up
> > the kit number them choose the individual components that are included
> > in that kit and keep that record so if any components are changed it
> > would update the kit.[/color]
>
> You have three objects that each get their own table:
>
> component (unique number, description, what else)
> kit (unique number, description)
> composition (kit number, component number, possibly amount)
>
> Set up your relationships--well, in the database--such that there is a
> one-to-many from both component and kit to composition. These
> relationships should be enforced, and I advise cascaded updates.[/color]

OK I have created 3 tables Componets, kits and Complete Kit. I have a
one to many relationship from Componets and Kits to the complete kit.
Now I would like to have a form that is used to build the kits. I
would like to pull up the complete kit number and be able to add
componets to it using a drop down list from the componets. Any
suggestions on doing this??

TIA

re: Looking for suggestion or help

Swilson513 wrote:[color=blue]
> OK I have created 3 tables Componets, kits and Complete Kit. I have a
> one to many relationship from Componets and Kits to the complete kit.
> Now I would like to have a form that is used to build the kits. I
> would like to pull up the complete kit number and be able to add
> componets to it using a drop down list from the componets. Any
> suggestions on doing this??[/color]

Yes. Your form fills in kits, represented in this data model by several
rows in the complete-kit table. That means it should be based on this table.

To have the relation with kits in view, create a form on Kits, and have
the before form as subform here. Be sure the data properties
LinkMasterFields and LinkChildFields of the subform control are set--if
you have set up the relationships as mentioned, they should be filled in
automatically.

In the data subform, you can have the component field be a combobox
control; its *controlsource* is the table (complete kit) field, its
*rowsource* the components table.

Did you spell 'componets' indeed? :-)

re: Looking for suggestion or help

Bas Cost Budde <b.costbudde@heuvelqop.nl> wrote in message news:<cn8bao$jp8$1@news2.solcon.nl>...[color=blue]
> Swilson513 wrote:[color=green]
> > OK I have created 3 tables Componets, kits and Complete Kit. I have a
> > one to many relationship from Componets and Kits to the complete kit.
> > Now I would like to have a form that is used to build the kits. I
> > would like to pull up the complete kit number and be able to add
> > componets to it using a drop down list from the componets. Any
> > suggestions on doing this??[/color]
>
> Yes. Your form fills in kits, represented in this data model by several
> rows in the complete-kit table. That means it should be based on this table.
>
> To have the relation with kits in view, create a form on Kits, and have
> the before form as subform here. Be sure the data properties
> LinkMasterFields and LinkChildFields of the subform control are set--if
> you have set up the relationships as mentioned, they should be filled in
> automatically.
>
> In the data subform, you can have the component field be a combobox
> control; its *controlsource* is the table (complete kit) field, its
> *rowsource* the components table.
>
> Did you spell 'componets' indeed? :-)[/color]

Well it looked good at the time Components.

Thanks for the help, I'm sure I'll need more.

Thanks Again

re: Looking for suggestion or help

swilson513@aol.com (Swilson513) wrote in message news:<4fadc0dd.0411150448.3b21ee42@posting.google. com>...[color=blue]
> Bas Cost Budde <b.costbudde@heuvelqop.nl> wrote in message news:<cn8bao$jp8$1@news2.solcon.nl>...[color=green]
> > Swilson513 wrote:[color=darkred]
> > > OK I have created 3 tables Componets, kits and Complete Kit. I have a
> > > one to many relationship from Componets and Kits to the complete kit.
> > > Now I would like to have a form that is used to build the kits. I
> > > would like to pull up the complete kit number and be able to add
> > > componets to it using a drop down list from the componets. Any
> > > suggestions on doing this??[/color]
> >
> > Yes. Your form fills in kits, represented in this data model by several
> > rows in the complete-kit table. That means it should be based on this table.
> >
> > To have the relation with kits in view, create a form on Kits, and have
> > the before form as subform here. Be sure the data properties
> > LinkMasterFields and LinkChildFields of the subform control are set--if
> > you have set up the relationships as mentioned, they should be filled in
> > automatically.[/color][/color]


[color=blue][color=green]
> >
> > In the data subform, you can have the component field be a combobox
> > control; its *controlsource* is the table (complete kit) field, its
> > *rowsource* the components table.
> >
> > Did you spell 'componets' indeed? :-)[/color]
>
> Well it looked good at the time Components.
>
> Thanks for the help, I'm sure I'll need more.
>
> Thanks Again[/color]

Finally just about everything is working, how could something so easy
be so confusing??

Another question, I need to not allow or at least warn when exiting a
required field on a FORM. I found this information but can't seem to
get it to work. The name of the field is Control Numer (yes another
spelling error on my field:)Do you see what is wrong or other
suggestion?

My field was actually "Control Numer" but with out the "_" I would get
errors.


Private Sub Control_Numer_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
If IsNull(Me.Control_Numer) Then
Cancel = True
strMsg = strMsg & "Control_Numer is required." & vbCrLf
End If
If IsNull(Me.Control_Numer) Then
Cancel = True
strMsg = strMsg & "AnotherField is required." & vbCrLf
End If
'Repeat for other fields as needed.
If Cancel Then
MsgBox strMsg
End If

End Sub

Thank You

re: Looking for suggestion or help

Swilson513 wrote:
[color=blue]
> Finally just about everything is working, how could something so easy
> be so confusing??[/color]

Right: once you know, it seems easy.
[color=blue]
> Another question, I need to not allow or at least warn when exiting a
> required field on a FORM.[/color]

You can set the field to be required (that is a property visible in
Design view of the table). That way the user does get a warning, but not
immediately after leaving the field. If you have many fields on the
form, it may be too vague for the user.

You could as further hint change the backcolor of every control that
sits on a required field, so there is some visual cue for the user which
fields need a value. I wrote routines that look up the Required property
of the fields and color the controls accordingly. Interested?

Be aware that if you forbid the user to exit the control unless a value
is entered, that constitutes a trap for the user. A warning seems better
to me than confinement.
[color=blue]
> My field was actually "Control Numer" but with out the "_" I would get
> errors.[/color]

True, Access changes the spaces in control names to underscores when
using it in code.
[color=blue]
> Private Sub Control_Numer_BeforeUpdate(Cancel As Integer)
> Dim strMsg As String
> If IsNull(Me.Control_Numer) Then
> Cancel = True
> strMsg = strMsg & "Control_Numer is required." & vbCrLf
> End If
> If IsNull(Me.Control_Numer) Then
> Cancel = True
> strMsg = strMsg & "AnotherField is required." & vbCrLf
> End If
> 'Repeat for other fields as needed.
> If Cancel Then
> MsgBox strMsg
> End If[/color]

First, you should check for every control you want to do this for in its
*own* event handler. Control_Numer gets one, AnotherField too, and so on.

Second, consider using the Exit event. That has a Cancel property as
well, and I'm not sure of exactly when BeforeUpdate fires. You can check
that in the Help on "event order" or so.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea