can't get requery to work

[kronar30]

i changed my code to use SQL delete which works (when there are no child records), however the requery goes right to the error routine displays "Record is deleted". Which I know and want to display the query window with the newly updated recordset.

Here is my code:

Private Sub cmdDeleteRecords_Click()
On Error GoTo deleteRecordsError

Set db = CurrentDb()
Set JBrec = db.OpenRecordset("JB_Jobs", dbOpenDynaset)
Stop
db.Execute "delete * from JB_Jobs where JB_Deletesw = true and JB_JoborBid = false"

Requery
GoTo endofsub

deleteRecordsError:
MsgBox "deleteRecordsError: " & Err.Description & " - " & Err.Number
endofsub:
End Sub

 

[dhookom]

The Requery doesn't have anything like "Me." Also, why open the recordset?

This is more standard coding (set a breakpoint to stop the code):

Private Sub cmdDeleteRecords_Click()
    On Error GoTo deleteRecordsError
    
    Set db = CurrentDb()
    db.Execute "delete * from JB_Jobs where JB_Deletesw = true and JB_JoborBid = false", dbFailOnError
    
    Me.Requery
Exit Sub
    
deleteRecordsError:
    MsgBox "deleteRecordsError: " & Err.Description & " - " & Err.Number
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[kronar30]

I used your sugested code, however the requery apears to not work, however the record is not being deleted and the DoCmd.Close gets an error now (record is not actually deleted from table). The form does display a query of the table.)

Private Sub cmdDeleteRecords_Click()
On Error GoTo deleteRecordsError

Set db = CurrentDb()

db.Execute "delete * from JB_Jobs where JB_Deletesw = true and JB_JoborBid = false", dbFailOnError

Me.Requery
Exit Sub

deleteRecordsError:
MsgBox "deleteRecordsError: " & Err.Description & " - " & Err.Number
endofsub:
End Sub

Private Sub Form_Close()
'Stop
If cmdDeleteRecords.Visible Then
If MsgBox("Do you wish to DELETE selected records first??", vbYesNo) = 6 Then
Call cmdDeleteRecords_Click
End If
End If
DoCmd.Close

End Sub

 

[dhookom]

What happens if you paste the following into the SQL view of a new, blank query and attempt to run it?

delete * from JB_Jobs where JB_Deletesw = true and JB_JoborBid = false

I would not attempt to requery a form in its close event.

Duane
Hook'D on Access
MS Access MVP

 

[rustychef]

dhookom is right, you're not using the requery method correctly.

Your code should work, but when the delete is executed, issues a Me.Requery

Me = current form.

This works if your data is being displayed on a form i.e. Default View = Continuous Forms.

If you're displaying results in another control like a listbox then its NameOfListbox.Requery

If its on a subform, Im not sure I remember correctly, but its like Me!NameOfSubform.Form.Requery or Me!NameOfSubform.Requery you might have to look that one up.

 

[kronar30]

The form is a contunuous form
I can modify the records (ie. turn delete sw on and off)
however I can't delete the record.
Now the close causes an error 'method or data member not found'

Here is the close.form code now:

code:
Private Sub Form_Close()

Me.DoCmd.Close

End Sub

 

[TheAceMan1]

... and what happens if you stick in [purple]DoEvents[/purple]?

[blue]    db.Execute "DELETE * " & _
               "FROM JB_Jobs " & _
               "WHERE JB_Deletesw = true AND " & _
                     "JB_JoborBid = false;"
    [purple][b]DoEvents[/b][/purple]
    Me.Requery[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[rustychef]

Just a couple of thoughts addressing the inability to delete a record.

In the form's properties:
Is "Allow Deletions" set to YES
and
What kind of value is set for "Recordset Type"?
If your recordsource has a join in it, a DYNASET can allow you to edit some record/fields while restricting others.

From the help file on Recordset Type:
Dynaset (Default) You can edit bound controls based on a single table or tables with a one-to-one relationship. For controls bound to fields based on tables with a one-to-many relationship, you can't edit data from the join field on the "one" side of the relationship unless cascade update is enabled between the tables. For more information, see the topic that explains when you can update records from a query.

 

[kronar30]

I added the DoEvents
Default View of the form is Continuous Forms
Allow Deletions is Yes
Recordset Type is Dynaset

Code:
Private Sub cmdDeleteRecords_Click()
On Error GoTo deleteRecordsError

Set db = CurrentDb()
Stop
db.Execute "delete * from JB_Jobs where JB_Deletesw = true and JB_JoborBid = false", dbFailOnError
DoEvents
Me.Requery
Exit Sub

deleteRecordsError:
MsgBox "deleteRecordsError: " & Err.Description & " - " & Err.Number
endofsub:
End Sub

Private Sub Form_Close()
Me.DoCmd.Close

End Sub

I have a stop in the code before the Delete, when I single step through the code, at the end of the cmdDeleteRecords_Click() - no errors, however the form does not show the record gone (ie. deleted) and it is still in the table. When I try to close the form I get the error 'method or data member not found' and the line Me.DoCmd.Close is highlighted ( actually only the '.DoCmd' is highlighted.
This is the main table and there can be child records - however this record has no child records attached to it.

 

[missinglinq]

Everything else aside, your "close" code makes no sense!

To begin with, the syntax would be DoCmd.Close, not Me.DoCmd.Close.

But more importantly, the form is already closing by the time the Form_Close event fires, so why issue a command to close the form?

DoCmd.Close would be used elsewhere in the form to force its closing, and because of a long recognized bug in Access, should always be preceded with a line forcing a record save:

If Me.Dirty Then Me.Dirty = False 
DoCmd.Close

If you don't do this, and you have any fields defined as Required and they are left empty, Access will dump the record, without enforcing the Required property or running any other validation you may have, close the form, and it won't bother to tell you that it's done this.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[kronar30]

Thanks for the help. I am adding a close form button and wish to make sure that any records selected for delete have been deleted (by user clicking the delete button) prior to allow close form.

I now no longer get the on close error.
However the newly selected to delete record does not delete, unless it had the delete flag previously set. I want the user to be able to select multiple records to delete before clicking the delete button??

Do I need to somehow comit the delete flag to the database before running the delete SQL?

Once again , thanks for all the help. I find the help text for access to be very confusing and hard to find the right search key. Probably because I don't have any formal Access training. Just a few books: Step by step MS Access 97, and Access 97 Developers Handbook.

 

[rustychef]

Im assuming that JB_Deletesw is a field in your table that your users are using to select the records they want to update, then my question is,

Are you having issues with all the records not deleting, or just one from a group of selected records?

If the answer to the question is yes, then you will need to "commit" (save) your delete flag value to the database.

If a user selects one or several records, then the last one selected is not automatically saved to the table until another record is gets the focus. So without a save, the last selected record is not marked for deletion.

 

[kronar30]

That makes sense and seems to fit the symptems.
But what code do I use?
I assume it would be:
if Me.dirty then ????

as I do not in code open, update or close the tables records while they are displayed in the forms query?

Thanks for all the help, the help files are more than useless.