Deleting a field (Priamry key field) within a form 2

[nim180]

Hi everyone,

I was hoping someone could help me with the following.

I have a form with a combo box, the user chooses from the combo box and clicks the search button and it populates a number of text fields with the information regarding the item in the combo box.

I also have a delete button which will allow the user to delete the record if required. The unique field is the primary key which is an autonumber therefore i would like to delete using this field. Below is the code i use but i seem to get an error saying "Data Type Mismatch". Can someone see where i am may be going wrong.

Dim db As DAO.Database, SQL As String
Dim strInput As String
Dim strMsg As String

    strMsg = "Please Enter Password to Delete Record?" & vbCrLf & vbLf & _
             "Please key the programmer's password."
    strInput = InputBox(Prompt:=strMsg, Title:="Delete Record")
    If strInput = "test" Then
       
      If MsgBox("Do You Really want to delete key  assigned to " & Forms!frmkeys.[MainFilter] & "?", vbQuestion + vbYesNo) = vbYes Then

         Set db = CurrentDb
         SQL = "DELETE ID " & _
         "FROM tblkeys " & _
         "WHERE ([ID]) = '" & Me!Text53 & "';"
         db.Execute SQL, dbFailOnError
   
Set db = Nothing
 
         DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

         MsgBox ("Record Has Been Deleted")
         
         DoCmd.Close
      End If
    
    Else
     
         MsgBox ("Password Incorrect Please Talk to Administrator")
        
Exit Sub

     End If

Thanks
Nim

 

[PHV]

Replace this:
"WHERE ([ID]) = '" & Me!Text53 & "';"
with this:
"WHERE ([ID]) = " & Me!Text53 & ";"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nim180]

Thank you PHV i knew it was something small. I just couldnt see it, have a star

regards,

nim

 

[TheAceMan1]

How are ya nim180 . . .

Your also unnecessarily deleting twice with

[blue]DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70[/blue]

Also ... since you require a password, the msgbox is unecessary. You just need the input box!

Cleaning up your code, incorporating [blue]PHVs[/blue] suggestion (which takes care of the mismatch error), and presenting an idea of more readable code ... try this:

[blue]   Dim db As DAO.Database, SQL As String
   Dim Msg As String, Style As Integer, Title As String, DL As String
   

   DL = vbNewLine & vbNewLine [green]'double line spacing![/green]
   
   Msg = "Please Enter Password to Delete Record!" & DL & _
         "Please enter the programmer's password!" & DL & _
         "Click 'Cancel' to abort deletion! . . ."
   Title = "User Input required! . . ."
   
   If InputBox(Msg, Title) & "" = "test" Then
      Set db = CurrentDb
      
      SQL = "DELETE ID " & _
            "FROM tblkeys " & _
            "WHERE ([ID]) = " & Me!Text53 & ";"
      db.Execute SQL, dbFailOnError
      
      Msg = "Record Has Been Deleted!"
      Style = vbExclamation + vbOKOnly
      Title = "Deletion Accomplished! . . ."
      MsgBox Msg, Style, Title
      
      Set db = Nothing
   Else
      Msg = "Password Incorrect!" & DL & _
            "Please Talk to Administrator! . . ."
      Style = vbCritical + vbOKOnly
      Title = "What Were You Thinking! . . ."
      MsgBox Msg, Style, Title
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[nim180]

Your input is always welcome Aceman and your code worked like a charm. The code is still in development stage and a bit messy but i will fix it up Have a star

nim