Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2004
    Posts
    36

    Unanswered: Validation before focus enters subform

    When I click on my subform after entering some data in the primary form, I get this error message:

    "The Microsoft Jet database engine can't find a record in the table <name> with key matching field(s) <name>. (Error 3101)"

    This is because I have not filled in a ItemCode, a combobox, on the parent form and ItemCode is part of a one-to-many relationship that involves automatic filling in of other fields such as ItemDesc, UnitPrice, etc. How do I code the validation that handles such an error?



    Thanks in advance,
    Swee

  2. #2
    Join Date
    Aug 2004
    Posts
    36
    Hmmm, does anyone have any ideas?

  3. #3
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by machix
    Hmmm, does anyone have any ideas?
    Open the Relationships Window. Add the Master Table and the related table onto the Relationships panel if not already added. Drag a join/link between the related key fields set for a one-to-many relationship A/R. dbl-click or right-click on the join line and select Edit Relationship... to oepn the edit window. Select the checkboxes:

    [x]Enforce Referential Integraty
    [x]Cascade Update Related Fields



    Another option if for some reason that doesn't work you can take action on with the 'before update' event of your master form to enter a related id on the sub form field. You can do this with VB code or a Macro.
    Last edited by savbill; 08-30-04 at 23:37.
    ~

    Bill

  4. #4
    Join Date
    Aug 2004
    Posts
    36
    Hi,

    I'm not sure if we are referring to the same thing so I've attached a small db for you guys to play around with and hopefully, understand my problem better.

    1. Open FrmOrderInfoMain
    2. Choose an outlet from the Brand combobox.
    3. Click on the subform. At this point, you will get an error message.


    Grateful and appreciative of any input. Thanks!
    Attached Files Attached Files

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi machix,

    I d/l your database and I know it's late (2:00am) but I think the relationships are off a bit. A bit too tired to check into it now but will look at it all in detail tomorrow and see what's up. That is if someone doesn't beat me to it. But just looking at the structure I didn't see smooth follow through. See you tomorrow evening,

    BUD

  6. #6
    Join Date
    Aug 2004
    Posts
    36
    I see. Well, it's afternoon over here. Difference in time zones. Thanks Bud. Looking forward to your reply as I've no idea what's wrong with the relationships.

  7. #7
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by machix
    Hi,

    I'm not sure if we are referring to the same thing so I've attached a small db for you guys to play around with and hopefully, understand my problem better.

    1. Open FrmOrderInfoMain
    2. Choose an outlet from the Brand combobox.
    3. Click on the subform. At this point, you will get an error message.
    You need to modify the Joins for the three joined tables in your main form data source query 'QueryOrderInfoMain' so it shows 3: ALL Records from tblOrderInfoMain and only those records from 'anylinkedtable' where the Join fields are equal.

    The easiest way to do this is to open the query in the design view, and double click on the Join Line. Then choose the #3 option per above. You SQL view result should look like this (showing Right Joins to the related tables.)

    Code:
    FROM tblSuppliesMaster RIGHT JOIN (tblExchangeRate RIGHT JOIN (tblDeliveryAddress RIGHT JOIN tblOrderInfoMain
     ON tblDeliveryAddress.DeliveryAddrCode = tblOrderInfoMain.DeliveryAddrCode)
     ON tblExchangeRate.CurrencyCode = tblOrderInfoMain.CurrencyCode)
     ON tblSuppliesMaster.SupplierCode = tblOrderInfoMain.SupplierCode;
    ~

    Bill

  8. #8
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111

    Smile

    Have a look at the attachment, I have made some changes so that the Parent form works with the subform.

    1) I have created a field POIDFK (number) in you subform and linked it to the POID in the parent form, have a look at your relationships now.

    2) I also changed data entry from Yes to No so that you can see the two records that I have created.

    Hope this helps.


    Regards
    Attached Files Attached Files
    Regards,



    John A

  9. #9
    Join Date
    Aug 2002
    Location
    Melbourne, Australia
    Posts
    111
    Swee,

    After I posted my reply I had another look at your question and I have misread your problem, It is a bit late here , so off the bed. Sorry for my error, just disregard my previous post.

    Regards,
    Regards,



    John A

  10. #10
    Join Date
    Aug 2004
    Posts
    36
    Thanks for the replies and time taken to look at my problem. Managed to get it solved. Thanks so much.

Posting Permissions

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