[AccessD] field cannot be updated...

Fri Mar 14 09:12:13 CDT 2014

First of all
database is linked to an mysql tables via ODBC 5.1 driver

1.You have right but I tried all the methods I knew about primary and index

2. yes person, is a name which is saved in person.T_people and is Unique 
because of many incompatibilities between mysql and utf-8 codepage
(e.g. utf-8 can't separate the Greek language letters with tons and there 
are many cases in Greek names in which the name may be seems the same but it 
doen't because the word is highlighted in another letter... and that's a 
problem)

3. record source of peoplefilms.IDPerson is:
SELECT T_people.ID_person, T_people.person FROM T_people ORDER BY 
T_people.person;
With that when the user writes a new name if it isn't alreadh into the list, 
it added on T_people getting a new people.ID_People which is autonumber

the not in list code is a known one:
Dim db As dao.Database
Dim rs As dao.Recordset
Dim strMsg As String

    strMsg = "'" & NewData & "' greek message" & vbCrLf & vbCrLf
    strMsg = strMsg & "greek message"
    strMsg = strMsg & vbCrLf & vbCrLf & "greek message No greek message"

    If MsgBox(strMsg, vbQuestion + vbYesNo, "greek message;") = vbNo Then
        Response = acDataErrContinue
    Else
        Set db = CurrentDb
        Set rs = db.OpenRecordset("T_people", dbOpenDynaset)
        On Error Resume Next
        rs.AddNew
            rs!person = NewData
        rs.Update

        If Err Then
            MsgBox "greek message"
            Response = acDataErrContinue
        Else
            Response = acDataErrAdded
        End If

    End If

-----Αρχικό μήνυμα----- 
From: Paul Hartland
Sent: Friday, March 14, 2014 3:03 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] field cannot be updated...

ok, I may be missing something here but going by your table descriptions:

1.  Why all the primary keys ?, I would have thought putting a primary key
on people.ID_People and peoplefilms.aa_peoplefilms would be enough and
maybe index peoplefilms.ID_person.

2.  In the people table the field called person, is this a name ?, if so
why have it set to unique ?

3.  You mentioned having a 'not in list' on peoplefilms.IDPerson which I
assume you meant peoplefilms.ID_Person, what's the reason behind that ?

Paul

On 14 March 2014 12:41, Kostas Konstantinidis <kost36 at otenet.gr> wrote:

> yes Paul,
> I just renamed the fields because in their first version were in greeklish
> I think there is no difference to the result of the query
> thank's
> /kostas
>
>
>
>
>
> -----Αρχικό μήνυμα----- From: Paul Hartland
> Sent: Friday, March 14, 2014 1:40 PM
>
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] field cannot be updated...
>
> Your select in your first email doesn't match the fields in you last one,
> the underscores are missing and the id field is a different name in the
> people table, going by your fields your query below:
>
> SELECT peoplefilms.aa, peoplefilms.IDperson, people.sex
> FROM people INNER JOIN peoplefilms ON people.IDperson =
> peoplefilms.IDperson;
>
> should be:
>
> SELECT peoplefilms.aa_peoplefilms, peoplefilms.ID_person, people.sex
> FROM people INNER JOIN peoplefilms ON people.ID_people  =
> peoplefilmsID_person;
>
> Paul
>
>
> On 14 March 2014 09:39, Kostas Konstantinidis <kost36 at otenet.gr> wrote:
>
>  Hi Bill,
>> no, it's unique only in people and not in peoplefilms because the most
>> usual for a person is to appear more than one time in the same movie 
>> (e.g.
>> as director and as an actor too)
>>
>> the weird is that it works for another similar database (for short films)
>> in which the relationships are designed exactly the same field by field
>>
>> people
>> ID_people --> Auto increment, Primary, Not Null, Unique
>> person      --> Primary, Not Null, Unique
>> sex            --> a simple checkbox
>>
>> peoplefilms
>> aa_peoplefilms --> Auto increment, Primary, Not Null
>> ID_films            --> Primary, Not Null
>> ID_person         --> Primary, Not Null
>>
>> thank's for your response
>>
>> /kostas
>>
>>
>> -----Αρχικό μήνυμα----- From: Bill Benson
>> Sent: Friday, March 14, 2014 1:54 AM
>> To: Access Developers discussion and problem solving
>> Subject: Re: [AccessD] field cannot be updated...
>>
>> Can you make changes BEFORE you add a record? It smells like one of those
>> not update able query issue that happens when Access can't prove to 
>> itself
>> there could be one and only one record that reults from the join. Which
>> would be, if you joined on a key that is unique in both tables. Is
>> IDPerson
>> unique in both tables?
>>
>> If not, I would be surprised you could update existing records either,
>> whether the field be aa or sex.
>> On Mar 13, 2014 7:26 PM, "Kostas Konstantinidis" <kost36 at otenet.gr>
>> wrote:
>>
>>  Hi,
>>
>>> I use a subform with a record source:
>>>
>>> SELECT peoplefilms.aa, peoplefilms.IDperson, people.sex
>>> FROM people INNER JOIN peoplefilms ON people.IDperson =
>>> peoplefilms.IDperson;
>>>
>>> on peoplefilms.IDperson there is also a "not in list" event which works
>>> adding any new record on people
>>>
>>> the problem is that after a "not in list"... I can't make any change on
>>> people.sex
>>> have tried refresh or requery but nothing changes
>>>
>>>
>>> thank's a lot
>>>
>>> /kostas
>>> --
>>> AccessD mailing list
>>> AccessD at databaseadvisors.com
>>> http://databaseadvisors.com/mailman/listinfo/accessd
>>> Website: http://www.databaseadvisors.com
>>>
>>>  --
>>>
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>> --
>> AccessD mailing list
>> AccessD at databaseadvisors.com
>> http://databaseadvisors.com/mailman/listinfo/accessd
>> Website: http://www.databaseadvisors.com
>>
>>
>
>
> --
> Paul Hartland
> paul.hartland at googlemail.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

-- 
Paul Hartland
paul.hartland at googlemail.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com 