Results 1 to 10 of 10
  1. #1
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171

    Delete query not deleting the record selected

    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

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,511
    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.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,549
    did you try running the query manually, and get an error message?
    some cannot delete due to the table relationships.

  4. #4
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    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.

  5. #5
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    I have also tried the DoCmd.RunCommand acCmdDeleteRecord method and I get the same result.

  6. #6
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    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?

  7. #7
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,511
    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?

  8. #8
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    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.

  9. #9
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,511
    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.

  10. #10
    WAVP375 is offline Competent Performer
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 05-25-2017, 02:17 PM
  2. Replies: 7
    Last Post: 07-14-2012, 01:02 AM
  3. Replies: 3
    Last Post: 07-27-2011, 02:07 PM
  4. Delete Query Deleting Too Much
    By TundraMonkey in forum Queries
    Replies: 6
    Last Post: 06-30-2009, 02:39 PM
  5. Delete query, deleting customers froma table.
    By keithsrobinson in forum Queries
    Replies: 2
    Last Post: 02-14-2006, 11:33 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums