Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2008
    Posts
    39

    Unanswered: Data entry form? Referential integrity error?

    This should be the world's simplest issue, yet I've been unsuccessfully googling it now for hours, reading forums... white papers...

    I've got a simple, single-table, bound form.
    Since it's for inputting Job Orders into the system, most of the time this form will be used for input.

    There's a relationship between my Job Orders table and my Customer table. I currently have referential integrity turned on, and believe I shouldn't expect to have to turn it off...

    However, as soon as I input a new Job Order number into the first control (which is the key for the Job Order table), an error message pops up:
    You cannot add or change a record because a related record is required in table 'Customer'.
    ...and that makes sense- I have a control for 'Customer Name' on my form which will pull the CustomerID from Customer - if I can only get there to set it.

    Obviously, there's a relationship between Customer and Job Orders, based on CustomerID, and I don't think I should have to remove it or remove enforcement.

    Isn't this simply the world's most basic situation? A data entry form into a table with a relationship?

    Please help restore my sanity, thanks!!!

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Try using a subform.

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Or perhaps on your form you could put the Customer control before the Job Order Number control.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  4. #4
    Join Date
    Jun 2008
    Posts
    39
    I thought about that... essentially using the Customer table to bind the main form to, right?

    I didn't try it because I didn't see how it would avoid this error, unless I'm picturing the cause of the error incorrectly:
    When a user inputs a PO number, the Customer Name still isn't selected, and I'd still get the error, correct?

    Or are you suggesting this to attempt to force them to select (or input) the Customer Name first?
    It's not bad logic from a database integrity standpoint... mulling over "user friendliness".

    Primarily what I don't like also about the subform is that if I select a customer, then my subform will suddenly contain a dump of all the PO's for that customer... and they'd have to scroll waaaaay down to the bottom to input a new one. Or, if I forced it somehow to be an input-only form, then I remove their ability to update (should a date change, for example).

    It's such a simple form, that's why this seems so complex:
    Job Number
    Customer Name (really CustID behind the scenes)
    PO Number
    PO Quantity
    PO Delivery Date
    Assembly Part Number
    Assembly Revision Number

    Thoughts?

  5. #5
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Quote Originally Posted by geolemon
    Or are you suggesting this to attempt to force them to select (or input) the Customer Name first?
    Yes that is my suggestion.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •