Help with Creating and Using Temporary Recordset to Delete Unwanted Records (1 Viewer)

[psimpson]

Currently using VBA for Ms Access 2007. The current issue I want help with is the basic syntax, objects and/or methods required to do the following:

Read a specific table (say table1) from project where form num equals num found in table1 Then
If the reg_number field from table1 = reg_number field from table2
delete all records where equal EndIf
Check for num equates in additional tables (table2 … 4) and delete records where equal.

Any help is appreciated.

Thanks.

 

[sparks80]

Hi,

I am finding it hard to understand what you need to achieve. Is this correct?

Firstly you have a form with a number on it.

You want to search a table called table1 and find records where a certain field matches this number.

Next you want to delete all records from tables 2 to 4 where a field named reg_number is present and equal to the value in table1.

 

[psimpson]

This is correct. But, the record in table1 which matches the form value should be deleted as well. Basically, I want to delete the child record for table 1 before deleting table1.

I hope this helps to clarify the issue and thanks for responding.

 

[steveboydon]

If you have the relationship set to cascade deletes, then when you delete the parent record the child record should autodelete due to cascades.

so try

strWhere = me.controlonform

docmd.runsql "DELETE * FROM table1 WHERE nameoffieldintable ='" & strWhere & "'"

note that the quotes after the = are single then double. The str value needs to be in single quotes.

Is this what you want.

Steve

 

[psimpson]

Yes. This is exactly the result needed. Will modifying the relationship to cascade delete cause the application to perform differently for non-delete transactions too? This is an existing application and I want to make sure that changing the relationship will not affect any other existing functionality. Can you confirm this for me?

Thanks.

 

[sparks80]

I think I understand your set up now.

So you have a relationships set up with the reg_number in table1 linked to the reg_number in tables 2-4.

For the relationships is "enforce relational integrity" checked or not? You will need to do this before cascade updates will work.

 

[steveboydon]

Cascade deletes basically ensures that when you delete a parent record all the children are deleted. If you delete all child records, the parent will still remain. It is only the parent deletion that deletes all children

As long as this is what you intend to do across all records, then this will be fine for this scenario.

However, it does depend on what your database is doing.

If we take a family database, a father can have many children. However, when the father dies, the children still remain.

One option I often use, is to soft delete records. Basically filter a parent record as maybe 'inactive' have the form source as a query based on the original table filter by the 'inactive' value = false etc.

You could then in effect 'promote' a child record or reassociate it to another parent record.

Another example a database with companies and employees. a company may cease to trade and all the employees could move to a new company. In this case you would just reassociate with another company.

The final example where you would want to delete all the child records. A database recording house information. Chances are when you delete a house, you would want to delete all the rooms with it

Hope this helps.

Steve