If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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?

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.

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,

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.

Open FrmOrderInfoMain

Choose an outlet from the Brand combobox.

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;