Delete record in dependent cascading combo box

[RFreund]

I have cascading combo boxes. The first is for Company and the second one which is dependent or "child" of the company is "client".
Client combo box is based on a query so I can select a client of the selected company. If select a company and client, then save the record, then delete the company - The companyid record is deleted and the client name is no longer visible in the form, but the clientid remains in the table. How, can I make sure both are deleted?

 

[GaP42]

Hi @dalski - I question what you are attempting or expecting to happen here:
The "Client Company" combo's purpose is to select a company from the list of all Companies (based on a Companies table query), so that the list of contacts is restricted to those associated to the Company. Do you use/maintain a table of Company - Contacts as the source?
Two text boxes showing who is the current Client Company and Client Contact should be displayed in the form.
The combos are used to update these text boxes.
You then indicate you delete the Client Company in the combo - this (I expect) does not delete the Client Company ID in the Proposal table as the source for the combo is the Companies table (I think). Anyway - why would you want that - there may be other current proposals that are active involving that Company (and Contact).
Note also that deleting the CompanyId in the proposals table has no impact upon the ClientContactID recorded. These two pieces are independent of each other (as far as the db is concerned). If you had a table where CompanyContacts was held and a FK to the CompanyContact then you would not hold both items in Proposals - but that is a further restructure of your data (normalisation).
If you wish to change the proposal so that the ClientCompany or ClientContact is changed then use the combos.
If the proposal is to be deleted - then delete the Proposal record.
If the Proposal record requires the removal of the ClientCompany and ClientContactID and will be saved in that state then consider:
- Does your Proposal table have ClientCompanyID and ClientContactID as required items? If so then the record cannot be saved in that state.
- If those constraints do not exist in your proposal table, then you can delete the ClientContactID or ClientCompanyID as needed using the textbox controls displaying these items in the form (if you add / show them on the form as suggested above).

 

[RFreund]

So to provide clarification:
While my markups point to the company and company contact, I really am asking about the "referral company" and "Referral contact". Which might be blank for any given job.
The user might select a referral company and contact by mistake. This will put a companyid and contacted into the referalcompanyid and referralcontactid field. If they simply delete the text in the referral company combobox it will remove the companyid from the referralcompanyid field and it will also "blank" the contact in the referral contact combobox. However, the referalcontactid remains in the table.

I have a Company Table, Contact Table, and Proposal Table.
contacts table has FK to the company table.
In the proposal table I have FK's to the company and the contacts table.

Edit -

Two text boxes showing who is the current Client Company and Client Contact should be displayed in the form.

Why do I need this?

 

[GaP42]

I have a Company Table, Contact Table, and Proposal Table.
contacts table has FK to the company table.
In the proposal table I have FK's to the company and the contacts table.

So a contact is associated to a company - so why have the FK to the company in the Proposal table? You can obtain/display that from the contactID FK in Proposal, joined to Contact table then to company via its FK to Company in the display of Company of the form - it would be a read-only, and dependent upon the Contact selected. Your combo for Company is used to filter the list of valid contacts for the Company chosen. That combo is not used to change the Company ID in the Proposal record - changes to the Contact ID will do that.
Essentially advocating that the function of finding a CompanyContact and the ability to delete the contact for a proposal are managed by separate controls, the first by the combo, the second by a textbox or command button to delete the ContactID (in Proposal)

If they simply delete the text in the referral company combobox it will remove the companyid from the referralcompanyid field and it will also "blank" the contact in the referral contact combobox. However, the referalcontactid remains in the table.

If you continue with both IDs you must realise that, as stated in my first post that these are two separate pieces of information. The action on one does not affect the other unless you tell Access to take an additional action.
In the AfterUpdate event of the Company Combo you would add code to make the control for Contact on the form be nothing (not point to any Contact). That could be as simple as:

If Me.cboCompany = "" Then
    Me.txtContactId = "" 
    ' where txtContactID is the name of the control on your form
    ' to show the current contactID in the proposal record
End If

An alternative is to create a button to "RemoveContact" which will do the same - set ContactID to "" (and CompanyId to "" - if you persist with the current proposal table structure).