Trying to delete a record with code

[Jon123]

Why does this code not delete the current record?

DoCmd.RunSQL "DELETE * FROM [Tble-Readings]WHERE
[Tble-Readings].[ID]= " & Me![ID] & ";"

I have this code on the a Close Button and if the record is incomplete I want it to be deleted from the table. The ID field is an autonumber so it is the 1st field to get a value if a user starts entering data. I get a msg box saying 0 fileds will be deleted why? the id's do match but it does not delete??

jon

 

[gromit]

Hi jon -

My guess is that the button works on a previously entered record, but not on a new one.

If so, then what you are seeing is the fact that Access doesn't actually write a new record until you move to another record, or close the form.

You could do something like:
DoCmd.Close acForm, Me.Name, acSaveNo

Of course that will close the form, which you may not want to do. There is probably also someway to return to the previous record, but I would have to look into it.

hth,

- g

 

[shadow9449]

Jon:

If your goal is to eliminate the record if it's incomplete when entered, have you considered using the "undo" method instead?

SHADOW

 

[gromit]

Thanks, Shadow. That's the other thing I was trying to remember.

 

[Pat Hartman]

If you put your edit code in the correct event, you won't need to delete the record because you would have prevented Access from saving it in the first place.

Move your edit code to the FORM's BeforeUpdate event and cancel the event if you have an error.

If some condition Then
    Cancel = Trun
    MsgBox "some error message", vbOKOnly
    Me.somefield.SetFocus
End If

Your code isn't working because there is no longer a Current record at the time the Close event runs. The Unload event is probably the last event where you could reference the current record. In any event, put your code in the correct event to avoid the problem entirely.

gromit,
By the time the form's Close event runs, any dirty record has already been saved.

 

[shadow9449]

You're welcome, Gromit

SHADOW