Subform not syncing correctly, get the same error. I setup the subform as discussed earlier in this thread
Sub records?
- Thread starter mounty76
- Start date
- Local time
- Today, 11:09
- Joined
- Feb 19, 2002
- Messages
- 45,765
The process is transparent. That's how easy it is.
It is almost impossible to debug a problem like this without a copy of the database. Can you create a small database that replicates the problem?
Just to be clear, the subform is actually embedded on the mainform, right? You are not calling a popup form a subform are you?
It is almost impossible to debug a problem like this without a copy of the database. Can you create a small database that replicates the problem?
Just to be clear, the subform is actually embedded on the mainform, right? You are not calling a popup form a subform are you?
Hi Pat,
Thanks, I've stripped it right back, there are a few fields in the tables that are/were linked to other tables, anyway the fault is still there when you open frmPO, enter the details is fine, but then when you try to add a line item the error comes up
Thanks, I've stripped it right back, there are a few fields in the tables that are/were linked to other tables, anyway the fault is still there when you open frmPO, enter the details is fine, but then when you try to add a line item the error comes up
- Local time
- Today, 11:09
- Joined
- Feb 19, 2002
- Messages
- 45,765
The problem is that the query for the subform includes the parent table. It should ONLY include the details table. AND, instead of selecting POID from the child table, it is selecting it from the parent table. In the parent table, the POID is an autonumber and cannot be updated so that is most likely causing the problem.
Do two things.
1. update the query for the subform to remove the PO table
2. Modify the Master/child links to fix the name of the child link to be just POID
In addition, there are other issues.
1. Referential integrity is not enforced. That allows you to just go to the subform and add a row without adding a parent record. This record becomes an orphan because it has null as the value of POID so it has no FK
2. The POID in the details table has 0 as the default, remove the zero. The default MUST be null
3. The POID in the details table is not defined as required. A detail item with out a FK is useless so POID must be required.
4. Remove the extraneous index on POID in the details table. When you define the relationship using the relationship window, Access automatically creates a non-unique index for the FK. You don't need two of them.
5. Once you fix the above, you will get an error if you try to add an item without a PO. I prefer to trap this error and provide my own message. Put the code to trap the error in the subform's BeforeInsert event so you can clear the typing in the subform and put the cursor back in the main form.
6. And finally, the advice we seem to have to give to everyone. Do NOT use embedded spaces in your object names. Do NOT use special characters. Limit yourself to a-z, A-Z, 0-9, and the underscore. Also avoid like the plague reserved words, especially those of properties and functions, they will also come back to haunt you.
Do two things.
1. update the query for the subform to remove the PO table
2. Modify the Master/child links to fix the name of the child link to be just POID
In addition, there are other issues.
1. Referential integrity is not enforced. That allows you to just go to the subform and add a row without adding a parent record. This record becomes an orphan because it has null as the value of POID so it has no FK
2. The POID in the details table has 0 as the default, remove the zero. The default MUST be null
3. The POID in the details table is not defined as required. A detail item with out a FK is useless so POID must be required.
4. Remove the extraneous index on POID in the details table. When you define the relationship using the relationship window, Access automatically creates a non-unique index for the FK. You don't need two of them.
5. Once you fix the above, you will get an error if you try to add an item without a PO. I prefer to trap this error and provide my own message. Put the code to trap the error in the subform's BeforeInsert event so you can clear the typing in the subform and put the cursor back in the main form.
Code:
If IsNull(Me.Parent.POID) then
Msgbox "Please add a PO first", vbOKOnly
Cancel = True
Me.Undo
Me.Parent.PODate.SetFocus
Exit Sub
End if
LarryE
Active member
- Local time
- Today, 08:09
- Joined
- Aug 18, 2021
- Messages
- 847
As I said, open the PODetails table and select the POID field (or whatever you have named it) that is linked to the main tblPONumber table. There will be a property sheet showing at the bottom that looks like the attached picture. There is a drop-down arrow to make a selection. Make sure the Indexed property (as shown above) Yes (Duplicates OK) is selected. Also make sure that field is NOT set as a Primary key. This detail table should have its own Primary key.
- Local time
- Today, 11:09
- Joined
- Feb 19, 2002
- Messages
- 45,765
Do NOT select an index on a FK. Access automatically creates HIDDEN indexes. The one shown here is redundant. Set the value to No. If you have a relationship defined AND you add an index here you end up with TWO which is not good. You can see them both by running the Access documenter if you question my recommendation.
HI All, thanks for all the help, much appreciated. Am making progress!!
Three things.....
1. Modify the Master/child links to fix the name of the child link to be just POID - I'm assuming you mean delete the relationship in the relationship window then remake that link? It was just POID in there so I think I'm barking up the wrong tree here?
2. Referential integrity is not enforced - The checkbox is not highlighted? But with the error message on there and default set to NIL will this kind of ensure the same thing?
3. Now when trying to add a record in the POdetail I get an error message as below. When I enter a description of a product it puts the PODetail ID as zero and then on the next line down it puts in the POID from the main form. I'm guessing this is because of an issue with 1. above....
Thanks for your help and patience!!
Three things.....
1. Modify the Master/child links to fix the name of the child link to be just POID - I'm assuming you mean delete the relationship in the relationship window then remake that link? It was just POID in there so I think I'm barking up the wrong tree here?
2. Referential integrity is not enforced - The checkbox is not highlighted? But with the error message on there and default set to NIL will this kind of ensure the same thing?
3. Now when trying to add a record in the POdetail I get an error message as below. When I enter a description of a product it puts the PODetail ID as zero and then on the next line down it puts in the POID from the main form. I'm guessing this is because of an issue with 1. above....
Thanks for your help and patience!!
- Local time
- Today, 11:09
- Joined
- Feb 19, 2002
- Messages
- 45,765
1. No. I said to modify the links. Because of the extraneous table in the Recordsource, the child name is invalid.
2. There is no substitute for RI. Why would you NOT want to have the database engine enforce something that you would otherwise have to write code to enforce?
3. You did NOT remove the extraneous table. The master/child links are NOT set at all now. In the earlier version, the name of the child was wrong.
I can fix it for you but that will not help you to understand the problem. You will keep making the same mistake.
Hi Pat, thanks for this......I'm completely lost now......I'm happy to make the changes but if you can explain what I need to do then I'll be able to follow it through and hopefully better understand....thank you in advance
Sorry I don't know what this means "extraneous table in the Recordsource"?
- Local time
- Today, 11:09
- Joined
- Feb 19, 2002
- Messages
- 45,765
The Recordsource for the subform includes BOTH the PO and the POdetails tables. Get rid of the PO. All the PO data is being displayed by the mainform. The subform shows only the data from the details table so there is no reason to include the PO table. There is not a single field you need from it. Remember, the master/child links are what is going to pull the PK of the parent record and place it into the FK in each new details record and you don't have to do it yourself.
THEN open the PO main form in design view, click on the subform control. NOT the subform in the control. Just highlight the frame. On the Data tab of the properties dialog, choose POID as the name for both the master and child links.
That will solve the error.
Then go back and reread my earlier post and work on the other problems you have. Referential integrity is very important and you need to set the properties correctly on the fields in the detail table. Again, remove the zero as the default for the FK field. NEVER, EVER leave 0 as the default for the FK. Access doesn't know what you are using this field for when you define it so it just arbitrarily defaults all numeric fields to 0 to protect you from yourself in case you don't understand how to handle nulls. HOWEVER, autonumbers do not generate 0 as a valid autonumber so it would NEVER be valid and will interfere with enforcing RI. Then mark the FK field as required. Nor all relationships need to have the FK marked as required but a hierarchical one like this does. The details record has no meaning whatsoever unless it is linked to a PO.
THEN open the PO main form in design view, click on the subform control. NOT the subform in the control. Just highlight the frame. On the Data tab of the properties dialog, choose POID as the name for both the master and child links.
That will solve the error.
Then go back and reread my earlier post and work on the other problems you have. Referential integrity is very important and you need to set the properties correctly on the fields in the detail table. Again, remove the zero as the default for the FK field. NEVER, EVER leave 0 as the default for the FK. Access doesn't know what you are using this field for when you define it so it just arbitrarily defaults all numeric fields to 0 to protect you from yourself in case you don't understand how to handle nulls. HOWEVER, autonumbers do not generate 0 as a valid autonumber so it would NEVER be valid and will interfere with enforcing RI. Then mark the FK field as required. Nor all relationships need to have the FK marked as required but a hierarchical one like this does. The details record has no meaning whatsoever unless it is linked to a PO.
HI Pat, thanks for this. It was getting very messy so deleted them and started again from scratch taking into account everything said. I now have it working properly, the FK is automatically done because the parent/child relationship is correctly setup, RI is enforced and all is good, thank you.
- Local time
- Today, 11:09
- Joined
- Feb 19, 2002
- Messages
- 45,765
You're welcome. Did you also remove the 0 as the default for the FK and add the required constraint?
On other thing....haha!!
I want to be able to 'clone' an order including the line items, obviously I'll have to make an append query but how would I do this and the PODetails FK recognise the new Primary Key for the new PO?
