Delete rows from multiple tables

[sailorguy]

Hello,

Newbie here

I am trying to use VBA to delete rows from multiple tables based on a textbox on a form. I have a simple form like this:

UserID
FirstName
LastName


I want a delete button on the form which will delete the row for UserID in both the Users table and the Numbers table. The Numbers tables is junction table.

The tables are like this

tblUsers
UserID (PK)
LastName
FirstName

tblPhoneLog
PhoneLogid (PK)
UserID (FK)
DnID (FK)
PhantomID (FK)

 

[jdraw]

Hello,

Newbie here

I am trying to use VBA to delete rows from multiple tables based on a textbox on a form. I have a simple form like this:

UserID
FirstName
LastName


I want a delete button on the form which will delete the row for UserID in both the Users table and the Numbers table. The Numbers tables is junction table.

The tables are like this

tblUsers
UserID (PK)
LastName
FirstName

tblPhoneLog
PhoneLogid (PK)
UserID (FK)
DnID (FK)
PhantomID (FK)

You can only delete records from 1 table at a time. If you're doing this is a program, save the number to be deleted, and use it in 2 separate (but similar) tables.

DELETE FROM UsersTable where UserID =

DELETE FROM NumbersTable where UserID =

 

[sailorguy]

You can only delete records from 1 table at a time. If you're doing this is a program, save the number to be deleted, and use it in 2 separate (but similar) tables.

DELETE FROM UsersTable where UserID =

DELETE FROM NumbersTable where UserID =

I see. Now simple question...how do I say "where UserID = Form Textbox 13" or whatever? In other words on the form I want the button to delete the records based on the current UserID (textbox) on the form.

 

[dcb]

Unless you have ref integrity; cascade delete's in your relationship
then the db will do this for you if you delete from tblUsers

 

[sailorguy]

Unless you have ref integrity; cascade delete's in your relationship
then the db will do this for you if you delete from tblUsers

I don't have referential integrity (cascade delete) set on these tables due to other issues. That's why I'm trying to see if there's a way to do it using VBA. I tried deleting the record from tblUsers, but it keeps the associated record in tblPhoneLog.

 

[dcb]

Makes sense:

So to expand on jdraw's reply:

Dim lngUserID as Long   ''Assume this is a number
Dim sqlUsers as string, sqlLog as string

lngUserID = Me.textbox13

sqlUsers = "DELETE FROM tblUsers where UserID = " & lngUserID & ";"
sqlLog =  "DELETE FROM tblPhoneLog where UserID = 	" & lngUserID & ";"

DoCmd.setwarnings false
DoCmd.RunSql sqlLog
DoCmd.RunSql sqlUsers
DoCmd.setwarnings true

 

[sailorguy]

THANKS!

That's exactly what i wanted to do! Now I know