Subform within a subform on a form?

[Gordon Foster]

Hi,

I am making an Access 2000 database to record serial numbers of products
that we ship out.

I have a main form which is for the orders with an underlying table called
'Orders'. On this form I have a subform for all the items (part numbers) on
the order with an underlying table called 'Order Details'. The items on the
order may have one or more serial numbers so I have a subform within the
subform with an underlying table called 'Order Details Details'. I can
navigate the main form from order to order. I can navigate the first
subform from one item to the next and see the serial numbers for each item.
I'm sure this is the way I should have my tables setup.

The problem is usability. I want users to select a part number and then
start scanning, whether each item needs one or more serial numbers. The way
in which it has to be done now is to select the first item from the subform
and scan the serial numbers for that item in the subform within the subform.
Next the user will have to navigate to the next item and scan the serial
numbers for that item and so on. The navigation part will slow down our
users as there may be a lot of items on the order. My plan is to use code
in the _AfterUpdate event of the control where the serial numbers are
entered to see if the maximum number of serial numbers has been reached for
that item and then I will navigate them to the next item and place the focus
back to the subform within the subform.

Does this look like the best way for me to be doing this? I'm just
wondering if I've made things more complicated for myself (i.e. setup my
relationships wrong).

Any suggestions would be appreciated. Thank you.

Gordon Foster

 

[Allen Browne]

Do you really need the Order Detail Details table?

If you must enter multiple serial numbers, could this not be done on
separate lines of the Order Detail table? If you have a Quantity field, it
would be easy enough to ensure this contains the value 1 if there is a
serial number.

For the purpose of printing an invoice, the report could group by ProductID
and PriceEach, and show the count if that is the desired output.

 

[Gordon Foster]

Hi Allen,

I 'think' I know what you are saying but we want know what serial numbers
were shipped together for one item. Say if there are 10 kits which have 2
serialized products in each kit, we want to know what the pairs are (or in
the future if there are 3 serialized products per item shipped then this way
I can accommodate it because it is dynamic).

We used to have a printout that was grouped by part number which is what I
think you were trying to suggest, there was only ever one serial number per
part number though and looked similar to the following:

[Part Number 1]
[Serial 1]
[Serial 2]
[Part Number 2]
[Serial 1]
[Serial 2]

This shows 4 kits were shipped, 2 of one type and 2 of another. And now we
would like:

[Part Number 1]
[Item 1]
[Serial 1]
[Serial 2]
[Item 2]
[Serial 1]
[Serial 2]
[Part Number 2]
[Item 1]
[Serial 1]

This shows 3 kits were shipped. 2 of one type and 1 of another. The 2 kits
however have 2 serial numbers accociated with them. This way one item can
have as many serial numbers as it needs and we will know what serialized
products were grouped together when shipped. I have actually at this point
almost finished the navigation I am talking about that I would like and if
you'd like to see a code snippet of what I have done I'd be more than happy
to share it (I hope I'm not confusing anyone and you understand what I'm
trying to do)...as I said though, I have something pretty much working now.

Gordon Foster

Do you really need the Order Detail Details table?

If you must enter multiple serial numbers, could this not be done on
separate lines of the Order Detail table? If you have a Quantity field, it
would be easy enough to ensure this contains the value 1 if there is a
serial number.

For the purpose of printing an invoice, the report could group by ProductID
and PriceEach, and show the count if that is the desired output.

<<snip>>

 

[Kelvin]

Your structure looks fine but here's an idea. Instead of using a
sub-sub-form, how about have it call up another form. you could put this in
a loop based on the number of entries to be entered and when all the entries
are made, it will automatically go back to the calling form. As an example,
on the calling form you would enter the Item number and how many serial
numbers are to be entered. In the AfterUpdate event of this field it will
go into a loop to open the new form to enter the serial number. This form
can close itself after the number is entered, then the loop will cause it to
open again to enter the next number. Once it has gone through the loop it
will go back to the calling form and go to the next new record. Since you
have your thing already working, you might not want to try this, but it's an
idea incase you can't get the sub-sub-form option to work.

Kelvin

Hi Allen,

I 'think' I know what you are saying but we want know what serial numbers
were shipped together for one item. Say if there are 10 kits which have 2
serialized products in each kit, we want to know what the pairs are (or in
the future if there are 3 serialized products per item shipped then this way
I can accommodate it because it is dynamic).

We used to have a printout that was grouped by part number which is what I
think you were trying to suggest, there was only ever one serial number per
part number though and looked similar to the following:

[Part Number 1]
[Serial 1]
[Serial 2]
[Part Number 2]
[Serial 1]
[Serial 2]

This shows 4 kits were shipped, 2 of one type and 2 of another. And now we
would like:

[Part Number 1]
[Item 1]
[Serial 1]
[Serial 2]
[Item 2]
[Serial 1]
[Serial 2]
[Part Number 2]
[Item 1]
[Serial 1]

This shows 3 kits were shipped. 2 of one type and 1 of another. The 2 kits
however have 2 serial numbers accociated with them. This way one item can
have as many serial numbers as it needs and we will know what serialized
products were grouped together when shipped. I have actually at this point
almost finished the navigation I am talking about that I would like and if
you'd like to see a code snippet of what I have done I'd be more than happy
to share it (I hope I'm not confusing anyone and you understand what I'm
trying to do)...as I said though, I have something pretty much working now.

Gordon Foster

Do you really need the Order Detail Details table?

If you must enter multiple serial numbers, could this not be done on
separate lines of the Order Detail table? If you have a Quantity field, it
would be easy enough to ensure this contains the value 1 if there is a
serial number.

For the purpose of printing an invoice, the report could group by ProductID
and PriceEach, and show the count if that is the desired output.

<<snip>>

 

[Allen Browne]

Right: you need to identify that particular parts were sold as components of
a kit. That makes sense of what you are doing. (The only other alternative
would be to create the kits at the product level (i.e. products that have
Product Details), rather than at the sales level, but that may not be
appropriate for your business.)

In terms of interface, it is possible in Access 2000 and later to place a
subform in the the Form Footer part of a continuous subform. That may or may
not be useful for you.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen,

I 'think' I know what you are saying but we want know what serial numbers
were shipped together for one item. Say if there are 10 kits which have 2
serialized products in each kit, we want to know what the pairs are (or in
the future if there are 3 serialized products per item shipped then this way
I can accommodate it because it is dynamic).

We used to have a printout that was grouped by part number which is what I
think you were trying to suggest, there was only ever one serial number per
part number though and looked similar to the following:

[Part Number 1]
[Serial 1]
[Serial 2]
[Part Number 2]
[Serial 1]
[Serial 2]

This shows 4 kits were shipped, 2 of one type and 2 of another. And now we
would like:

[Part Number 1]
[Item 1]
[Serial 1]
[Serial 2]
[Item 2]
[Serial 1]
[Serial 2]
[Part Number 2]
[Item 1]
[Serial 1]

This shows 3 kits were shipped. 2 of one type and 1 of another. The 2 kits
however have 2 serial numbers accociated with them. This way one item can
have as many serial numbers as it needs and we will know what serialized
products were grouped together when shipped. I have actually at this point
almost finished the navigation I am talking about that I would like and if
you'd like to see a code snippet of what I have done I'd be more than happy
to share it (I hope I'm not confusing anyone and you understand what I'm
trying to do)...as I said though, I have something pretty much working now.

Gordon Foster

Do you really need the Order Detail Details table?

If you must enter multiple serial numbers, could this not be done on
separate lines of the Order Detail table? If you have a Quantity field, it
would be easy enough to ensure this contains the value 1 if there is a
serial number.

For the purpose of printing an invoice, the report could group by ProductID
and PriceEach, and show the count if that is the desired output.

<<snip>>

 

[Gordon Foster]

Hi Kelvin,

Thank you very much for your input. I thank Allen as well. I was assigned
to something other than the database yesterday but am back on it now. I do
like your idea of calling up another form and think that is the way that I
am going to implement my solution.

Again, thank you both for your valuable input.

Gordon Foster

Your structure looks fine but here's an idea. Instead of using a
sub-sub-form, how about have it call up another form. you could put this in
a loop based on the number of entries to be entered and when all the entries
are made, it will automatically go back to the calling form. As an example,
on the calling form you would enter the Item number and how many serial
numbers are to be entered. In the AfterUpdate event of this field it will
go into a loop to open the new form to enter the serial number. This form
can close itself after the number is entered, then the loop will cause it to
open again to enter the next number. Once it has gone through the loop it
will go back to the calling form and go to the next new record. Since you
have your thing already working, you might not want to try this, but it's an
idea incase you can't get the sub-sub-form option to work.

Kelvin

<<snip>>