DELETING records from two tables

[kealaz]

I want to be able to delete records from two tables.

I am currently using a macro (mcDeleteVENDOR) which has two actions, both
OpenQuery

qryDeleteVENDOR_a
qryDeleteVENDOR_b

They are both delete queries.

The first one askes for a vendor no.
Criteria: [Enter Vendor No:]
and then deletes the appropriate record from tblMANUF

The first one askes for a vendor no.
Criteria: [Enter Vendor No:]
and then deletes the appropriate record from tblVENDOR

The tables are as follows....

tblMANUF
MANUF
VENDORNAME
VENDORNO

tblVENDOR
VENDORNAME
ADDR_ST
ADDR_CITY
PHONE
FAX
ACCOUNT
VENDORNO

The Primary key for tblVENDOR is VENDORNO, and that is also how they are
related. tblMANUF does not have a primary key.


When I run the macro (mcDeleteVENDOR) to delete a vendor, I get two little
pop up windows asking me the same information.


I have two questions.

1) Can I do something so that I only get one little pop up window to get
the VENDORNO and both queries can use the same information?

OR

Can I delete the information from both tables using one Delete Query?




2) If I enter a bogus number that does not exist in either table, there is
no indication that there was no record found to delete. Whether I enter a
correct number or a number that is not there, the whole thing runs the same.
Can I do something that will tell the user, "record not found" so that they/I
know that the vendor that I am trying to delete didn't get deleted, because
the number was not found?


Thank you for any and all help!!!!!!

 

[kealaz]

Hi Jack,
Thank you for your reply. I am not too concerned about a typo deleting
the wrong record. Chances are that I am going to be the only user making
deletions from the records. And, I would need to "look up" the vendor number
prior to making the deletion. Additionally, it is a function that is rarely
used. I would however, like to have it working properly for when it is used.
I wouldn't be opposed to using a form to make the deletions. I don't
know how to create a form that would delete from TWO different tables.
Having said that, I still think using the macros is an option. Regardless, I
would like to proceed with this so any help on how to do any of the above
would be greatly appreciated.

Thank you!!!
Kealaz

I doubt that you will be able to meet all your objectives using a Macro that
contains two delete queries.

I would caution you against the methodology that you are using to accomplish
the objective. The problem is that you are deleting records based to the
user's ability to accurately type in the correct VENDORNO. One minor typo
and the wrong record is deleted. Worse still you have no way of know that
the wrong record was deleted.

Suggest that you make the deletions from a form that will display all the
relevant information associated with the Vendor that you wish to delete.
From such a form you can accomplish all your objectives and also be able to
do a sanity check to ensure that the right Vendor is being deleted.

It appears that for every record in tblMANUF there is one and only one
record in tblVENDOR. If that is correct then it would make more sense to put
all the data in a single table. If for no other reason you will be able to
use the Macro with a single Delete Query assuming that you are really are
unconcerned about a typo deleting the wrong record.

Jack Cannon

I want to be able to delete records from two tables.

I am currently using a macro (mcDeleteVENDOR) which has two actions, both
OpenQuery

qryDeleteVENDOR_a
qryDeleteVENDOR_b

They are both delete queries.

The first one askes for a vendor no.
Criteria: [Enter Vendor No:]
and then deletes the appropriate record from tblMANUF

The first one askes for a vendor no.
Criteria: [Enter Vendor No:]
and then deletes the appropriate record from tblVENDOR

The tables are as follows....

tblMANUF
MANUF
VENDORNAME
VENDORNO

tblVENDOR
VENDORNAME
ADDR_ST
ADDR_CITY
PHONE
FAX
ACCOUNT
VENDORNO

The Primary key for tblVENDOR is VENDORNO, and that is also how they are
related. tblMANUF does not have a primary key.


When I run the macro (mcDeleteVENDOR) to delete a vendor, I get two little
pop up windows asking me the same information.


I have two questions.

1) Can I do something so that I only get one little pop up window to get
the VENDORNO and both queries can use the same information?

OR

Can I delete the information from both tables using one Delete Query?




2) If I enter a bogus number that does not exist in either table, there is
no indication that there was no record found to delete. Whether I enter a
correct number or a number that is not there, the whole thing runs the same.
Can I do something that will tell the user, "record not found" so that they/I
know that the vendor that I am trying to delete didn't get deleted, because
the number was not found?


Thank you for any and all help!!!!!!

 

[kealaz]

I have two questions.

1) Can I do something so that I only get one little pop up window to get
the VENDORNO and both queries can use the same information?

OR

Can I delete the information from both tables using one Delete Query?


2) If I enter a bogus number that does not exist in either table, there is
no indication that there was no record found to delete. Whether I enter a
correct number or a number that is not there, the whole thing runs the same.
Can I do something that will tell the user, "record not found" so that they/I
know that the vendor that I am trying to delete didn't get deleted, because
the number was not found?


Thank you for any and all help!!!!!!

 

[a a r o n _ k e m p f]

this would work for you, if you just moved to SQL Server
----------------------------------------------------------------
create procedure deleteFromTwoTables
(
@Vendor INT
)
As

Delete From Table1 Where Vendor = @Vendor
Delete From Table2 Where Vendor = @Vendor