Here is my code, to delete a record from a subform from the main form, but it is not working. The error is user defined type not defined.
Private Sub cmdinsertlist_Click()

Dim Rs As DAO.Recordset
Set Rs = CurrentDb.OpenRecordset("Select * From tblrefLink Where refID = " & Me.ID & " And UserID = '" & Forms.frmqcpList2.txtQcp & "'")

If Not Rs.EOR Or Not Rs.BOF Then
    Do Until Rs.EOF
       Rs.Delete
       Rs.Update
       Rs.MoveNext
   Loop
   Rs.Close
End If

Set Rs = Nothing


End Sub

lucasfatoye

It is generally not a good idea to take values into SQL strings at runtime. Try this instead (make sure UserID is text, not an integer.

Dim Rs As DAO.Recordset
Dim sSQL As String
Dim lRefID As Long
Dim lUserID As Long

lRefID = Me.ID
lUserID = Forms.frmqcpList2.txtQcp

sSQL = ("Select * From tblrefLink Where refID = " & lRefID & " And UserID = '" & lUserID & "'"

Set Rs = CurrentDb.OpenRecordset(sSQL)

If the problem is in the reference to a subform for lUserID, then you might need to do something like:

lUserID = [Forms]![frmMainFormName]![sfrmSubFormName].[txtTextControlName]

since the subform is an object or control on the main form. Main form / sub form Bang Dot notation is pretty kludgey in Access. I always have problems with this issue.

mmcdonal

Look it up at: http://wrox.books24x7.com

Sorry, this:

sSQL = ("Select * From tblrefLink Where refID = " & lRefID & " And UserID = '" & lUserID & "'"

should be:

sSQL = "Select * From tblrefLink Where refID = " & lRefID & " And UserID = '" & lUserID & "'"


mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks, I think that works

lucasfatoye