Join key of '***' not in recordset

[ChrisLeicester]

Hi All.

I am having an issue with a subform on my purchase orders side. Funnily I use the same format on my sales side and that works, but on the purchases side I get the following,

Cannot add record (s), join key of prodordertb is not in recordset.

The main form data come from a query linking a purchase orders record and the supplier table. The purchase order table is in the error ProdOrderTB.

The subform is a continuous form based on another query, StockOrdersTB with links to the product table for the product name, size and also linked to weight unit table for weight description.

I have a vba that opens the main form in add record mode and then is meant to set the supplier and the date.

I don't understand the error as the.main form is not linked to any other form where it wants the primary key, and the subforms are linked by master child correctly

Also, between the main and sub, the master key field is on the form and the child key field is on the subform.

Any ideas

Thanks

 

[jdraw]

Cannot add record (s), join key of prodordertb is not in recordset.

Can you post the SQL for the recordset mentioned?

 

[ChrisLeicester]

Hi

from the sql link to the query, i did the original query in design view

SELECT StockOrderTB.StockCaseItemID, StockOrderTB.FromPurchaseOrder, StockOrderTB.ItemOrdered, [ProductName] & " " & [PackWeight] & [UnitName] AS ItemName, StockOrderTB.CaseQuantity, StockOrderTB.OrderedPrice, [CaseQuantity]*[OrderedPrice] AS LineCost, ProductsTB.SupplierSKU
FROM UnitsTB INNER JOIN (StockOrderTB INNER JOIN ProductsTB ON StockOrderTB.ItemOrdered = ProductsTB.ProductID) ON UnitsTB.UnitID = ProductsTB.[Weight Unit];

Strangley, if i open the form in normal view, it works perfectly, it just seems to be when trying to set a new record in the main form that this issue comes up

 

[jdraw]

I don't see a prodOrderTB in that sql. Is there a specific line of code that shows the error, or is there some other SQL being referenced?

 

[ChrisLeicester]

Private Sub SupConBTN_Click()

Dim Supplier As Integer  'The supplier chosen

    'set the variable
    Supplier = [Forms]![POsupplierPickFM]![SupChosen]   'gets supplier from select box
    
    
         DoCmd.OpenForm "PurchaseOrderFM", acNormal, , , acFormAdd
        [Forms]![PurchaseOrderFM]![SelectPOsupplier] = Supplier
        [Forms]![PurchaseOrderFM]![PurchaseOrderDate] = Date
        
        


End Sub

It is failing on thee forms!purchaseorderfm!selectposupplier = supplier line.

i think that field in the table is a look up, would that cause it,

thanks

 

[Gasman]

Funnily I use the same format on my sales side and that works

You would be suprised how many times we hear that.

I would expect that variable to be Long if it is meant to be the PK from the supplier table?

 

[jdraw]

Several errors/issues can be attributed to Lookup fields . Most will advise you to avoid them. See the link below.
Lookup wizard/fields

 

[ChrisLeicester]

I used to always use look up fields before just using normal number fields now.
If i need a combo box on a form I now add it in design view and then link to it.

This one slipped through the net, must have slipped back into that habit.

Will ammend it and try again

 

[The_Doc_Man]

Strangley, if i open the form in normal view, it works perfectly, it just seems to be when trying to set a new record in the main form that this issue comes up

I interpret this as that you have a SELECT based on a three-element INNER JOIN and your linkage allows you to find extant records easily. It is true that SELECT queries, if constructed correctly, CAN be used to add records. From your discussion, that is exactly what you wanted to do. But it gets more complicated when virtually performing an INSERT INTO through the SELECT.

When you try to add a record, the JOIN - that SELECTs three component records to form the JOINed record - must CREATE three records to the three tables participating in the triple JOIN. But this has to be done in proper order internally.

Understanding SQL Query Order of Execution | Built In

Take the time to understand how the clauses in SQL are evaluated. Here, we discuss the order of execution in SQL and explain why it matters.

builtin.com

What actually happens when you evaluate the query is that the FROM/JOIN ... ON clauses are evaluated first, At that stage, the error message says you cannot JOIN to one of the members because there is no key matching the defined JOIN, yet there WOULD be a non-trivial component in that record to be stored when the SELECT gets evaluated (after the WHERE and GROUP BY clauses, if any).

Here is a link to a list of reasons why working queries become READ-ONLY, in fact or in effect.

https://allenbrowne.com/ser-61.html

 

[ChrisLeicester]

Thank you Doc Man,
I have printed that for future reference.

I rebuilt new tables and rebuilt the form with the new tables and new queries and it now seems to work.
Thanks All