Record is deleted?

[Ben]

Hi all,

Below is a portion of my code in a sub:


qdf.delete "myQry"
strSQL = "...."
set qdf = db.CreateQueryDef("myQry",strSQL)
DoCmd.OpenQuery "myQry", acViewNormal, acReadOnly


I encountered the following error message when I was trying to click a
button on the form, the form provides a dropdown to allow for a user
selection, based on the selection, I delete an existing query and create
a new one based on the user condition. All I do is change the condition
in the WHERE clause and save it with the previously deleted query's name:

error message : "Record is deleted"


But then I clicked debug and when I step through it, I was able to run
to completion. Please share with me your thoughts about this error message.

Thanks,

Ben

 

[Kardan via AccessMonster.com]

Hi Ben

As a guess, the error may relate to a control or selection on your form
rather than the query.

How is the user selecting the deletion criteria? If it is to navigate to a
record, there is your problem.

I do this sort of thing all the time, but I never create a query to perform
an action, this only increases the size of the database unnecessarily. Use a
Combo box on the form with no record source, set the combo box record source
to the criteria field the user is to select and then try the following in the
form module for a button click event;

'START OF CODE

strSQL = "DELETE * FROM MyTable WHERE MyField = "
'From the following two lines select the option based whether the selection
criteria is text or not
strSQL = strSQL & Me.MyComboBox 'Non text field option
strSQL = strSQL & Chr(34) & Me.MyComboBox & Chr(34) 'Text field option

DoCmd.RunSQL strSQL

Me.MyComboBox.Requery

'END OF CODE


And remember, the form MUST NOT be displaying a record you are trying to
delete.

Hope this helps,

Richard