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

    Delete query not deleting the record selected


    A select query based on two fields of a form selects the proper record however when it is converted to a delete query it always deletes the first record in the table.
    Sounds simple. I have used this method before with no problems.

    Any ideas?

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Post the SQL

  3. #3
    WAVP375 is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    I have a form with a list box bound to a query. The form is bound to a table. When the list box is clicked I populate four text boxes. A button is provided for the delete where it opens the delete query But only the first record is deleted not the one selected. When looking at it in spreadsheet view the proper record is selected.

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Open the delete query in design view. Click on the view icon top left and select SQL view, copy it and paste it here.

  5. #5
    WAVP375 is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    DELETE [Requirement Table].[Job Nbr], [Requirement Table].[Course Nbr]
    FROM [Requirement Table]
    WHERE ((([Requirement Table].[Job Nbr])=[Forms]![FrmRequirementDelete]![JobNumber]) AND (([Requirement Table].[Course Nbr])=[Forms]![FrmRequirementDelete]![CourseNumber]));

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    That looks good.

    When the list box is clicked I populate four text boxes
    How are you populating them? The surest way is to filter the form on the primary key of the listbox selection.

    Debug it by: just prior to running the query, display a msgbox with those two values so that you can see what is about to be deleted. If they are the incorrect values then you know that your form is not showing the correct data/record.

  7. #7
    WAVP375 is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    The msgbox shows the selected record but it is still deleting the first record. How can I tell which record my form is showing?

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    The msgbox showed the correct values in job number and contract and yet the query was still deleting some other record?

  9. #9
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    How can I tell which record my form is showing?
    Test it out by changing it to a SELECT Query first:
    Code:
    SELECT [Requirement Table].[Job Nbr], [Requirement Table].[Course Nbr]
    FROM [Requirement Table]
    WHERE ((([Requirement Table].[Job Nbr])=[Forms]![FrmRequirementDelete]![JobNumber]) AND (([Requirement Table].[Course Nbr])=[Forms]![FrmRequirementDelete]![CourseNumber]));
    By the way, in a DELETE query, you do not need to list individual fields after the DELETE word - as it deletes the WHOLE record, not just specific fields in your record.

  10. #10
    WAVP375 is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Dayton Ohio
    Posts
    171
    The primary key in this table is Job Nbr and Course Nbr. Does this have any effect?

    When changed to a select query it selects the proper record.

  11. #11
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    It should delete the exact same records as the select query returns.

    It shouldn't matter, but what if you tried like this:
    Code:
    DELETE *
    FROM [Requirement Table]
    WHERE ((([Requirement Table].[Job Nbr])=[Forms]![FrmRequirementDelete]![JobNumber]) AND (([Requirement Table].[Course Nbr])=[Forms]![FrmRequirementDelete]![CourseNumber]));
    Also, your Form is open when you are running this, correct?

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

Similar Threads

  1. Replies: 7
    Last Post: 07-14-2012, 01:02 AM
  2. Deleting multiple records selected in a Listbox
    By ankitmehtta in forum Access
    Replies: 4
    Last Post: 11-08-2011, 01:20 PM
  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