Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error 3128 (Specify the table...) (Office XP)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>On searching, I've found some similar topics, but not exactly what I'm after...

    I have two tables, Provider and Schedule. Provider contains an (autonumber, but not primary key) ID field, and Schedule contains a ProviderID (integer) field.
    I also have a listbox, List0. I'm trying to write a query that deletes all items from Provider where ID=List0 and all fields from Schedule where ProviderID=List0.

    This is what I've written:
    SQL = "DELETE * FROM Providers LEFT JOIN Schedule ON Providers.ID = Schedule.ProviderID" & _
    " WHERE Providers.ID=" & List0
    ... and I receive the dreaded "Run-time error 3128: Specify the table containing the records you want to delete."

    What am I doing wrong?

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> I know I could achieve the required result by running two delete queries, one for each table. However I'd ideally like the user to see only one "You are about to delete x records" prompt. Another workaround would be to use Dcount to calculate x and display the prompt myself, but I'd rather just get the query right!
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

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

    Re: Error 3128 (Specify the table...) (Office XP)

    A delete query can delete records from only one table at a time, so you'll have to use 2 delete queries. You can suppress the "You are about to delete ..." message by inserting a line

    DoCmd.SetWarnings False

    before the line that executes the query. Don't forget to turn it back on with

    DoCmd.SetWarnings True

    afterwards.

    Note: if ID was the primary key in Provider, you could specify Cascade Delete Related Records for the relationship between Provider and Schedule. Deleting records from Provider would then also delete related records from Schedule, so that you'd need only one delete query.

  3. #3
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks

    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>Thanks, Hans.

    Providers also contains two text fields, Department and Institution. Together, these form my primary key, becuase I don't want any repeats of the Department-Institution combination. If I set ID as the primary key instead, is there an easy way I can retain the integrety of the Department and Institution fields?

    <img src=/S/doh.gif border=0 alt=doh width=15 height=15>Hold on, I've just found the "Indexes" button. I'd never spotted that before - I should be able to figure it out from here! Cheers again, Hans.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

Posting Permissions

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