How do I delete a record based on a user enetered string value

  • Thread starter Thread starter SA
  • Start date Start date
S

SA

Hi,

I would like to delete a record based on a user entered string. Here are the
details. I have a txtFind textbox. A string is entered by the user. Based on
that string value it displays that one record in the fields in the
frmDeleteComponent form. There is also a lstDelete listBox which displays all
the record. But for some reason it does not delete that particular record as
requested by the user. I have written the sequence I am following and I am
hoping that someon is able to answer my question. I need help.

Thanks,
SA
'================================
My code in frmDeleteComponent:
'================================
Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

Dim stDocName As String

Dim Msg, Style, Title, response
Msg = "Do you wish to Delete This Component?"
Style = vbYesNo + vbExclamation
Title = "Delete Component Confiramtion"
response = MsgBox(Msg, Style, Title)

If response = vbYes Then
stDocName = "macroDelete"
DoCmd.RunMacro stDocName
End If

Exit_cmdDelete_Click:
Exit Sub

Err_cmdDelete_Click:
MsgBox Err.Description
Resume Exit_cmdDelete_Click

End Sub

'**************************************

Private Sub cmdFind_Click()
On Error GoTo Err_cmdFind_Click

DoCmd.GoToControl txtPartNumber.Name
DoCmd.FindRecord Me.txtFind.Value, acEntire, False, acSearchAll, True,
acCurrent, True

Exit_cmdFind_Click:
Exit Sub

Err_cmdFind_Click:
MsgBox Err.Description
Resume Exit_cmdFind_Click

End Sub


'====================================
Details of macroDelete:

1 SetWarnings
2 OpenQuery
3 RunMacro
4 Close
5 OpenForm

1 SetWarning: is set to No
2 OpenQuery: calls queryDelete ; View: datasheet; dataMode: Edit
Here's the sql statement:
DELETE tblParts.*, "PartNumber" AS Expr1
FROM tblParts
WHERE ((("PartNumber")="txtFind"));


3 runMacro: runs another macro called MacroDeleteDisplay with repeatCount
set to
Action: GoToControl: lstDelete
RunCommand: Refresh
4 Close: Actually closes the frmDeleteComponent (I have included the code
from that form above)
5 OpenForm: frmDeleteComponent; View:Form; Window Mode: Dialog
 
SA,

I am not sure if this is the only problem, but the Query is not right,
somehow. Assuming PartNumber is the name of one of the fields in the
tblParts table, I expect it should be more like this:

DELETE tblParts.*
FROM tblParts
WHERE [PartNumber]=[Forms]![frmDeleteComponent]![txtFind]

(Also assumes that I have understood correctly that the txtFind textbox
is on the frmDeleteComponent form?)
 
Back
Top