Delete sql statement in a subform (1 Viewer)

[captainlove]

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

 

[boblarson]

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 & "'"

 

[captainlove]

Deleting a a control field in a subform

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

 

[DCrake]

Simple Software Solutions

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:
http://www.icraftlimited.co.uk

 

[captainlove]

Hello Code master,

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

 

[captainlove]

hello code master

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

 

[boblarson]

hello code master

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

On what line?

 

[captainlove]

Dim rs as recordset

 

[boblarson]

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.).

 

[captainlove]

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

 

[boblarson]

first - where is it highlighting for that error

 

[captainlove]

Delete a text box in a subform(can anyone help please)

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

 

[captainlove]

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

 

[boblarson]

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

 

[captainlove]

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

 

[pbaldy]

Try putting a space in front of the word And.

 

[boblarson]

And don't post the same question multiple times.

 

[boblarson]

Time to post your db

 

[pbaldy]

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.

 

[captainlove]

Deleting a a control field in a subform

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