Delete*FROM question

[mohobrien]

I have a form with a subform. The main form has a bunch of data in one table and the subform has more specific info. The tables are joined in a one to many relationship.
When I delete a record in the main table from the form, I want the related records in the other table also deleted.
I tried this:

' This is to delete the holder table entries for records that are deleted. 
 Private Sub Form_AfterDelConfirm(Status As Integer)
Dim StrSQL As String
Dim DispNumToDelete As String
DispNumToDelete = Me![Disposition Number]
StrSQL = "Delete*FROM tblHolders where tblHolders.DispositionNumber=DispNumToDelete"
DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True
End Sub

I checked DispNumToDelete and it holds the correct value as a string but I am always prompted to "Enter Parameter Value".
What am i missing?
PS Even a pointer to a help file will be useful to me as I have no skill level.

 

[Rich]

Set the CascadeDelete related records to yes in the relationships window

 

[mohobrien]

Many thanks!

 

[Pat Hartman]

Cascade delete will solve your problem if the query is being run to delete child records from a related table.

To explain why your query won't work - SQL has no way of accessing variables defined in your Access modules. You need to change the format of the assignment statement so that VBA evaluates the variable such that SQL will have a literal value to work with.

StrSQL = "Delete*FROM tblHolders where tblHolders.DispositionNumber = " & DispNumToDelete & ";"

You don't need to use a variable at all. The above statement should just reference the form field. Don't make variables unless you need them. Assigning a form control to a variable would only save execution time if the form control would need to be referenced multiple times. It takes more resources to reference a form field than an internal variable.

 

[mohobrien]

I will add that to my NB list.