setup for one to MANY,MANY

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am starting to design a database for tracking parts and one of the
requirements is that up to 250 parts can be packaged into a Lot and 3 Lots
are packaged into a Batch. When the part is made it is not known what Lot it
will be packaged in, in fact it could be scrapped. When I try to envision
what a data entry form will look like for the parts going into a Lot all I
can think of is a loooong continuous form. Does anyone have any suggestions
for how this data entry can be made more expedient.
 
I am starting to design a database for tracking parts and one of the
requirements is that up to 250 parts can be packaged into a Lot and 3 Lots
are packaged into a Batch. When the part is made it is not known what Lot it
will be packaged in, in fact it could be scrapped. When I try to envision
what a data entry form will look like for the parts going into a Lot all I
can think of is a loooong continuous form. Does anyone have any suggestions
for how this data entry can be made more expedient.
Click to expand...

If you have some way of tracking each part, like a serial number, then
create a table with the following fields:
SerialNo LotNo BatchNo Process1 Process2 Process3 Rejected
Then you would use a second table to track lots and a third to track
Batches. Don't duplicate data in the various tables beyond the
SerialNo/LotNo/BatchNo which is necessary to link the tables together
in a query. As for data input, a continuous form should do the trick,
but put it in a subform. Put a combo box for the BatchNo and a combo
box for the LotNo on the main form and use them to fill in that part
of the record. In other words, work it so that you are only putting
in those fields for which the data has changed. There is some tricky
programming here, but it can be done and it sounds like the time spent
in coding will be worth it.
 
I am starting to design a database for tracking parts and one of the
requirements is that up to 250 parts can be packaged into a Lot and 3 Lots
are packaged into a Batch. When the part is made it is not known what Lot it
will be packaged in, in fact it could be scrapped. When I try to envision
what a data entry form will look like for the parts going into a Lot all I
can think of is a loooong continuous form. Does anyone have any suggestions
for how this data entry can be made more expedient.
Click to expand...

Well, if you must enter or select 250 parts then you're going to have to enter
250 parts *somewhere* - and a continuous form seems to me to be as good a
choice as any. I'd use a Form based on Batches; a single record Subform based
on the Lots in that batch; and a contiunous sub-subform based on parts, with
(I presume) a combo box to select which parts make up that lot, and a
scrollbar to display more than will fit on the screen.

What kind of layout did you have in mind? As I say... you've got 250 "facts"
(this part is in this lot) to enter, somehow!

John W. Vinson [MVP]
 
John W. Vinson said:
Well, if you must enter or select 250 parts then you're going to have to enter
250 parts *somewhere* - and a continuous form seems to me to be as good a
choice as any. I'd use a Form based on Batches; a single record Subform based
on the Lots in that batch; and a contiunous sub-subform based on parts, with
(I presume) a combo box to select which parts make up that lot, and a
scrollbar to display more than will fit on the screen.

What kind of layout did you have in mind? As I say... you've got 250 "facts"
(this part is in this lot) to enter, somehow!

John W. Vinson [MVP]
----------------------------------------------------------------------
Click to expand...
Thanks, the layout I was hoping to have is that instead of 1
column(sfrmParts) with
250 rows, is there a way to have 3-4 columns in a continuous form ? I know
it sounds nit-picky but.................
 
Thanks, the layout I was hoping to have is that instead of 1
column(sfrmParts) with
250 rows, is there a way to have 3-4 columns in a continuous form ? I know
it sounds nit-picky but.................
Click to expand...

You could have four 82-row listboxes, or four Subforms... but I DON'T see what
benefit that gives you. Having to look at 250 checkboxes, or 250 of ANY type
of control, to find the one that you want to check would drive anyone nuts!

One question: how likely is it that you will have a Lot which in fact contains
250 *different parts*? In practice, won't a lot be made up of fewer? If you
*do* use a combo box, you can use the very helpful autocomplete feature; you
do NOT need to scroll down the entire list, you can tab into the combo and
start typing the first couple of letters of the part number or part
description and it will jump directly to it. I wonder if your concern might be
misplaced based on perception rather than fact!

John W. Vinson [MVP]
 
You could have four 82-row listboxes, or four Subforms... but I DON'T see what
benefit that gives you. Having to look at 250 checkboxes, or 250 of ANY type
of control, to find the one that you want to check would drive anyone nuts!

One question: how likely is it that you will have a Lot which in fact contains
250 *different parts*? In practice, won't a lot be made up of fewer? If you
*do* use a combo box, you can use the very helpful autocomplete feature; you
do NOT need to scroll down the entire list, you can tab into the combo and
start typing the first couple of letters of the part number or part
description and it will jump directly to it. I wonder if your concern might be
misplaced based on perception rather than fact!

John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -
Click to expand...

It wouldn't be practical to use a continuous form as a multi-column
listbox in this manner, because the programmer doesn't have much
control over the underlying code. With a great deal of work, such a
control could be made out of scratch using labels and a few other
controls. It is possible because the visibility, size, and location
of Access controls can be set programatically, and the data can also
be handled programatically. It would take a great deal of time, and
it is not for the novice programmer. You would be better off doing as
John suggests and using a common control with the autocomplete feature.
 
Back
Top