Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

MS Access To SQL Server

I split my 2007 Access DB to Frontend Backend with SQL Server 2005.

Now Im getting an error on a select list: Cannot add records primary key for table not in recordset.
0
webdork
Asked:
webdork
  • 13
  • 5
  • 4
  • +1
1 Solution
 
sammySeltzerCommented:
I think your question is not sufficient to determine what the issue is.

However, do you have a subform?
0
 
webdorkAuthor Commented:
yes subform
0
 
webdorkAuthor Commented:
Its an order form with a product subform. The subform has a select list linked to the product table.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
webdorkAuthor Commented:
Before splitting the DB to Front/Back end you could select a product from the subform select list and a line item with all product details would form and you could select another product line item. and so on.
0
 
webdorkAuthor Commented:
Subform select list:

Record Source: SELECT DISTINCT Products.ProductName, ProductId FROM Products;
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is ProductID the Primary Key of the Products table?

Remove the Distinct keyword and see what happens.

0
 
webdorkAuthor Commented:
Yes ProductID is the primary key. Im trying that now.
0
 
webdorkAuthor Commented:
Still error after removing DISTINCT keyword as recommended.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Can you send the code snippet that produces the error?
0
 
webdorkAuthor Commented:
Im not sure how to extract the code??
0
 
webdorkAuthor Commented:
here is screenshot
screenshot.png
0
 
sammySeltzerCommented:
You need to add productID to the subform select you just showed
0
 
sammySeltzerCommented:
Then use [ProductID] as the field in the LinkChildFields and LinkMasterFields
properties of the subform control (the control on main form that holds the
subform object). The subform then will track with the ProductIDvalue that you
have showing in the main form, and the ProductIDvalue in the subform will be
filled in by ACCESS with the value from the main form.

Try it
0
 
webdorkAuthor Commented:
the link field is OrderID. It needs to be to get the correct order details.  There is no ProductID reference in the main form
0
 
sammySeltzerCommented:
Ok, I thought you said productID is the primary key??
0
 
webdorkAuthor Commented:
Not sure we are approaching this the right way. It worked before I split the DB.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your screenshow shows the Recordsource of the combo to be this:

SELECT DISTINCT Products.ProductsID, Products.ProductName FROM Products;

Your BoundColumn is set to 2. Try setting the BoundColumn of the Combo to 1 (to store the value from the First column, which is ProductID).
0
 
webdorkAuthor Commented:
Still no remedy. i've tried all recommendations.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Let's back up.

Exactly what are you doing when you receive this message? Are you loading the form, or are you trying to select something from a Combo, etc etc?

I assume these are linked tables? If so, can you confirm that your tables all have Primary Keys defined on them, and that those PKs are properly represented in the Access links? To do that, open the link in Design view (Access will complain, but open it anyway) and insure that a PK has been defined on the table.

Are you storing the VALUE of ProductID in your parent table (the table/query your form is based on), or are you storing the ProductName? Your settings would store the ProductName, which is not proper design.

What code is in your combo's BeforeUpdate and AfterUpdate events?
0
 
webdorkAuthor Commented:
This DB model is based on an early MS Access Northwest DB.  Its been in use since 2002. Last week I split Access to use SQL Server 2005 as a Backend.  After the split, the ComboBox on the sub form stopped working.
Here are the details:

I've got an OrderForm and a OrderSubForm linked by OrderID

The OrderSubForm underlying query is OrderDetailsExtended linking the Products table with the OrderDetails table.

The Products table PK is ProductID.

The OrdersDetails table has an OrderDetailsID column that is the identity column and does not allow nulls. This column is not tagged as a PK. The OrdersDetails table has two columns tagged as PK. OrderID and ProductID, neither allow nulls.

In the OrderSubForm there is a ComboBox. When I invoke this Combobox, I see the list of ProductNames as I should, but when I try to select one I get an error:

"Cannot add record(s); Primary key for table 'Products' not in recordset"

This is the Control Source for the ComboBox:
ProductName

This is the RowSource for the ComboBox:
SELECT DISTINCT Products.ProductName FROM Products;


Here are the Before and After events:
Private Sub ProductID_BeforeUpdate(Cancel As Integer)
' If Orders Subform is opened as a standalone form, display a message
' and undo changes made to the ProductName text box. (The IsLoaded function
' finds a form in the Forms collection when it's opened as a standalone
' form, but not when it's opened as a subform on a main form.)

    'Dim strMsg As String, strTitle As String
   ' Dim intStyle As Integer
   
   ' If IsLoaded("Orders Subform") Then
      '  strMsg = "You can't add or edit a Product Name when you open Orders Subform as a standalone form."
        'intStyle = vbOKOnly
       ' strTitle = "Can't Add or Change Product Name"
       ' MsgBox strMsg, intStyle, strTitle
       ' Me!ProductID.Undo
       ' Me.Undo
    'End If

Private Sub Product_AfterUpdate()

End Sub

End Sub
0
 
webdorkAuthor Commented:
cant wait anymore
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Your UI design is flawed, regardless of whether it's been working in the past or not. The error you're reporting:

"Cannot add record(s); Primary key for table 'Products' not in recordset"

Is self-explanatory. Given these statements:

(a) "The Products table PK is ProductID. "
(b) This is the Control Source for the ComboBox: ProductName
(c) This is the RowSource for the ComboBox:SELECT DISTINCT Products.ProductName FROM Products;  

Your form design is incorrect. You should NEVER store anything other than the Primary Key value of a lookup table in the host table. In this case, you're storing the value of Products.ProductName, when you should be storing the value of Products.ProductID. As the error said, you are NOT including the Primary Key value of the Products table (i.e. ProductID) in the recordset. You MUST do this with SQL Server.

Access allows you to get away with things of this nature. SQL Server does not.

In other words: Change your UI design to work with accepted normalization methods and it will work. If not, you'll have nothing but trouble moving forward with SQL Server.




0
 
Kelvin SparksCommented:
Just a thought as I don't see it here, was the PK an autonumber in Access? Is som check on SQL Server that the PK (which will be an interger) has the identity seed set yto yes (is one of the properties of that field near the bottom of the list).

Kelvin
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 13
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now