Full Version: subform record is a step behind the main
UtterAccess Forums > Microsoft® Access > Access Forms
schutterp
Can anyone tell me why my subform is a step behind the main form?
cenario: choose a number in parent, populate child, go to next record, choose a diff number in main, populates child with previous number ...
theDBguy
Can't say for sure... you might be doing or missing some requeries along the way.
Jack Cowley
Are your Master/Child links set up correctly? Are the tables (subform and main form) joined on the PK of the table the main form is based on?
schutterp
Ok, I'm seeing a problem in my setup, the subform is on the one side of the relationship. Is there any way to make this fly???
form in a form in a form in a form is probably not a great idea anyway, but I am stumped.
Care to read my story? Maybe not, but here it is:
After choosing a customer, an order number and date are entered into the main form. This filters a subform where several parts can be entered for each order along with details about the order. The procedure is to enter the part or pick from the list of parts (the pk for tblPARTS is an fk in tblORDEREDPARTS which is the record source for the subform), then add details and go to the the next record to enter another part for the same order.
Now here's the tricky part: one of the details for each part involves a many-to-many relationship with services. So I want to show in a continuous subsubsubform the services related to the part or be able to enter the services if the part is new to the database.
I have been able to add services to a part while being unable to call up the records that already include the part in the fk. I now have it to where I can get one record complete, but now I can't get another part onto the same order...
If you can help, you will be my hero. In fact, you already are for previous help I have received!
Jack Cowley
Is this accurate:
blParts
PartID (PK and auto)
PartNumber
Description
...etc...
tblServices
ServiceID (PK and auto)
ServiceDescription
...etc...
tblPartsAndServices
PartsAndServicesID (PK and auto)
PartID (FK)
ServiceID (FK)
If the table above is your 'many-to-many' relationship then create query based on this so you can show the PartNumber and the ServiceDescription. If you use this query as the Row Source for a combo box you can select the PartNumber and its related Service for that record and save the PartsAndServiceID in your table, not PartID and/or ServiceID
I hope that makes sense - Again, in your form you do not pick a PartID or ServiceID but the PartsAndServiceID...
Now I will cross my fingers and hope I understood your question...
Jack
schutterp
You have the many-to-many correct, but where are you suggesting I put the combo box based on the query created?
Jack Cowley
You have a form or subform where you save the PartNumber in a record. Instead of selecting a PartNumber from a combo box based on the Parts table save the PartsAndServiceID based on that table.
hope that made sense...but let me say that if you now have a combo where you select the PartNumber replace that combo with the one I suggested... I assume that if there is 1 PartNumber and you will need two different Services that you will use another record for that PartNumber and its Service...
hth,
Jack
schutterp
pkPart ID has foriegn keys in both the partsandservice table and the orderedparts table. The main form's record source is the ordered parts table. So what should the subform's recordsource be? When I make it the partsandservices table I can't seem to get the previous partsandservices to display based on the combo box in the main form since the combo box is bound to the partID foriegn key field in the orderedparts table.
e're getting there ... thank you for your help!
Jack Cowley
The OrderParts table, which I am not sure what table that is, does not have the PartID as I understand what you are trying to do...
This is getting a bit confusing for me... I assumed, and maybe wrongly so, that you have an Order for a customer and that Order has a list if items ordered (a subform). An item in the customers order contains a Part but that part also has a service component. So the order my be for a quantity of 10 PartNumber ABC with a Service of 123. The next item might be for 10 of PartNumber ABC with a Service of 276. Is that basically correct?
If that is basically correct then your subform of items the customer is ordering would have a Combo Box based on the tblPartsAndService so that your users can select PartNumber ABC and Service 123 or one item in the order and then do the same for the second item except select the PartNumber ABC and Service 276. The PartsAndServiceID will be saved in this table not that PartID or ServiceID.
Now when you make of the tblPartsAndService table you have a form with a combo box for PartID and it is based on your tblParts. The ServiceID is also a combo box but based on tblService. You go to a new record, select the PartNubmber and then you select the ServiceNumber...
I hope that this makes sense and if not then just let me know...
Jack
schutterp
I apologize for creating some confusion.
ach part may or may not require one or more services to be completed. So an item in a customers order will contain a part, but the services performed on the part are not specified in the order. So order 1 could have three items - 10 quantity of part ABC due tomorrow, 10 quantity of part ABC due next week and 10 quantity of part BCA. Part ABC would require Service 123 and 276 every time it is ordered, and the customer's order item doesn't specify the service, instead that is determined by the part.
After the customer's order is completed, they might order the part again some time, let's say Order 2 which has two items. After Order 2 is entered in the main form, I choose the part ABC from a combobox (controlsource fkPartID) and see in a subsubform that service 123 and 276 are required for the part. Then I enter the item details such as the due date and move to the next record in tblORDEREDPARTS. Now this item is for a new part and so after I enter it into the combobox (controlsource fkPartID) and add it to the list, I should be able to choose the required services in the subsubform combobox.
So, I have tblORDEREDPARTS for each item in each order. It contains PartID foriegn key and OrderID foriegn key. tblPartsandServices contains a PartID foreign key and a ServiceID foreign key.
Thanks again, let me know what I can clarify and I will do so asap!
Jack Cowley
No need to apologize as it is a bit confusing for this old fellow! Your latest post raises a question for me... Will some customers use PartNumber ABC and Service 123 and others use PartNumber ABC and Service 345? In other words, can a PartNumber ABC have different Services for different customers?
lso, how do you handle PartNumber ABC due now and PartNumber ABC due at some time in the future? Do you create a separate record for each?
I'm trying to sort out how best to accomplish what it is you want to do and if each order can be unique then your setup of form/subform/subsubform sounds good...
Jack
schutterp
Part ABC won't be ordered by more than one customer. Also, the service used for the part is for internal use. The service refers to the services outsourced. So if part ABC is ordered, we need to note when entering the order what outsourcing services will be required before the part is sent to the customer. As a side note, when the part reaches a certain point, a form is used to create our own order for the service that is required for the part.
There is a separate record for parts due now and parts due some time in the future.
Have a look at the visual layout of the relationships. This may help you see where I am going with this. The purpose of recording data about the services required for the parts is to be able to track the parts when they are sent out for outsourcing services and track costs. That is where tblOutsourcePO and tblOutsourceOrders enter the equation.
Jack Cowley
Hmmm. Now I am getting really confused as this process is getting more and more complicated with each post... It seems to me the tblParts does not belong in tblORDEREDPARTS. pkPOUtsourcePts belongs in tblOutsourceOrders and then tblOUTSOURCE and tblPARTS linked to tblOUTSOURCEPARTS.
Think about that and see if that sounds like the route you want to take... Unfortunately I am now outta here until sometime later this afternoon... Hopefully this will give you an idea as we have come a long way from a subform that does not update properly....
hth,
Jack
schutterp
I've left you Utterly confused!
There is little doubt whether tblPARTS should be in tblORDEREDPARTS. This is because parts, quantity, and due date are the main attributes of each record in tblORDEREDPARTS - and this table is the heart of the whole database.
Putting pkOutsourcePts in tblOutsourceOrders instead of pkOutsourceID may be a good idea, but I don't see it fixing my original problem. I will make the change as suggested.
Ostill feel confident in my table/relationship structure. My original question may have changed to: How can I populate a continuous subsubsubform [whose recordsource is tblOUTSOURCEPARTS] upon populating a combobox based on tblORDEREDPARTS.fkPartID in the subform and also be able to enter new records into tblOUTSOURCEPARTS when the PartID is entered for the first time into tblPARTS through the same combobox in the subform???
Now how Utterly simple is that?
Thanks for your help on this so far! I know we're close...
Jack Cowley
You certainly have it right when you suggest you have left me Utterly Confused...Which is easy to do!!!
On the case of tblORDEREDPARTS and TBLOUTSOURCEPARTS the table tblParts is just a lookup. I would remove the link between tblPARTS and tblOUTSOURCEPARTS as you do not need it. fkPartID in tblORDERPARTS would be a combo box based on tblPARTS.
If tblORDERPARTS is your subform and tblOUTSOURCEPARTS is your subsubform then the link between these tables should be pkBrotherssNo from tblORDERPARTS. If tblOUTSOURCEPARTS is a continuous form it should show the records that are related to tblORDERPARTS and I see no reason for their to be a PartID in the tblOUTSOURCPARTS. Read on....
To my way of thinking you do not need the subsubform because tblOUTSOURCEPARTS should be a lookup combo box on tblORDERPARTS where you save pkOutsourcePts not PartID.... You select the part with the correct service and that is it. IF there is no service for a specific part then you have to go the appropriate form and add the service for the selected part....
I hope that I am making some sense, because your setup still does not look correct to me...but I have been wrong so many times that I have lost count!!!
Jack
schutterp
The problem I thought I would have without fkPartID in tblOUTSOURCEPARTS is that when an order is entered for a part that has been done before, there would be added data entry or at least duplicate records for each part/service.
uppose a new record is entered into tblORDEREDPARTS and a fkPartID is chosen from the combobox. If a previous record in tblORDEREDPARTS included the same fkPartID and had associated records in tblOUTSOURCEPARTS, how would I query those records?
Then, would I be creating new records in tblOUTSOURCEPARTS with the current pk in tblORDEREDPARTS, so that next time the services are multiplied because I saved several records in tblOUTSOURCEPARTS (one for each time the part is ordered...)
Am I understanding your approach?
Thanks,
Ryan
Jack Cowley
tblOUTSOURCEPARTS is a junction table between two other tables...it is the table that joins the two tables in a Many-to-Many relationship. What you have is a combo box in your form based on tblORDEREDPARTS and that combo box is based on tblOUTSOURCEPARTS. What you do is open that combo box, find the Part AND the Service that you want and you save the 'pkOutsourcePts', not the PartID.

To make it easier to find a Part in the combo based on tblOUTSOURCEPARTS you can have another combo box based on the PartID, PartDescription from tblPARTS. This PartsID combo will 'filter' the combo based on tblOUTSOURCEPARTS so when you open it you only have to scroll though parts that have existing Services.

If the part you want from tblOUTSOURCEPARTS does not have a Service that you need for the current order then you will need to open a form that allows you to add the PartID and ServiceID to tblOUTSOURCEPARTS. Once that is done you can come back to the current combo box and select the new Part/Service record....

I hope you understand what I am suggesting, as it is the way to go. I think you are trying to do too many things with a single form and I cannot think of a way to do that. You will need to open a form to add a service and/or part to the tblOUTSOURCEPARTS if the combination does not exist. You can probably do it with code, behind the scenes, but opening a form and making the appropriate selections is probably the better way to go...

Let me know if this sounds like the solution that will work for you...

EDIT - In the form with the Part combo box that combo box would NOT be bound to a field in the underlying table. You want to change the current PartID to the PK of tblOUTSOURCEPARTS....

Jack
Edited by: Jack Cowley on Wed May 14 11:25:12 EDT 2008.
schutterp
I think what you're suggesting would require a link table between tblORDEREDPARTS and tblOUTSOURCEPARTS because it is a many-to-many relationship ...
Jack Cowley
tblOUTSOUCEPARTS is the junction table between tblParts and tblOutSource and its primary key, pkOutSourcePts, would be Foreign Key in tblORDEREDPARTS. tblOUTSOURCEPARTS is a 'lookup' table and would be the Row Souce for a combo box in your form, as I described earlier...
On tblORDEREDPARTS change the 'fkPartID' to 'fkOutSourcePts' and then you can join the two in your relationship window if you like. It will work with or without the link in the relationship window.
I hope that I am not muddying the waters for you....
Jack
schutterp
As I was saying, I don't think this can work without a much larger overhaul of the table structure. This is because the primary key in tblORDEREDPARTS has a presence in several other relationships and it is parts, not outsourceparts, that are a major characteristic of each record. Therefore, if I put pkOutsourcePts into tblORDEREDPARTS in the place of fkPartID, I would be creating many more records in tblORDEREDPARTS since each part can have several records in tblOUTSOURCEPARTS. That is, unless it was a many-to-many relationship between tblORDEREDPARTS and tblOUTSOURCEPARTS.
Jack Cowley
Make "pkBrothersNo" an fk in tblOUTSOURCEDPART. Make tblOUTSOURCEDPARTS a subform of your form based on tblParts and linked on 'pkBrothersNo'. Select the PartID and ServiceID from combo boxes, based on their respective tables, in the subform. Now you can have one item/order in tblORDEREDPARTS that has many parts and services...
Am I getting close to what you want? Sorry that I have not caught on sooner, but hopefully I have finally stumbled on to what you want to do... I appreciate your patience!!
Jack
schutterp
Getting warmer ... Now would you say I could just replace fkPartID in tblOUTSOURCEPARTS with fkBrothersNo? I'm thinking with pkPartID's presence in tblORDEREDPARTS, it would be redundant to keep it in tblOUTSOURCEPARTS ...
Thank you for your input so far, we're definitely getting there!
Ryan
schutterp
Then again ... my concerns in this morning's post may still pose a problem:
<
Whadayathink?
Jack Cowley
Ryan -
blORDEREDPARTS
OrderPartsID (FK to tblORDEREDPARTS)
PartID (FK to tblParts)
ServiceID (FK to Service table)
In the above some of the field names are not the same as your actual field names as I can't see them when I am tying here, but the setup should be what you are looking for... In the above setup the Master/Child link to the subform (based on tblOUTSOURCEDPARTS) is OrderPartsID...which I think is actually 'pkBrothersNo'....
In the subform you select each Part and then the Service and then those parts and services are unique to that specific order.
hth,
Jack
Jack Cowley
Ryan -
Yes, there will be 'duplicate data' in the table "tblOUTSOURCEDPARTS" in a record for OrderNo 123 having the same PartID and ServiceID as Order 138. This is OK an is normal... Your table "tblOUTSOURCEDPARTS" may look like this:
tblOUTSOURCEDPARTS
OutsourcedPartsID = 8
PartID = 13
ServiceID = 27
OutsourcedPartsID = 8
PartID = 14
ServiceID = 27
OutsourcedPartsID = 9
PartID = 13
ServiceID = 27
This is normal and is perfectly normal. Duplicate data might be were you put EmployeeLastName and EmployeeFirstName in many tables instead of referring to the EmployeeID in those tables...
hth,
Jack
schutterp
I will spend some time with this and post a response tomorrow.
Thanks again for all your help!
Ryan
Jack Cowley
Ryan -
You are most welcome and thank you for your forbearance....
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.