General discussion

  • Creator
  • #2257646

    An Access delete query with a composite key.


    by theandoman ·

    I ran into a little problem with a project of mine. I need to run a delete query, called up by a form, to delete records using a composite key. I can’t find the syntax for this anywhere.

    The form has a text box for entry of the Last name, First name (my composite key in that format). Ex.- you enter name (Smith, Joe) in the text box and click on a button labeled delete. This calls up the delete query to remove that person’s listing from our phone directory database.

    I can’t find the VBA code for this with the composite key. Is the syntax for this any different?

    Thanks in advance.

All Comments

  • Author
    • #3199474


      by tony hopkinson ·

      In reply to An Access delete query with a composite key.

      delete from phonenumbers where fullname = ‘Smith, Joe’

      or delete from phonenumbers where surname = ‘Smith’ and Forename = ‘Joe’

      If the latter you need to break up the ‘composite’ key.
      look up access’ string functions, left, right substr (mid ?) etc. There is another one the will give you the character position of the comma.

      Using the name as the key is a bit of a duff constraint isn’t it, what if you know two guys called Joe Smith?

      • #3199338

        Found a solution

        by theandoman ·

        In reply to Confused

        Thanks for your reply, I ended up splitting the composite key into two text boxes (Last name and First name) and am running a delete query with a sub query. This setup’ll run the subquery in the phone book for the first name and then automatically run the delete query for the last name out of those with the same first name.

        In cases of the same name we’ll use either their preferred name (Jim or James, whatever they prefer to be called, even nicknames). Worst case scenario is adding their middle initial.

        • #3199324

          Well it will work

          by tony hopkinson ·

          In reply to Found a solution

          I’d get shot If I did it in my line of work though.

          Delete from phones where surname = Substr([editfield],1,instr([editfield],’,’) – 1)
          and forename = substr([editfield],instr([editfield],’,’ + 2,datalength([editfield]))

          should work in sql I think.
          Editfield being your dataentry box and assuming that the comma and space delimiting surname and forename are guaranteed.

        • #3199276

          Thanks again

          by theandoman ·

          In reply to Well it will work

          That’s one of the benefits of state government work. I’m a network tech in a remote site, so they (the Capital people) think I (and all other remote site techs) arte just above the stuff that pond scum lives off of. LOL

          I am currently learning Access VBA and doing it in the midst of studying for the last two tests towards my MCSA. Sometimes I think I can almost feel some of what I learn trickling out my ears as I cram more in.

          I’ve copied down the syntax you’ve given me for this, as I’m trying to learn as much as I can as quickly as I can, Especially SQL. Thanks.

Viewing 0 reply threads