Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98

    Cannot add record because a related record is requires in table

    Greetings all,

    Here is my scenario:
    I have a DB that deals with inventory, both owned and 3rd party client inventory.
    When equipment comes in from a customer, I have a form that allows the receiver to create an incoming invoice in tblIncInvoicecC.
    Next, the person will enter the equipment that physically came in on that invoice. So the next form they use is one to enter all of the equipment information that gets stored in tblClientInventory.

    I have a few combo boxes on this second form that allows the user to select controlled input such as the Model, Category, Status, Manufacturer, and Inbound Invoice. This Inbound Invoice combo box is set to pull up the list of previously created invoices from tblIncInvoicesC. When all of the information is entered and I attempt to save the record entry, I get the following error:
    Click image for larger version. 

Name:	Capture2.PNG 
Views:	14 
Size:	3.8 KB 
ID:	42772

    For a bit of reference, let me recap the other combo boxes:
    The Model combo box looks up Model types from tblModelC and when selected, that information, along with all of the rest, is written to tblClientInventory.
    The same process is applied to Status from tblStatusC, Category from tblCategoryC, and Manufacturer from tblManufacturerC. All of these combo boxes work perfectly, but for some reason the invoice combo box does not.

    I have used the same methods for generating the Control Source, Row Source, and Row Source Type. For some reason, the invoice number will just not allow the form to write the record.

    Here are the combo box information sets:
    Model: Control Source = Model
    Row Source = tblModelC
    Row Source Type = Table/Query

    Status: Control Source = Status
    Row Source = tblStatusC
    Row Source Type = Table/Query
    etc, etc.

    For the Invoice combo box I have:


    Inbound Invoice: Control Source = Inbound Invoice
    Row Source = tblIncInvoicesC
    Row Source Type = Table/Query

    I understand the error says that it is trying to write something to a field that does not exist... but why? Just like the Category, Model, Manufacturer, I am simply selecting the Invoice Number so that it can write it to tblClientInventory with all of the other information too.

    Now, here are the tables:
    tblCategoryC
    Click image for larger version. 

Name:	CategoryC.PNG 
Views:	14 
Size:	7.1 KB 
ID:	42773

    tblModelC
    Click image for larger version. 

Name:	ModelC.PNG 
Views:	14 
Size:	18.7 KB 
ID:	42774

    tblIncInvoicesC
    Click image for larger version. 

Name:	IncInvoicesC.PNG 
Views:	14 
Size:	10.9 KB 
ID:	42775

    tblClientInventory
    Click image for larger version. 

Name:	ClientInventory.PNG 
Views:	14 
Size:	13.8 KB 
ID:	42776

    I am assuming the error is telling me that the record in tblIncInvoicesC does not exist, yet that is impossible because I can select it from the combo box. Perhaps it is trying to write a different value where the field names do not match?

    Any help would be greatly appreciated.
    Thanks.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Have you set a relationship between the two tables: tblClientInventory and tblIncInvoicecC

    ...When all of the information is entered and I attempt to save the record entry, I get the following error:
    The error message is saying that the record can not be saved in tblClientInventory because a related record is required in tblIncInvoicecC

    Perhaps you can show us a screen shot of your relationships or post a copy of the db
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Quote Originally Posted by Bob Fitz View Post
    Have you set a relationship between the two tables: tblClientInventory and tblIncInvoicecC

    The error message is saying that the record can not be saved in tblClientInventory because a related record is required in tblIncInvoicecC

    Perhaps you can show us a screen shot of your relationships or post a copy of the db
    Thanks for the reply.
    Here is a shot of the relations...
    Click image for larger version. 

Name:	tblRelations.jpg 
Views:	15 
Size:	99.2 KB 
ID:	42780

    Regards.

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    So, are you using a main form with a sub form

  5. #5
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Quote Originally Posted by Bob Fitz View Post
    So, are you using a main form with a sub form
    Negative. I initially was going to do that, main form for the Incoming Invoice creation then subform for the equipment information, but I decided against it because there are times when the equipment will arrive and will not get inventoried right away. So we enforce creating the invoice right away, to show it arriving, then get to the inventory of it when we can.
    So, I have separate forms for both of these for right now. Single form for Incoming invoice and single form for the equipment information.

    Regards.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I am assuming the error is telling me that the record in tblIncInvoicesC does not exist
    It means, you cannot create a child record because there is no related parent record.
    You're probably trying to insert the Inbound Invoice value but there is no InvoiceID yet.
    EDIT - could also be Outbound Invoice, but the restriction is the same for both.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Quote Originally Posted by Micron View Post
    It means, you cannot create a child record because there is no related parent record.
    You're probably trying to insert the Inbound Invoice value but there is no InvoiceID yet.
    EDIT - could also be Outbound Invoice, but the restriction is the same for both.
    Well, if the invoice was already created, which it was, then there is an ID associated with it in tblIncInvoicesC... which is why I am able to select it from the combo box. When writing the record to tblClientInventory, it should write the ID number in the Invoice Number field accordingly... or am I incorrect in this manner?

    In similar fashion, I have combo boxes for Type, Category, Model, Status, and Manufacturer which all operate accordingly. They allow me to select from the combo box and then write it all to tblClientInventory accordingly. I have tested these by disabling the Invoice combo box and everything works.

    Regards.

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    When writing the record to tblClientInventory, it should write the ID number in the Invoice Number field accordingly...
    So, you have a combo on the second form whos data comes from the table one the one side of the relationship. Can you show us the SQL statement that is used as the Row Source property of that combo
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Quote Originally Posted by Bob Fitz View Post
    So, you have a combo on the second form whos data comes from the table one the one side of the relationship. Can you show us the SQL statement that is used as the Row Source property of that combo
    I'm not certain exactly which you are referring to... but here's a screenshot, in design view, of the second form, for entering equipment info (the form with the error), and the Inbound Invoice combo box selected showing the properties sheet.

    Click image for larger version. 

Name:	Properties1.PNG 
Views:	10 
Size:	45.6 KB 
ID:	42782
    Attached Thumbnails Attached Thumbnails Properties1.PNG  

  10. #10
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    It is my understanding, if I am correct, that the following is true:
    • Control Source is where the information will be stored/written; in this case the record info will be written to the Inbound Invoice field of tblClientInventory
    • Row Source is where the lookup information comes from; in this case tblIncInvoicesC to get the invoice number and associate it with its corresponding ID to be saved to tblClientInventory
    • Row Source Type is the method in which the information is obtained; in this case via Table/Query
    • Bound Column is the column where the the pertinent information to be stored is; the ID number of the record


    Is that correct?

    Regards.

  11. #11
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by FriQenstein View Post
    It is my understanding, if I am correct, that the following is true:
    • Control Source is where the information will be stored/written; in this case the record info will be written to the Inbound Invoice field of tblClientInventory
    • Row Source is where the lookup information comes from; in this case tblIncInvoicesC to get the invoice number and associate it with its corresponding ID to be saved to tblClientInventory
    • Row Source Type is the method in which the information is obtained; in this case via Table/Query
    • Bound Column is the column where the the pertinent information to be stored is; the ID number of the record


    Is that correct?

    Regards.
    That is also my understanding
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Can you upload a zipped copy of the database with any confidential data removed?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    re last post - all correct except possibly the last one. If the ID field is an autonumber field, you cannot write to it from a bound column.
    other post - I don't know the answer because I don't know how your data input process works. Your comment might be correct, but not if you're trying to put the inbound or outbound invoice number into inventory table and the PK from invoices has not been created yet. All I can tell you for sure is what the error message means. Without seeing a db, I think it's up to you to find the reason. Part of your difficulty may be that you're not using main/subform design - unless you're saying the process is to create child/many records with no parent record. I've only encountered that restriction once in my many years of dabbling in Access. I used a main/subform approach, but I used a combination of user name and some other tidbit to create a unique parent record. The process was such that the details of the order needed to be submitted so that someone else could create the order identifier. Not sure if that parallels what you need to do or not.

    EDIT: BTW, I only used these references
    if you're trying to put the inbound or outbound invoice number into inventory table and the PK from invoices has not been created yet.
    as an example - a guess. Don't focus on those fields; rather understand the message and use that knowledge to find the cause.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Is this a new problem with a form which had been working correctly or is this a new form that you are developing and having problems with
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  15. #15
    FriQenstein is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    98
    Ok, I think I may have found the issue.. or at least what lead to the issue.

    Yes, the form was working previously, and now is not. When I copied the form to from my backup, apparently things got a bit borked at that point... (yes, I said borked).

    So, for the Row Source, instead of something like: SELECT [tblClients].[ClientID], [tblClients].[ClientName] FROM tblClients ORDER BY [ClientName];
    it got changed to something like in my previous shots showing tblIncInvoicesC for the Row Source.

    I will attempt to correct it and post back in a while...

    Regards.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-12-2020, 12:27 AM
  2. Replies: 4
    Last Post: 12-04-2019, 02:33 PM
  3. New Record Requires a Related Record
    By DigitalAdrenaline in forum Forms
    Replies: 9
    Last Post: 06-14-2019, 02:51 AM
  4. Replies: 17
    Last Post: 01-25-2018, 08:03 PM
  5. Replies: 15
    Last Post: 03-05-2015, 03:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums