I have a mainform with a combo box to select quality control procedures i.e 10000 when I do select with the combo box the subform is populated with references and responsibilites.

I have a text box to insert new references . I am trying to create a command button to delete references I want from the subform, but cannot seem to get it to work. Here is the code I have
working
Dim sql As String

sql = "INSERT INTO tblreferences(references,qcpID) VALUES ('" & Me.txtreferences & "','" & _
Me.txtQcp & "' )"
DoCmd.RunSQL sql

---notworking-------------------------------------------------------------

Private Sub cmdDeletelist2_Click()

Dim sql As String


sql = "Delete from tblreferences where refID = " & Me.ID & _
" AND UserId = " & Forms.frmqcpList2.txtQcp
DoCmd.RunSQL sql


DoCmd.RunSQL sql
Me.Requery



End Sub

Perhaps:

sql = "Delete * from tblreferences where refID = " & Me.ID & _
" AND UserId = " & Forms.frmqcpList2.txtQcp

Or if UserID is text then:

sql = "Delete * from tblreferences where refID = " & Me.ID & _
" AND UserId = '" & Forms.frmqcpList2.txtQcp & "'"

Thanks Bob,

I am still getting an error message in the sql statement in my vba. This is how my VBA reads

Dim sql As String
sql1 = " find the references for Me.ID"
rs.Open sql
While rs.EOF = False
(sql = "Delete * from tblreflink where refID = " & Me.ID & _
" AND UserId = " & Forms.frmqcpList2.txtQcp)

where refId = rs.Fields("refId").Value
rs.MoveNext
end while
rs.Close
rs = Nothing


End sub

Looking at your VBA you do not indicate as to whether the criterion are strings or numbers. Strings need enclosing with ' (single apostrophes).

rs.Open sql
While rs.EOF = False
(sql = "Delete * from tblreflink where refID = " & Me.ID & _
" AND UserId = " & Forms.frmqcpList2.txtQcp)

where refId = rs.Fields("refId").Value
rs.MoveNext
end while
rs.Close
rs = Nothing


Your code also looks disjointed and irregular. Try this:

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

CodeMaster::cool:
http://www.icraftlimited.co.uk

Hello Code master,

Thanks for the code, it is giving an error message saying user defined type, not defined.
Am a bit lost

hello code master

I am a getting an error message, it is saying user defined type not defined

hello code master

I am a getting an error message, it is saying user defined type not defined
On what line?

Dim rs as recordset

It should be

Dim rs As DAO.Recordset

but you also need to have Microsoft DAO 3.x checked in the references (Tools > References) in the VBA window, where the x in 3.x means the number highest in your list (3.6, or 3.51, etc.).

Hello Bob,

Thanks for the tip.
Now it is saying method or data member not found. Can I share the database with you

Captain Love

first - where is it highlighting for that error

I have a main form that has a combo box for quality control categories. When I select a category i.e 10000

My subform get populated with references and responsibilites for this category.

I have a delete button on the subform, next to each of the references or responsibilites. when I click the button, the code attempt to delete all references/ responsibilities for that qcp, i.e 10000 instead of just deleting one reference/responsibilites, which the button is next to


Here is the code

Private Sub CmdDelete_Click()
Dim Sql As String

If Me.Check26.OldValue = True Then

Sql = "Delete from tblreferences where refid=" & Me.ID & _
"And qcpId = " & Me.qcpID
DoCmd.RunSQL Sql
End If

Me.Requery
End Sub

I have a main form that has a combo box for quality control categories. When I select a category i.e 10000

My subform get populated with references and responsibilites for this category.

I have a delete button on the subform, next to each of the references or responsibilites. when I click the button, the code attempt to delete all references/ responsibilities for that qcp, i.e 10000 instead of just deleting one reference/responsibilites, which the button is next to


Here is the code

Private Sub CmdDelete_Click()
Dim Sql As String

If Me.Check26.OldValue = True Then

Sql = "Delete from tblreferences where refid=" & Me.ID & _
"And qcpId = " & Me.qcpID
DoCmd.RunSQL Sql
End If

Me.Requery
End Sub

If you don't have a space before the AND then what this:

Sql = "Delete from tblreferences where refid=" & Me.ID & _
"And qcpId = " & Me.qcpID

comes out looking like is

Sql = "Delete from tblreferences where refid=" & Me.IDAnd qcpID = " & Me.qcpID

You need to add a space before the And like:

" And qcpID = " Me.qcpID

I still have the same result bob, tries to delete all the references for the particular qcp

Try putting a space in front of the word And.

And don't post the same question multiple times.

Time to post your db

Oh, and I can't remember if Access requires

DELETE * FROM...

or if

DELETE FROM

is enough, as it is in SQL Server. If you still have trouble, add the asterisk.

Hello bob,

Here is the database, the form is frmqcplist2

The steps are

1) select a qcp document from the combo box i.e 12010
2) click display
3) subform populated with references and responsibilities
4) sub form has command9 button. click on command9 for references for a check box that is ticked
4) pop up window appear asking for parameter value for ref id.
5) enter one of the values in the subform i.e 17
6) it then give a message say it want to delete x number of rows, which are all the rows of references for that particular qcp
7) click no cause if you click yes (all the data is lost)

What I want to achieve is to be able to delete one reference at a time.

Thanks bob

Okay, your main problem is that you didn't have the underscore within the ref_ID field name. Changed the code to this:


Dim Sql As String

If Me.Check26.OldValue = True Then

Sql = "Delete from tblreferences where refid=" & Me.ID & "And qcpid=" & Me.txtreferences

DoCmd.RunSQL Sql
End If

Exit Sub


One problem I noticed is that after the deletion the form won't requery because your form is based off a temp table but the load of it is based on the permanent table. Somehow you need to run the code for the button on the main form.

Also, you are using DoCmd.SetWarnings and, for one - it is DoCmd.SetWarnings FALSE (not OFF) and then if you do use those you need to put DoCmd.SetWarnings TRUE as the first line in your Error Handler as well so that you don't end up with no warnings at all if the code errors out and you don't get to the reset code.