Sub records? (1 Viewer)

mounty76

Registered User.
Local time
Today, 15:06
Joined
Sep 14, 2017
Messages
341
What does that mean? Did you check?

What does that mean? Are you still getting the error? Is the subform not syncing correctly?
Subform not syncing correctly, get the same error. I setup the subform as discussed earlier in this thread
 

mounty76

Registered User.
Local time
Today, 15:06
Joined
Sep 14, 2017
Messages
341
I'm guessing it's an easy thing to do when your an expert on access :D
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:06
Joined
Feb 19, 2002
Messages
42,976
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?
 

mounty76

Registered User.
Local time
Today, 15:06
Joined
Sep 14, 2017
Messages
341
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
 

Attachments

  • Sample.zip
    1.2 MB · Views: 121

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:06
Joined
May 21, 2018
Messages
8,463
Remove tblPO from the subform query. Delete and readd the subform and it will work.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:06
Joined
Feb 19, 2002
Messages
42,976
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.
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
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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 18:06
Joined
May 21, 2018
Messages
8,463
Also compact this thing. It was 10mb went down to 700k
 

Attachments

  • MajPSample.accdb
    700 KB · Views: 100

LarryE

Active member
Local time
Today, 15:06
Joined
Aug 18, 2021
Messages
562
There is no indexed property showing on the property sheet?
1652387022439.png

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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:06
Joined
Feb 19, 2002
Messages
42,976
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.

aaindex.JPG
 

LarryE

Active member
Local time
Today, 15:06
Joined
Aug 18, 2021
Messages
562
I just wanted to make sure it wasn't set to Yes (No Duplicates)
 

mounty76

Registered User.
Local time
Today, 15:06
Joined
Sep 14, 2017
Messages
341
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....

1652457269914.png


Thanks for your help and patience!!
 

mounty76

Registered User.
Local time
Today, 15:06
Joined
Sep 14, 2017
Messages
341
Attached is sample DB of the issues I'm having now, it is almost working! 🤪
 

Attachments

  • Sample.accdb
    792 KB · Views: 103

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:06
Joined
Feb 19, 2002
Messages
42,976
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....
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.
 

mounty76

Registered User.
Local time
Today, 15:06
Joined
Sep 14, 2017
Messages
341
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
 

mounty76

Registered User.
Local time
Today, 15:06
Joined
Sep 14, 2017
Messages
341
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"?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:06
Joined
Feb 19, 2002
Messages
42,976
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.
 

mounty76

Registered User.
Local time
Today, 15:06
Joined
Sep 14, 2017
Messages
341
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:06
Joined
Feb 19, 2002
Messages
42,976
You're welcome. Did you also remove the 0 as the default for the FK and add the required constraint?
 

mounty76

Registered User.
Local time
Today, 15:06
Joined
Sep 14, 2017
Messages
341
Yes I removed this as well, all working good now, thanks again.
 

mounty76

Registered User.
Local time
Today, 15:06
Joined
Sep 14, 2017
Messages
341
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?
 

Users who are viewing this thread

Top Bottom