Insufficient key column information for updating or refreshing

[toon10]

Hi

I have a VB 6 form which looks at data in an Access database. It has the standard “add”, “update”, “delete”, “refresh” buttons for handling the data. This used to work fine when I was just looking at one table. Now I’m looking at two.

My problem is this.

I have two tables, product and part. My code to open the recordset is:

“adoPrimaryRS.Open "SELECT Products.Description AS ProdDesc, Parts.Description, Parts.PartID, Parts.ProdID AS PiD From Parts, Products WHERE Parts.ProdID = Products.ProdID AND (Parts.PartID = '" & Me.txtID.Text & "') ORDER BY Parts.PartID", db, adOpenStatic, adLockBatchOptimistic”

I have put in place a combo box which appears which lists the product descriptions from the product table so when I edit my recordset, I can choose a description from the list and it updates the Parts table with the corresponding ProdID. This works fine when I update or add a new record but for some reason, the delete button (which works over one table) generates this error message:

“-2147467259 Insufficient key column information for updating or refreshing.”

Here’s my delete button code:

Private Sub cmdUpdate_Click()
On Error GoTo UpdateErr

msg = "Save details?"
Style = vbQuestion + vbYesNo + vbDefaultButton2
Title = "Save"
Response = MsgBox(msg, Style, Title)
If Response = vbYes Then
adoPrimaryRS.Fields(3).Value = txtFields(2).Text 'Used to force update of combo box
adoPrimaryRS.UpdateBatch adAffectAll 'update database with all changes made
adoPrimaryRS.Requery
If mbAddNewFlag Then
adoPrimaryRS.MoveLast
End If
RefreshCombo 'reset the combo box to reflect the changes
Else
cmdCancel_Click
Exit Sub
End If

cboProducts.Visible = False
txtFields(4).Visible = True

mbEditFlag = False
mbAddNewFlag = False
SetButtons True
mbDataChanged = False

Exit Sub

UpdateErr:
If Err.Number = -2147217842 Or Err.Number = -2147217887 Then 'wrong data type.
msg = "The data entered is of the wrong type."
Style = vbCritical
Title = "Save operation cancelled!"
Response = MsgBox(msg, Style, Title)
cmdCancel_Click
Exit Sub
End If
If Err.Number = 0 Or Err.Number = 20 Then
Resume Next
Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical 'return error code and description
Exit Sub
End If
End Sub

Does anyone know why this would appear on the delete button when I use the same adoPrimaryRS recordset to add and amend and that works fine? Any help would be gratefully received.
Toon.

 

[pkailas]

Try replacing:

(Parts.PartID = '" & Me.txtID.Text & "')

with

(Parts.PartID = " & Me.txtID.Text)

I believe your PartID is an integer data type... No quotes used.

 

[toon10]

Thanks for replying. The DataID is refers to a part number of alphanumeric characters e.g. XXX3412, XXX5547, etc.

 

[pkailas]

What code do you use for your Delete query?

 

[toon10]

I've managed to get around the problem but it's a bit messy.

I had set my PrimaryRS when the form opens and this is the recordset I've used for all my transactions. However, as the delete didn't work, I've had to create a new recordset specifically for the delete.

Here's what I've done.

Private Sub cmdDelete_Click()
On Error GoTo DeleteErr
msg = "Are you sure you wish to delete this part?"
Style = vbQuestion + vbYesNo + vbDefaultButton2
Title = "Delete"
Response = MsgBox(msg, Style, Title)
If Response = vbYes Then
Me.DataID.Text = ""

Dim ParamInputParts As DataEnvironment1
Set ParamInputParts = New DataEnvironment1

Set db = New Connection
db.CursorLocation = adUseClient
db.Open DataBaseCON

ParamInputParts.Commands("cmdPartDelete").Parameters(0).Value = Me.txtID.Text
Set rs = ParamInputParts.Commands("cmdPartDelete").Execute 'set recordset to command recordset execution

Form_Load 'launch the form load routine again to reset the fields and recordset to exclude deleted records
msg = "Record deleted."
Style = vbInformation
Title = "Delete"
Response = MsgBox(msg, Style, Title)
RefreshCombo 'reset the combo box to reflect the changes
End If
Exit Sub

DeleteErr:
If Err.Number = 0 Or Err.Number = 20 Or Err.Number = -2147217887 Then ' Or Err.Number = -2147467259 Then
Resume Next
Else
MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical 'return error code and description
Exit Sub
End If
End Sub

 

[pkailas]

I had a feeling you'd have to do that. I didn't see the delete query in your other code..