General discussion

Locked

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.

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Confused

by Tony Hopkinson In reply to An Access delete query wi ...

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.

PS.
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?

Collapse -

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.

Collapse -

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.

Collapse -

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.

Back to Software Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums