Delete a record based on a value entered by the user and display therest of the records

[agarwasa2008]

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:

Action:
1 SetWarnings
2 OpenQuery
3 RunMacro
4 Close
5 OpenForm

Details
----------
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

[Rich P]

Try something like this in a command button:

Private Sub Command1_Click()
DoCmd.SetWarnings False
If MsgBox("Do you want to delete this record?",vbYesNo, "Delete Record
Yes/No") = vbYes Then
DoCmd.RunSql "Delete * From tblPars Where PartNumber = '" & txtFind &
"'"
End If
DoCmd.SetWarnings True
End If

Note: to make the code more readable - you may want to rename txtFind as
txtPartNumber. Also, when using parameters in a query as above - you
have to delimit the parameter with single quotes ' ' if it is a string.
In Jet sql -- data parameters are delimted with the # symbol. Number do
not require delimiting. I also temporarily disabled the default
warnings message and then re-enable it at the end of the procedure.
Otherwise you will get the default message everytime you delete
something:

1 Record is about to be deleted. Continue Yes/No?

You already have a custom message with the If MsgBox(...) statement.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Rich P]

Correction: Date Parameters #3/25/2007# require the # symbol as the
delimeter in Jet Sql. Most sql languages use the single quotes ' ' for
delimiting dates the same as strings. That I know, only Jet sql (Access
Jet sql) uses the # symbol for delimiting dates.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[agarwasa2008]

On May 19, 12:17*pm, Rich P <[e-mail]:

Try something like this in a command button:

Private Sub Command1_Click()
DoCmd.SetWarnings False
If MsgBox("Do you want to delete this record?",vbYesNo, "Delete Record
Yes/No") = vbYes Then
* DoCmd.RunSql "Delete * From tblPars Where PartNumber = '" & txtFind &
"'"
End If
DoCmd.SetWarnings True
End If

Note: to make the code more readable - you may want to rename txtFind as
txtPartNumber. *Also, when using parameters in a query as above - you
have to delimit the parameter with single quotes ' ' if it is a string.
In Jet sql -- data parameters are delimted with the # symbol. *Number do
not require delimiting. *I also temporarily disabled the default
warnings message and then re-enable it at the end of the procedure.
Otherwise you will get the default message everytime you delete
something:

1 Record is about to be deleted. *Continue Yes/No?

You already have a custom message with the If MsgBox(...) statement.

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Well, I had one more question ---
how would I show all the records minus the deleted record in the
lstBox. I would like to refresh the lstBox with the right information.
Currently it is showing all the records.

Also, I would like to know what is wrong with the way I have set the
macro originally.

Thanks,
Sangeeta.

[Rich P]

For general purpose operations - the built in macros are fairly ideal
because they don't require any maintenance. But if you need to
customize some operation - the macro is not the most ideal way to go
because they are not easy to customize. Thus, you would use VBA to
write custom procedures/functions.

As for "refreshing" your listbox with the current rows in your table -
just add this to the sample procedure:

Private Sub Command1_Click()
DoCmd.SetWarnings False
If MsgBox("Do you want to delete this record?",vbYesNo, "Delete Record
Yes/No") = vbYes Then
DoCmd.RunSql "Delete * From tblPars Where PartNumber = '" & txtFind &
"'"
End If
listbox1.Rowsource = "Select fldx From tblParts"
listbox1.Requery
Me.Requery
DoCmd.SetWarnings True

End If

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[karsagarwal]

On May 19, 2:15*pm, Rich P <[e-mail]:

For general purpose operations - the built in macros are fairly ideal
because they don't require any maintenance. *But if you need to
customize some operation - the macro is not the most ideal way to go
because they are not easy to customize. *Thus, you would use VBA to
write custom procedures/functions. *

As for "refreshing" your listbox with the current rows in your table -
just add this to the sample procedure:

Private Sub Command1_Click()
*DoCmd.SetWarnings False
*If MsgBox("Do you want to delete this record?",vbYesNo, "Delete Record
*Yes/No") = vbYes Then
* *DoCmd.RunSql "Delete * From tblPars Where PartNumber = '" & txtFind &
*"'"
*End If
*listbox1.Rowsource = "Select fldx From tblParts"
*listbox1.Requery
*Me.Requery
*DoCmd.SetWarnings True

End If

Rich

*** Sent via Developersdexhttp://www.developersdex.com***

Thank you for all your help. It works like magic.

SA