Cannot delete records in subform

[Tiger955]

Frontend Access 2010, backend SQL Server 2008:
I have a Problem which I cannot solve at all.

on a form there are 3 subforms which get their records through 3 passthrough queries from the Server.

All 3 subforms have the same Settings like AllowDeletions, allow Edits,Lockings, etc. All are Snapshots. All tables have PK's.
From all connected tables I can delete (add, edit) records directly in the tables.

On one of the 3 Subforms I can delete a selected record with this code

 Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
Dim strsql As String
 
 If KeyCode = 46 Then  'KeyCode = 46 ..DELETE Key
    If MsgBox("Delete?", vbYesNo + vbQuestion, "Security question") = vbYes Then
    strsql = "Delete from dbo.tblAusbildungPersonal where MaID= " & Me.Parent.MaID & " and AusbildungsID= " & Me.AusbildungsID
    
    Call SQL_PassThrough(strsql)
    End If
 End If
 Me.Requery
 
 End Sub

On the other 2 subforms the code does not even start when pressing the DELETE Key, the Status bar say "Cannot delete record".

when I use the code on the Main form refering to the fields in the subform it works also for the 2 subforms.

Thanks your help.
Michael

 

[arnelgp]

use dynaset recordset.

 

[sneuberg]

If it works in the main form then my guess is the SQL is somehow different in the subform, Maybe the references are off. I suggest adding some Debug.Print statements in, for example before Call SQL_PassThrough(strsql) put in

Debug.Print strsql

and see what shows up in the immediate window when you run the code. Maybe you'll see what's wrong.

 

[jdraw]

As arnelgp said -- use dynaset

[A snapshot provides a static image of the data that is not updateable.

from M$oft:

Snapshot --- No tables or the controls bound to their fields can be edited.

 

[Tiger955]

Thanks your advices.

The strange Thing is, that is works on one subform but not on the other two.

I do not use dynaset because I want to execute it on the Server directly, not on the connected tables.

And as I said, I cannot even run the code, putting a Debug.print straight after the start, like

 Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
Dim strsql As String
  
 Debug.print "test"
 ...

Meanwhile I solved it in a way which works.

Next to each subform I put a button with the same Code but starting

 Private Mybutton_OnClick
 DIM strsql as string
  
  If MsgBox("Delete?", vbYesNo + vbQuestion, "Security question") = vbYes Then
    strsql = "Delete from dbo.tblAusbildungPersonal where MaID= " & Me.MaID & " and AusbildungsID= " & Me.MySubform.Form!AusbildungsID
    
    Call SQL_PassThrough(strsql)
    End If
  Me.Requery
 
 End Sub

..and it works.

I have no idea, why the Form_KeyDown works only on one Subform...

Thanks your replies.
Michael