Deleting current record

bobby6055
bobby6055 used Ask the Experts™
on
I have tried several onclick delete code. Now I want to try something new using some conditions as follows.

Perform delete action in the Edit or newRecord mode with the following defined conditions.

(a). If "IsNull or nothing" in text1(currentscreen), or text2(currentscreen)  or text3(currentscreen),
         then msgbox "Nothing to delete.

       Else....

(b). If "Not Is Null" text1(currentscreen), or text2(currentscreen)  or text3(currentscreen),
       Then msgbox "Are you sure you want to delete the current record #111 (for example)

      If User Response is "Yes" then perform the delete action for record id #111
      If User Response is "No" then do not perform the delete action for record id #111

 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Check this sample
DB27038488.mdb
Something along these lines should to the trick...
Dim strSQL
' This code assume you are using DAO to access recordsets

If  Insull(Text1) AND Isnull(Text2) AND Insull(TExt3) Then
   MsgBox "Nothing To Delete"
Else
	If MsgBox("Are you sure you want to delete record #" & Me.ID,vbYesNo) = VbYes then
	strSQL="DELETE * from [Your Table Name] Where [ID=" & Me.ID 
	' this is the record we will delete. But we need to move off the record
	' before deleting it
	With Me.RecordsetClone
		if Not .BOF then ' we are not at the begining of the recordest
			.MovePrevious ' se we can safely move back one
		Else ' we were on the first record
			.MoveNext
		End If
		DoCmd.SetWarnings False
		CurrentDb.Execute strSQL
		DoCmd.SetWarnings True
	End With
	Me.Requery
End If

Open in new window

Author

Commented:
LambertHeenan:
I tried your suggestion from an Onclick command button. When I clicked the button to delete, I recieved
a Microsoft Visual Basic dialog box with a Run-Time error  '3167': "Record Deleted"

How do I resolve the error?

Bobby

Author

Commented:
Debug highlighted ..... Me. Requery

so I changed it to .,..... Me.Refresh
....and the error disappeared.
Sorry 'bout that. I often mix up refresh and requery. :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial