Inactivate Matching record in subform based on Main form

[wendyhnc]

I have a Main form
Company_id
Company
etc..
3 subforms (Address, Contracts, Contacts), all different tables with company_id as foreign key.

My question is, I have a button on the main form that when clicked a popup form opens for a comment to be entered, once the user clicks ok (on the popup) it inactivates the company on the main form. I haven't been able to figure out how to inactivate the linked records in the address, contracts and contacts tables.

Any suggestions? Thanks!

 

[KenReay]

Hi

What do you mean by 'inactivate', do you set some flag within the company record?

If yes the way to do it is as follows (for each subform)

Dim Rs as Recordset
Set Rs = ME.SubformControl1.FORM.RecordsetClone
If Rs.RecordCount > 0 Then
Rs.MoveFirst
Do Until Rs.EOF
Rs.Edit
Rs!ChkActive = False
Rs.Update
Rs.MoveNext
Loop
End If

You need to use your own subform control name(s) of course

More significantly, I would ask why do this?, you can get the same effect with a query joining on CompanyId, to see if Company is Active ? no duplication of data, no possibility of inconsistency, much better

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[wendyhnc]

I have a main table with company information, then 3 tables for address, contacts and contracts. The reason I have the three tables is because there is a many to one relationship.

Each table has an inactive_flg, so when I inactivate the Company record, I want to inactivate the child records (due to other forms that pull this data, I only want active records to show).

Thanks, I will try your suggestion.