Form designing.

[glen]

Hello,
I have some problems here in form designing. I have one main form with sub form.
The main form is for work orders numbers, i.e xe305,xe306...........
The sub form is for detail of items in each order number.
The problem is now that normally when we click on next record of main form navigation button it will jump us on
next record, means if we are on work order # xe305 then it will jump to work order xe306 or new blank record.
But what I want that by on clicking next record button of main form it should not jump to next work order number but it should
jump to next blank record in sub form.
for example in work order number xe305 we have to deliver 10 items but in first delivery we deliver only 5 items and 5 items reaming.
So it means in work order number xe305 we deliver 5 items in delivery serial#1.next time when we will deliver the rest 5 items it will be


delivery serial #2.
I am attaching my table relationship and form design. I know there is also problem in my table relationship.
Please guide me properly.
If possible please make a sample demo database for me.
Thanks

[Missinglinq]

Sorry, but common sense should tell you that if you click on the native 'next record' button on the Main Form, it's going to take you to the next record on the Main Form!

Only workaround I can think of would be to hide the native next button, on the Main Form, and replace it with a custom command button, coded to move to the next record on the Subform.

Linq ;0)>

[ssanfu]

From your description, this is the structure I would start out with. I drew this out on paper first, then created the tables.
One order can have many deliveries, and one delivery can have multiple order details (items).

The field names ending in "PK" are for linking tables and shouldn't be displayed on forms/reports.
The two fields "WorkOrder" and "DeliveryNum" are for you to assign your numbers (roll your own number scheme - automatic or manual).

Attachment 9505


The main form's record source would be a query based on the table "Orders".
A sub form's record source would be based on a query based on the table "Deliveries"
A sub sub form's record source would be based on a query on the table "Order_Details"

If possible please make a sample demo database for me

I could do that..... but then you wouldn't have the fun or get the experience..

[glen]

......................

[glen]

From your description, this is the structure I would start out with. I drew this out on paper first, then created the tables.
One order can have many deliveries, and one delivery can have multiple order details (items).

The field names ending in "PK" are for linking tables and shouldn't be displayed on forms/reports.
The two fields "WorkOrder" and "DeliveryNum" are for you to assign your numbers (roll your own number scheme - automatic or manual).

Attachment 9505


The main form's record source would be a query based on the table "Orders".
A sub form's record source would be based on a query based on the table "Deliveries"
A sub sub form's record source would be based on a query on the table "Order_Details"


I could do that..... but then you wouldn't have the fun or get the experience..

Ok Thanks,
I will try myself but if not possible from me then you have to design for me.

[glen]

The main form's record source would be a query based on the table "Orders".
A sub form's record source would be based on a query based on the table "Deliveries"
A sub sub form's record source would be based on a query on the table "Order_Details"


I could do that..... but then you wouldn't have the fun or get the experience..

I made the tables according your design and made relationship.And I think it working fine.
but the problem is now to design forms.
and secondly I am confused about sub sub form.

[glen]

........................

[ssanfu]

The main form would have the info about the Orders. The data is displayed in the detail section.
In the footer would be the sub-form for the "Deliveries" data - displayed in the details section of the sub form.
In the footer of the sub-form would be the sub-sub-form.
The details section of the sub-sub-form would display the Order_Details data.
Pretty much like your form in post #1 (and 4).


(BTW, I would use queries for the record sources of the form so the data can be sorted. If you use tables, the data won't/can't be sorted.)

[glen]

Thank you very much.
I get the design procedure.
Now if there was some problem in record sources, Sorting and filtering then I will ask you.

[glen]

The two fields "WorkOrder" and "DeliveryNum" are for you to assign your numbers (roll your own number scheme - automatic or manual).

I want to assign automatic number to delivery field.
How I can do it?

[ssanfu]

Here are two examples of custom autonumbers:

http://www.rogersaccesslibrary.com/forum/topic395.html
http://www.baldyweb.com/CustomAutonumber.htm

[glen]

Thank you I will try to learn auto number from these tutorials.
But Now I need help in cascading combo boxes.
I want that first I will select department from combo1 then Location so it should show me records of that
department and location.
I m attaching my form design which I made according your assistance and tables relationships.
Regards,

[ssanfu]

Still thinking....

Would you tell me more about locations/departments and post a few examples??
Might have to change the structure again...


Oops, I think I missed the "requested by' field in table "Orders"

[glen]

Still thinking....

Would you tell me more about locations/departments and post a few examples??
Might have to change the structure again...


Oops, I think I missed the "requested by' field in table "Orders"

we have two factories factory A and factory B in Location.
and too many same departments in both locations.
i.e in Factory A we have department "Production" and in factory B also have same department "Production"
we have to deliver items in both locations in same department.
Department - Location
Production - Factory A
Production - Factory B

and requested by field is not important that's why I removed.

[glen]

May be this will help you to understand what I want?