Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Can't Run Function from Query (Access 97)

    I want to delete a number of records from this database. There are quite a few one-to-many linked records linked to each main record so I have written a VBA function that will run Delete queries to delete all the dependent records and then delete the main record. I will use a query to select the main records that I want to delete and I had intended to simply call my function in that selection query and pass each selected primary key to the function. When I run that selection query I get this error: "Error 2486 You can't carry out this action at the present time."! The VBA code stops on the first DoCmd.RunSQL command in the function. The detailed code in the function was copied from another routine that was initiated manually that I have used successfully. I've never seen that error before and can't figure out why it won't work.

    Any ideas?

    Thanks,
    Bill

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't Run Function from Query (Access 97)

    If you set "Enforce Referential Integrity" and "Cascade Delete Related Records" for the relationships between the main table and its children (and between the children and their children, if applicable), you can run a delete query on the main table; this will automatically delete the corresponding child records.

    If you can't or won't do that, we'd need to have detailed information about the code you're using.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Can't Run Function from Query (Access 97)

    Hans,

    Yes, I think many of the links have the settings you mentioned and they would delete OK but this is an old database that is working fine and I am reluctant to make changes that I don't have too. I'll go do that as a last resort but let me give you a sample of the code involved and see if I'm trying to do something that is not allowed, or something like that.

    Here is the beginning of the function:

    Public Function DeleteMDRecords(MailID As Long) As Long
    ....Dim db As DAO.Database
    ....Dim MainRs As DAO.Recordset
    ....Dim MainID As Long
    ....Dim strSQL As String

    ....Const AffilSQLn = "DELETE AffiliationAssign.* FROM AffiliationAssign WHERE ((AffiliationAssign.MainID)="
    ..... . . additional similar constants . . .
    ....Set db = CurrentDb()
    ....'get the related Main records
    ....Set MainRs = db.OpenRecordset("SELECT Main.MainID, Main.MailID " & _
    .................................."FROM Main WHERE ((Main.MailID)=" & MailID & ");")
    ....If MainRs.RecordCount <> 0 Then
    .......MainRs.MoveFirst
    .......Do Until MainRs.EOF
    ............MainID = MainRs("MainID")
    ............'delete all records related to this MainID
    ............strSQL = AffilSQLn & MainID & ");"
    ............DoCmd.RunSQL strSQL
    ............. . . more of the same kind of thing . . .

    It fails on that DoCmd.RunSQL above.

    The query that calls the function looks like this, for testing purposes:

    SELECT MailData.MailID, DeleteMDRecords([MailID]) AS Expr1
    FROM MailData
    WHERE (((MailData.MailID)=12345));

    Do you see anything questionable in this?

    Thanks,
    Bill

    P.S. How do you make indented stuff stay indented in these messages?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Can't Run Function from Query (Access 97)

    DAO operates at a lower level than DoCmd. Trying to delete records using DoCmd.RunSQL from DAO causes interference. Change the DoCmd.RunSQL line to its DAO equivalent

    db.Execute strSQL, dbFailOnError

    Note: it's rather unusual to delete records from a select query. Keep in mind that Access will (try to) delete the records each time you open the query, or if it is already open, when you switch to another window then back to the query.

    About your PS: there are two ways to preserve indentation in a post

    1) Place a <!t>[pre]<!/t> tag before, and a <!t>[/pre]<!/t> tag after your code. This will display the text you enter "as is" in a fixed-width font.
    Be careful: text between these tags isn't wrapped automatically, so long lines make the post look weird and may cause the Lounge window to scroll horizontally. Because of this, you get a warning when your post contains <!t>[pre]<!/t> tags.
    The <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags can be typed, and they are also available from the 1-Click TagPanel.

    2) Replace the number of spaces representing a tab with the <!t>[tab]<!/t> tag (also available from the 1-Click TagPanel).
    I'm lazy, I copy code into an empty Word document, run the macro from <post#=346,967>post 346,967</post#>, then copy the result and paste it into a post.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2002
    Location
    Midlothian, Virginia, USA
    Posts
    875
    Thanks
    0
    Thanked 2 Times in 1 Post

    Re: Can't Run Function from Query (Access 97)

    Your code suggestion worked like a charm! Thanks also for the suggestions on keeping text formatted or inserting Tabs that stay. I knew that there was some HTML-like codes that would do it but I didn't know what they were and I didn't make the connection to "TagPanel".

    Thanks,
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •