Cannot enable edit mode in a sub-form, in a many to many relationship

[HubCon]

Hi
I would be really grateful if someone can help point me in the right direction to solve a problem. I am creating a database for tools, which has a many to many relationship in it. Each tool can be used in many parts, and each part can need multiple tools.
I think I have managed to set up the link table correctly, but I am having trouble with viewing the data in a sub-form.
The main form [Funique], has a sub-form [FQUsedOnSubForm]. When I open up the [Funique] form directly, I can see the PartNumber & the Xnumber fields, and can edit/create.


However, when a user searches for a tool, a query runs and the results are displayed in a continuous form. A button in this form opens the [Funique] form, but in view only mode, to prevent accidental changes. A button on [Funique] called Edit Mode, allows the editing of data. This works well for all the controls except for the sub-form.
If there is existing data in the sub-form, it is shown, but cannot be edited or added to. If there is no existing data, the sub-form has no data displayed and no option to add.
The sub-form is in a tabbed/paged section of [Funique]

I cannot understand why the data is visible/editable when the form is opened directly, but not when the form is opened indirectly and the Edit Mode button clicked.

The form [Funique] has the following code for the click event of the Edit Mode button:
Forms!Funique.cmdSaveCloseFunique.Visible = True
ToolDescription.SetFocus
Forms!Funique.cmdButFuniqueEditMode.Visible = False
Forms!Funique.AllowEdits = True

Should the last line of code enable the sub-form to be editable?
I have added the following, but this has no effect:
Me.FQUsedOnSubForm.Enabled = True

I have also tried the following, and none work or throw up error messages:
Forms!Funique.FQUsedOnSubForm.Form.PartNumber.Allo wEdits = True
Me.FQUsedOnSubForm.Enabled = True
Me.FQUsedOnSubForm.Locked = False
Me!page1.FQUsedOnSubForm.Form.AllowEdits = True
Forms![Funique]![FQUsedOnSubForm].Enabled = True
Forms![Funique]![FQUsedOnSubForm].Form.Enabled = True
Forms!Funique.FQUsedOnSubForm.Form.AllowEdits = True

The database is still work in progress, but this is a fundamental part of it. I cannot proceed until this works. The database is 3mb in size if anyone wants to review it. (Using access 2013)

Regards
David

[June7]

I will look at db if you want to provide. Follow instructions at bottom of my post.

[davegri]

Me.FQUsedOnSubForm.Form.AllowEdits = True

Assumes code above is on Funique and FQUsedOnSubForm is the name of the control on Funique tab holding the subform.

[June7]

That should work as well as Forms!Funique.FQUsedOnSubForm.Form.AllowEdits = True​, which OP said they attempted.

Tab control is irrelevant when referencing objects sitting on it.

[Welshgasman]

Sounds like you query is non editable?

http://allenbrowne.com/ser-61.html

[HubCon]

Hi June7
See the attached database
ToolDatabase-4.zip
On opening, first open the Funique form. Click on the WhereUsed tab, and you can see the numbers in the sub-form. It is also possible to edit & enter new numbers.
Close this, then use the Main form to view the tools. If you click on button "TOOL number search", and type in 16-, a list of tools will appear.
From this list you can see a "view tool" button on the right. Click on this and it will open Funique in view only mode.
Clicking the Edit Mode button does not allow editing or the addition of new numbers in the WhereUsed tabbed sub-form.
thank you for looking at this.
David

[HubCon]

DaveGri
I have tried similar to this but it did not work before. Tried exactly as you have written, and it appeared to have no effect. Then tried editing a number, and found it was possible. I could not add a new item though.
I added another line with AllowAdditions, and this again "appeared" not to work. Tried again, and it worked. I think possibly I've been using only AllowEdits or Enabled, and this has confused me when viewing an empty sub-form. My poor knowledge of what is a sub-form is, and how it is controlled has thrown me.
I have spent hours on this, and got nowhere, and yet a simple suggestion has helped clarify the requirement, and my understanding. Your help is very much appreciated.

[davegri]

DaveGri
I have tried similar to this but it did not work before. Tried exactly as you have written, and it appeared to have no effect. Then tried editing a number, and found it was possible. I could not add a new item though.
I added another line with AllowAdditions, and this again "appeared" not to work. Tried again, and it worked. I think possibly I've been using only AllowEdits or Enabled, and this has confused me when viewing an empty sub-form. My poor knowledge of what is a sub-form is, and how it is controlled has thrown me.
I have spent hours on this, and got nowhere, and yet a simple suggestion has helped clarify the requirement, and my understanding. Your help is very much appreciated.

This way works for me.

Private Sub cmdButFuniqueEditMode_Click()
Forms!Funique.cmdSaveCloseFunique.Visible = True
ToolDescription.SetFocus
Forms!Funique.cmdButFuniqueEditMode.Visible = False
Forms!Funique.AllowEdits = True


Me.FQUsedOnSubForm.Form.AllowEdits = True
Me.FQUsedOnSubForm.Form.AllowAdditions = True


'FQUsedOnSubForm.SetFocus
'Me.AllowEdits = True
'Forms!Funique.FQUsedOnSubForm.Form.PartNumber.AllowEdits = True
'Me.FQUsedOnSubForm.Enabled = True
'Me.FQUsedOnSubForm.Locked = False
'Me!page1.FQUsedOnSubForm.Form.AllowEdits = True
'Forms![Funique]![FQUsedOnSubForm].Enabled = True
'Forms![Funique]![FQUsedOnSubForm].Form.Enabled = True
'Forms!Funique.FQUsedOnSubForm.Form.AllowEdits = True
End Sub

Edit: Also need .AllowAdditions=True
to enable adding recs in subform.

[June7]

Why open a form as 'view only' then give users discretion to make it editable? Just open editable to begin with.
If I make a form a 'view only' form it stays that way.

I followed your instructions. That subform is not editable regardless of how it is opened.

From Allen Browne list of reasons for non-editable dataset: It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables.

This form with that RecordSource cannot serve both purposes. Suggest you simplify RecordSource and bind form to table, not query that includes related tables, especially the parent table that main form is bound to.