Delete Records from a Form

[puT3]

Split off from To search record.

No worries :)

Let us know how this works for you.

It works well..Another question is I want to delete records from a form when I try to delete from the form, the records do not delete in the table. Why do this happen? I use the access button wizards.

The form name is [Delete Record]. The control are

textbox: [txtISBN],
button: cmdDelete,

where user enter the ISBN/ISSN number and click delete, the records will be deleted.

[puT3]

I used this code

Dim strMsg As String
    Dim strSQL As String
 
    strMsg = "Are you sure you want to delete this record? You cannot undo a deletion."
 
    If MsgBox(strMsg, vbYesNo) = vbYes Then
        strSQL = "DELETE * FROM [Printing Media Library] WHERE [Printing Media Library].[ISBN/ISSN]= " & [Forms]![View/Delete Record]![txtISBN] & ";"
    CurrentDb.Execute strSQL, dbFailOnError
    DoCmd.GoToRecord , , acNewRec
Else
    DoCmd.OpenForm "Delete Record"
End If

and there is error saying "data type mismatch in criteria expression"

[puT3]

and I want to know how to disable the design,view,lay out view,so that user cannot change it....

[NeoPa]

and I want to know how to disable the design,view,lay out view,so that user cannot change it....

Are you talking about the Form property Allow Design Changes? This should be set to Design View Only.

[NeoPa]

I used this code

Dim strMsg As String
    Dim strSQL As String
 
    strMsg = "Are you sure you want to delete this record? You cannot undo a deletion."
 
    If MsgBox(strMsg, vbYesNo) = vbYes Then
        strSQL = "DELETE * FROM [Printing Media Library] WHERE [Printing Media Library].[ISBN/ISSN]= " & [Forms]![View/Delete Record]![txtISBN] & ";"
    CurrentDb.Execute strSQL, dbFailOnError
    DoCmd.GoToRecord , , acNewRec
Else
    DoCmd.OpenForm "Delete Record"
End If

and there is error saying "data type mismatch in criteria expression"

I expect [ISBN/ISSN] is a string/text field. In which case the format of your SQL is wrong. Try this for line #7 instead :

strSQL = "DELETE * " & _
         "FROM [Printing Media Library] " & _
         "WHERE [ISBN/ISSN]='" & [Forms]![View/Delete Record]![txtISBN] & "'"

[puT3]

I expect [ISBN/ISSN] is a string/text field. In which case the format of your SQL is wrong. Try this for line #7 instead :

strSQL = "DELETE * " & _
         "FROM [Printing Media Library] " & _
         "WHERE [ISBN/ISSN]='" & [Forms]![View/Delete Record]![txtISBN] & "'"

Thank you, it works now...thanks a lot

[NeoPa]

Excellent. That's two threads sorted in a week (or so) :D

Sounds like progress ;)

[puT3]

How can I make the report do not show any empty records? For example, in this [Company] table do not has address...I want the report only shows company that has address

[NeoPa]

OK. But do me a favour.

Post this question in it's own thread with all the required details (searching through the previous thread to remind myself of everything leaves me dooing all the work here. That's not the right balance).

Feel free to post a link in here to your new thread and I will certainly have a look and help. This should be a simple question, but without the facts to hand, it is far more complicated than it needs to be.