Delete data from subform

Question

I have a form which has a subfrom displayed as adatasheet, and it is bound to a table.

The datasheet is populated based on calculations from fileds on the form, which mar result in 1 to fifty or more records. That part works fine.

Now Iam dealing with the situation in which a user changes a filed on the form. Since this may result in a different number of subrecords, I need to first delete the existing records and recreate them. I have a field on the form which (theoretically) will tell me the number of records on the subform.

Right now I set NumRecs equal to that number and then

Set rstSpread = dbsTemplate.OpenRecordset( _

"SELECT (some fields)] " & _

"FROM [Resource Date Spread]" & _

"WHERE (([Resource Date Spread].[Resource Data ID]) = " & ResDataID & ")")

For R = 1 To NumRecs

rstSpread.FindFirst "[Duration ID] =" & R

rstSpread.Delete

Next R

Which works, but I would rather count the number of records returned and use that for Numrecs.

Unless there is an easier way to delete the records surrently showing on the subform, before I repopulate them.

???

Answer 1

If you are deleting records, you would run a DELETE QUERY to delete the records (not understanding why you need to delete the existing records and then recreate them as it sounds a little like you don't quite have a well-designed structure) and then an append query and then requery the form.  No recordset iteration should be necessary but the big question is how is the subform related to the parent form and how are they linked (master/child fields) or are they even linked?

Answer 2

Note that deleting the records is almost certainly NOT what you want to do! Records in a Query have no independent existance; if you delete a record from a Query it permanently and irrevokably deletes the record from the underlying table. Is that really what you want to do?? I.e. are you running a MakeTable query or an Append query to populate a table and then displaying it?

You might want to consider instead just using a Select Query as the recordsource for the subform. Set its AllowEdits and AllowDeletes properties to No, to prevent users from messing up the data, and simply Requery it when the calculation changes.

If you really do want to create and then delete records you don't need a recordset and you don't need a loop: just use a Delete query

DELETE * FROM [Resource Date Spread] WHERE (([Resource Date Spread].[Resource Data ID]) = " & ResDataID & ")