none
Update Access Delete Query & Pass if via VBA code built into a form. RRS feed

  • Question

  • Ok, I have a simple delete query in Acess here it is in SQL form (DELETE [1_Data].Ship_ID, [1_Data].Source, *
    FROM 1_Data
    WHERE ((([1_Data].Ship_ID) In (select  [Ship_ID]
    from [DUP])) AND (([1_Data].Source) Like 'WWF_201504*'));

    I want to update the Like 'WWF_201504*' piece each month with the correct date like the next month would be 'WWF_201505'.  How do I pass this parameter to the query to update it before it delete the records from my table using VBA code in a Form in Access?

    Thanks

    Monday, July 13, 2015 4:35 PM

Answers

All replies

  • Hi,

    If you're doing this in VBA, then perhaps you could try something like:

    CurrentDb.Execute "DELETE FROM 1_Data " _
       & " WHERE ID In(SELECT Ship_ID FROM Dup) " _
       & " AND Source Like 'WWF_" _
       & Format(Date(), "yyyymm") & "*'", dbFailOnError

    (untested)

    Hope that helps...

    • Proposed as answer by André Santo Monday, July 13, 2015 4:57 PM
    • Marked as answer by L.HlModerator Friday, July 24, 2015 5:32 AM
    • Unmarked as answer by L.HlModerator Friday, July 24, 2015 5:32 AM
    Monday, July 13, 2015 4:45 PM
  • Thanks, but when I put this into the event procedure for the query, it returns the error message "too few parameters".  I actually would like it to either pop up and ask for that parameter or reference  a text field which includes that parameter in the form.
    Monday, July 13, 2015 6:03 PM
  • Hi,

    Not sure exactly what you did because queries don't have events, but if you didn't want to use VBA, then you could try this as a query:

    DELETE FROM 1_Data WHERE ID In(SELECT Ship_ID FROM Dup)

    AND Source Like "WWF_" & [Enter Year and Month (yyyymm)] & "*"


    Hope that helps...
    • Edited by .theDBguy Monday, July 13, 2015 6:08 PM
    • Proposed as answer by ryguy72 Thursday, July 16, 2015 11:36 PM
    Monday, July 13, 2015 6:07 PM
  • Hi,

    I'm attaching this VBA code to a button in a form see screen shots below. When I click the query button it returns this VBA editor screen in which I entered your code.

    Monday, July 13, 2015 6:42 PM
  • Awww man, it didn't capture the screen shots. I inserted them here.

    • Marked as answer by L.HlModerator Monday, August 10, 2015 5:34 AM
    Monday, July 13, 2015 6:45 PM
  • Hi,

    Thanks for posting the screenshot. Is either 1_Data or Dup a parameter query? If so, you could try this code: Leigh's Execute Action Queries

    Monday, July 13, 2015 7:33 PM
  • Hi,

    I have made a sample database to test the code you provided, the sql could successfully delete the records in the table [1_Data], could you please run the below query to make sure there are available records could be deleted ?

    select * From 1_Data Where ID in (select Ship_ID from DUP) And Source like 'WWF_201507*'

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, July 14, 2015 7:40 AM
    Moderator