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 second one askes for a vendor no.
Criteria: [Enter Vendor No:] -- (same vendor no. as above)
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 (an error message of some type)?

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

 

[Piet Linden]

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

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 (an error message of some type)?

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

One way is to create an unbound form with a dropdown where you choose
the ID of the record you want to delete. Then in the delete query,
you modify the criteria so that the ID field points to the control on
the unbound form.

e.g. WHERE VendorNo = Forms![frmYourFormName]![cboVendorNo]

then you can execute both delete queries in the click of a button.

CurrentDB.Execute "qdelVendor", dbFailOnError
CurrentDB.Execute ...

 

[kealaz]

Hi Piet,

This worked perfectly. Thank you very much!