deleting a subform's corresponding record

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

Hello again,

I have a form that includes multiple subforms linking one-
to-one on two fields, namely detailled diagnosis sheets.
The user is supposed to fill out only one detailled
diagnosis sheet, depending on which diagnosis he
primarily indicated on the parent form.
Consequently, if the diagnosis is later changed, the old
detailled diagnosis should be deleted.
I am currently just opening the table of the old
diagnosis as a recordset and performing a search on the
two linking values, but as the records are getting quite
large and the computer is slow, is there a better way?

TIA

Chris
 
Instead of opening a recordset and doing a search, use code to run a delete
query using the linking field as a parameter. The SQL will be much quicker
than the search and delete.

strSQL = "DELETE * FROM tblMyTable WHERE fldMyField=" & txtMyTextbox & ";"
CurrentDb.Execute strSQL, dbFailOnError

If txtMyTextbox contains a text value instead of a numeric value, adjust the
line to:
strSQL = "DELETE * FROM tblMyTable WHERE fldMyField=""" & txtMyTextbox &
""";"

When you execute this code, it will NOT prompt prior to the delete, so make
sure you want to do this before it is done. You may want to provide your own
message box to warn the user and allow them to cancel before running this.
 
Back
Top