forms & subforms & deleting records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi. I have a form and then a subform in it, whose values are based off the
information in the form (somehow, which the wizard did).

I have a button to delete a record from the subform, which works fine.

However, I want to have a button to delete a record from the form. The
problem with this is that the corresponding records lower in the
hierarchy/inheritance (the ones on the subform) need to be deleted first.

The subform is not based off of some sort of query, instead, this is its
record source: SELECT [tbl Dealer Staff Title].[Dealer Title Staff ID], [tbl
Dealer Staff Title].[Dealer Title ID] AS [tbl Dealer Staff Title_Dealer Title
ID], [tbl Dealer Staff Title].[Dealer Staff ID], [tbl Dealer Titles].[Dealer
Title ID] AS [tbl Dealer Titles_Dealer Title ID], [tbl Dealer Titles].[Dealer
Title], [tbl Dealer Titles].[Dealer Title Importance] FROM [tbl Dealer
Titles] INNER JOIN [tbl Dealer Staff Title] ON [tbl Dealer Titles].[Dealer
Title ID]=[tbl Dealer Staff Title].[Dealer Title ID];

Is there a way in VBA to go through the subform and delete each record (if
there are records) and then subsequently delete the main record. The last
part is easy, it is just that I am not sure, first of all, how to determine
how many records there are (or I could use come kind of while loop with some
kind of condition). The other problem is that is there a way to bypass the
“are you sure you want to delete this†so that instead it is just deletes all
lower records instantly?

Thanks, I’d appreciate any help, I am sorry I cannot be any clearer, I am
very new to Access.
 
If the main form is bound to one table, and the subform to a related table,
you could use the Relationships window (Tools menu) to create a relationship
between them, and check the box for Cascading Deletes. Then when you delete
a record in the main form, Access will automatically delete all the records
in the subform.

If that is not desirable, you could execute a Delete query statement to
delete all the subform records. This kind of thing:

Dim strSql As String
strSql = "DELETE FROM [MyRelatedTable] WHERE [MyForeignKey] = 999;"
dbEngine(0)(0).Execute strSql, dbFailOnError
 
Thanks!

Allen Browne said:
If the main form is bound to one table, and the subform to a related table,
you could use the Relationships window (Tools menu) to create a relationship
between them, and check the box for Cascading Deletes. Then when you delete
a record in the main form, Access will automatically delete all the records
in the subform.

If that is not desirable, you could execute a Delete query statement to
delete all the subform records. This kind of thing:

Dim strSql As String
strSql = "DELETE FROM [MyRelatedTable] WHERE [MyForeignKey] = 999;"
dbEngine(0)(0).Execute strSql, dbFailOnError

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

virgirl920 said:
Hi. I have a form and then a subform in it, whose values are based off
the
information in the form (somehow, which the wizard did).

I have a button to delete a record from the subform, which works fine.

However, I want to have a button to delete a record from the form. The
problem with this is that the corresponding records lower in the
hierarchy/inheritance (the ones on the subform) need to be deleted first.

The subform is not based off of some sort of query, instead, this is its
record source: SELECT [tbl Dealer Staff Title].[Dealer Title Staff ID],
[tbl
Dealer Staff Title].[Dealer Title ID] AS [tbl Dealer Staff Title_Dealer
Title
ID], [tbl Dealer Staff Title].[Dealer Staff ID], [tbl Dealer
Titles].[Dealer
Title ID] AS [tbl Dealer Titles_Dealer Title ID], [tbl Dealer
Titles].[Dealer
Title], [tbl Dealer Titles].[Dealer Title Importance] FROM [tbl Dealer
Titles] INNER JOIN [tbl Dealer Staff Title] ON [tbl Dealer Titles].[Dealer
Title ID]=[tbl Dealer Staff Title].[Dealer Title ID];

Is there a way in VBA to go through the subform and delete each record (if
there are records) and then subsequently delete the main record. The last
part is easy, it is just that I am not sure, first of all, how to
determine
how many records there are (or I could use come kind of while loop with
some
kind of condition). The other problem is that is there a way to bypass
the
"are you sure you want to delete this" so that instead it is just deletes
all
lower records instantly?

Thanks, I'd appreciate any help, I am sorry I cannot be any clearer, I am
very new to Access.
Click to expand...
Click to expand...
 
Back
Top