Saving a Parent form, while a relational subform is open on the Parent form.

[dbhokie]

(The record cannot be deleted or changed because 'tblinvoice' includes related records.



I have a form "Transactions", It has a sub-form "Invoices".

Originally I locked all the fields on transactions so the user could enter in invoices in a datasheet view in the "invoices' sub-form and attach them to the current transaction displayed above in the Transaction form. Then they wanted a lock unlock button in case they wanted to edit the transaction data in the Transaction form so I entered in:

So....
I did:

Private Sub cmdLockUnlock_Click()


   Dim ctrl As Control
    Dim strControl As String
    
    strControl = Me.ActiveControl.Name
    
    On Error Resume Next
    For Each ctrl In Me.Controls
        With ctrl
            If .Name <> strControl Then
                .Locked = Not .Locked
            End If
        End With
    Next ctrl
End Sub

Now what is going on is the following. If you make the form "dirty" then try to close out:





I can't think of how to deal with this. Do I need to save the subform, then the form with an if dirty?

[June7]

Makes no sense to me to lock controls then give user ability to unlock.

If the controls are not locked in design so this code is not necessary, is there an error when editing transaction?

[dbhokie]

So when the open the form, the Parent form "Transactions" is locked to prevent them accidentally changing the record while adding invoices to the associated "transaction". (It is a one to many relationship). They requested an unlock button in case while adding invoices they notice there is an error in the transaction. This allows them to unlock it and edit the transaction data on that form but only if they purposefully do so by unlocking it (by clicking the button). All of the controls are locked when the form opens, this code "unlocks" them. I did manage to do that for them, but it won't work to save a record because the invoice subform is open on the same form and bound relationally to the transactions table:

[June7]

I don't understand how locking/unlocking controls would interfere with saving data.

Didn't answer my question.

[dbhokie]

Well June, you helped me realize what the problem was even without me asking the proper question.
I was having an error when I tried to close the form if I had so much as typed in "a" field on the main form:
The error was:
The record cannot be deleted or changed because 'tblinvoice' includes related records.
Well I just tried to re-create it and couldn't. That was because the field named "Contract Number" is the field on the "Transactions" table that links the "Invoice" table (which the subform is based off of).
I am an idiot, naturally "THAT FIELD" cannot be changed because it is the link to the below records that are open. (I don't want that field to be changed anyways), I think I need to change my code to not unlock that particular field does not unlock but the rest do.

In summary..when I was getting the error was when I typed anything into the related field which it can't change due to it being the many to one relationship field between that table and the subform's table.

To prevent this error from happening by user mistake (not my mistake) I just need to always keep that field locked.

[dbhokie]

I don't understand how locking/unlocking controls would interfere with saving data.


Didn't answer my question.

Seems I can be sure that if June7 sees my question (second time now), it is a stupid error on my part. I apologize. (albeit it still would have been a dumb error on my part without you seeing it) I don't know when trying to test it why I was typing and deleting in the linked field. Ugh

[mike60smart]

Looks like you are using the Contract Number as the Link Parent/Link Child value ??
This looks wrong to me.
I agree with June. It is pointless to have a Look/Unlock button.

[dbhokie]

Looks like you are using the Contract Number as the Link Parent/Link Child value ??
This looks wrong to me.
I agree with June. It is pointless to have a Look/Unlock button.

It is, and that was the problem when unlocking I typed into that field like an idiot.
It works, are you saying better practice may be to have a button to open the related transaction in a popup so they can fix whatever it is they see when entering invoices? If the user enters the data right the first time then they don't really need the unlock button, but they said while they enter an invoice (tied to the contract number) they may see where they entered something erroneously on the original transaction and wish to fix it.

[Minty]

There is no problem in editing the parent data at all on the same form, with the exception of the main PK linking field.
So your solution is fine just keep the linked ID field locked at all times.

[mike60smart]

On the form shown you have an AssetID which I am assuming is a PrimaryKey?

Do you then have a Subform for displaying the Rental Detals?

Is this Rental Subform using AssetID as the Foreign Key ?

[June7]

Not using autonumber field as PK?