Solved Impossible to modify check box in subform that has no modification restriction at all

[Etxezarreta]

Hello all,
A huge headache here:
I have a form, with a subform linked to a table that contains a yes/ no check box field. There is no edition or modification restriction whatsoever for the form, the subform or the fields of this subform.
When I filter the subform with a query, the returned values are the correct ones: but there is no way to change the checkbox value!! I have looking for similar issues, but nothing really helpful so far.
I am loosing it, here is my database: the form is f_ConsultationGestionContacts _Projets, the subform is sf_t_AffectationProjetA_Contacts_expend and the table is t_AffectationProjetA_Contacts_expend.
Many thanks in advance.
Etxe.

 

[Etxezarreta]

And the subform is definitely bounded to the table

 

[MajP]

Appears the query is coming back not updateable; However testing the query it should be updateable. The table is indexed, no joins, and I do not think an In clause can cause a query to be not updateable. I dropped it in the query window and it was updateable.

SELECT t_affectationprojeta_contacts_expend.id_tempcontactprojet,
       t_affectationprojeta_contacts_expend.id_contact,
       t_affectationprojeta_contacts_expend.fk_id_projet,
       t_affectationprojeta_contacts_expend.concatconcatprojet,
       t_affectationprojeta_contacts_expend.nom,
       t_affectationprojeta_contacts_expend.prenom,
       t_affectationprojeta_contacts_expend.structure,
       t_affectationprojeta_contacts_expend.profession,
       t_affectationprojeta_contacts_expend.statut,
       t_affectationprojeta_contacts_expend.specialite,
       t_affectationprojeta_contacts_expend.departement,
       t_affectationprojeta_contacts_expend.ville,
       t_affectationprojeta_contacts_expend.projet,
       t_affectationprojeta_contacts_expend.confirmed
FROM   t_affectationprojeta_contacts_expend
WHERE  t_affectationprojeta_contacts_expend.departement IN ( "87", "64" )
       AND t_affectationprojeta_contacts_expend.profession IN ( "Médecin" )
       AND t_affectationprojeta_contacts_expend.statut IN (
           "hospitalier", "liberal" )
       AND t_affectationprojeta_contacts_expend.projet IN (
           "alpha", "beta", "gamma" )

Also I checked the subform and it is set to allow edits and allow additions. I also set the recordset to inconsistent updates. So I could not figure it out.

Hopefully someone else can look. I checked the obvious.

 

[theDBguy]

And the subform is definitely bounded to the table

Hi. Since @MajP already verified your form's design, the next step would be to check your code, which I did and realized you may be doing the filtering in an unnecessarily complicated way (I can't say for sure, because I really don't know the purpose of the form), so I made some minor adjustments. Please try the attached and let us know if this approach will work for you.

 

[Uncle Gizmo]

Also, as you are only selecting from one table, you can simplify your SQL Statement:-

SELECT id_tempcontactprojet,"
       id_contact,
       fk_id_projet,
       concatconcatprojet,
       nom,
       prenom,
       structure,
       profession,
       statut,
       specialite,
       departement,
       ville,
       projet,
       confirmed
FROM t_affectationprojeta_contacts_expend
WHERE  departement IN ( "87", "64" )
       AND profession IN ( "Médecin" )
       AND statut IN (
           "hospitalier", "liberal" )
       AND projet IN (
           "alpha", "beta", "gamma" )

 

[missinglinq]

...Also I checked the subform and it is set to allow edits and allow additions...

But what are they set on the Main Form? If the Main Form cannot be updated, the Subform, which is a Control on the Main Form, can't be updated.

I'd check but I can't run the attachment...I only run v2007.

Linq ;0)>

 

[Etxezarreta]

spot on

Appears the query is coming back not updateable; However testing the query it should be updateable. The table is indexed, no joins, and I do not think an In clause can cause a query to be not updateable. I dropped it in the query window and it was updateable.

SELECT t_affectationprojeta_contacts_expend.id_tempcontactprojet,
       t_affectationprojeta_contacts_expend.id_contact,
       t_affectationprojeta_contacts_expend.fk_id_projet,
       t_affectationprojeta_contacts_expend.concatconcatprojet,
       t_affectationprojeta_contacts_expend.nom,
       t_affectationprojeta_contacts_expend.prenom,
       t_affectationprojeta_contacts_expend.structure,
       t_affectationprojeta_contacts_expend.profession,
       t_affectationprojeta_contacts_expend.statut,
       t_affectationprojeta_contacts_expend.specialite,
       t_affectationprojeta_contacts_expend.departement,
       t_affectationprojeta_contacts_expend.ville,
       t_affectationprojeta_contacts_expend.projet,
       t_affectationprojeta_contacts_expend.confirmed
FROM   t_affectationprojeta_contacts_expend
WHERE  t_affectationprojeta_contacts_expend.departement IN ( "87", "64" )
       AND t_affectationprojeta_contacts_expend.profession IN ( "Médecin" )
       AND t_affectationprojeta_contacts_expend.statut IN (
           "hospitalier", "liberal" )
       AND t_affectationprojeta_contacts_expend.projet IN (
           "alpha", "beta", "gamma" )

Also I checked the subform and it is set to allow edits and allow additions. I also set the recordset to inconsistent updates. So I could not figure it out.

View attachment 81368 Hopefully someone else can look. I checked the obvious.

Thanks a lot for your time!

 

[Etxezarreta]

Hi. Since @MajP already verified your form's design, the next step would be to check your code, which I did and realized you may be doing the filtering in an unnecessarily complicated way (I can't say for sure, because I really don't know the purpose of the form), so I made some minor adjustments. Please try the attached and let us know if this approach will work for you.

Spot on, it works fine, thank you.

 

[Etxezarreta]

But what are they set on the Main Form? If the Main Form cannot be updated, the Subform, which is a Control on the Main Form, can't be updated.

Linq ;0)>

Hello,
The main form can be updated

Hi. Since @MajP already verified your form's design, the next step would be to check your code, which I did and realized you may be doing the filtering in an unnecessarily complicated way (I can't say for sure, because I really don't know the purpose of the form), so I made some minor adjustments. Please try the attached and let us know if this approach will work for you.

A last question: what do mean by "unnecessarily complicated way" please? That would help me a lot to improve the way I create my lines of code.
Thanks.
Etxe.

 

[Etxezarreta]

Also, as you are only selecting from one table, you can simplify your SQL Statement:-

SELECT id_tempcontactprojet,"
       id_contact,
       fk_id_projet,
       concatconcatprojet,
       nom,
       prenom,
       structure,
       profession,
       statut,
       specialite,
       departement,
       ville,
       projet,
       confirmed
FROM t_affectationprojeta_contacts_expend
WHERE  departement IN ( "87", "64" )
       AND profession IN ( "Médecin" )
       AND statut IN (
           "hospitalier", "liberal" )
       AND projet IN (
           "alpha", "beta", "gamma" )

Thanks, done!

 

[Etxezarreta]

Hi. Since @MajP already verified your form's design, the next step would be to check your code, which I did and realized you may be doing the filtering in an unnecessarily complicated way (I can't say for sure, because I really don't know the purpose of the form), so I made some minor adjustments. Please try the attached and let us know if this approach will work for you.

I found out how to simplify, I wrote the request in a single chain of characters. Thanks again

 

[Uncle Gizmo]

I accidentally left a double quote in the SQL statement in this line:-

SELECT id_tempcontactprojet,"

That one there on the end, you will need to remove that.

 

[theDBguy]

I found out how to simplify, I wrote the request in a single chain of characters. Thanks again

Hi. Glad to hear you got it sorted out. Good luck with your project.