Delete query not deleting the record selected

[WAVP375]

The following code is being used to delete a record. When the delete query is executed from the navigation pane it works properly, but when the delete query is executed programmatically it deletes the first record in the table.

DoCmd.SetWarnings False
DoCmd.OpenQuery "QdelRequirementDelete"


DoCmd.SetWarnings True
DoCmd.Close
DoCmd.OpenForm "Volunteer Menu"

and the query

Field: Job Nbr Course Nbr
Table: Requirement Table Requirement Table
Delete: Where Where
Criteria:[Forms]![FrmRequirementDelete]![Job Nbr] [Forms]![FrmRequirementDelete]![Course Nbr]


Any help appreciated

[Bulzie]

Post a screenshot of Query builder or SQL code of delete statement?

Maybe comment out the SetWarnings False to see if it gives you any messages.

[ranman256]

did you try running the query manually, and get an error message?
some cannot delete due to the table relationships.

[WAVP375]

Private Sub BtnAcceptAnother_Click()

'DoCmd.SetWarnings False
DoCmd.OpenQuery "QdelRequirementDelete"
'DoCmd.SetWarnings True
DoCmd.Close
MsgBox "Requirement has been deleted"
DoCmd.OpenForm "FrmRequirementDelete"

End Sub


No messages and no change.

[WAVP375]

I have also tried the DoCmd.RunCommand acCmdDeleteRecord method and I get the same result.

[WAVP375]

The table I am deleting from has only two fields and both are designated as primary key. Couls this have anything to do with the problem?

[Bulzie]

Go into design of query QdelRequirementDelete, select SQL View and past the code. Maybe someone can spot something. Are you sure it is a Delete query and not a Select query? Did you say you can run it manually and it works? Other thing, are you sure there are records that meet the criteria to be deleted? Maybe it works but no records to delete?

[WAVP375]

The delete works properly when executed from the navigation pane. I choose the record to delete from a list box populated by the table from which I am attempting to delete the record. I have captured the SQL but cant paste it into my reply here in the forum.

[Bulzie]

Odd your query will work if you run it manually but will not work in your code. Are you sure the form is open and those 2 fields have values when you run the code? Put a break point at the beginning of the code and set some variables to look at those 2 values on the form(JobNbr and CourseNbr) to make sure they are accurate values.

Or instead of query maybe try:

Docmd.RunSQL "Delete * from [Requirement Table Requirement Table] WHERE [JobNbr] = ' & [Forms]![FrmRequirementDelete]![Job Nbr] & 'AND [CourseNbr] = ' & [Forms]![FrmRequirementDelete]![Course Nbr] & ')"

Syntax on that will need work but you get the idea.

[WAVP375]

I inserted the MsgBox with field values and the proper values were there.
I took out the close after the delete query and it worked properly. I don't understand what the DoCmd.Close has to do with the delete query.