table relationship

[Jean-Paul De Winter]

Hi,

I have 2 tables both with an ID field.
Both tables are linked through this ID field.

Now, when I want to edit the value in table B I get a message saying the
table can't be changed because it's "read only"
I think this has to do with the type of relationship I use...
How to change this and in what should I cnage it?
Thanks

 

[Jeff Boyce]

Jean-Paul

What kind of an "ID" field? Are you using the Access Autonumber? If so,
the Autonumber in table1 bears NO relationship to the Autonumber in table2.

It's somewhat uncommon to have tables of data related "one-to-one", as
you've described. There are situations in which it is the appropriate data
structure, but you've not explained much about your data, so there's no way
to tell if what you've done will work well for you or cause you
headaches?<g>

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jean-Paul]

No autonumbering in table2... table1 yes.

I wrote:

sql_zwem = "SELECT leerlingen.*, Diverse.* FROM Diverse INNER JOIN
leerlingen ON Diverse.ID = leerlingen.ID WHERE Diverse.Zwemmen ='" & ab
& "' ;"
Set TB_zwem = db.OpenRecordset(sql_zwem)

TB_zwem.Edit
TB_zwem!zwem_niveau = TB_zwem!zwem_niveau
TB_zwem!zwem_niveau > zwem_max Then
TB_zwem!zwem_niveau = zwem_max
End If
If TB_zwem!IHP_Zwem = True Then
if TB_zwem!zwem_niveau > zwem_max Then TB_zwem!zwem_niveau = zwem_max
Else
If TB_zwem!zwem_niveau > IHPzwem_max Then TB_zwem!zwem_niveau = HPzwem_max
End If
Punten = TB_zwem!zwem_niveau
TB_zwem.Update

It's at the line
TB_Zwem.edit
I get the rrromessage saying something about "read only"

I think it has to do with the "inner join" but I don't know what

Thanks for your kind help
JP, Belgium

 

[Jeff Boyce]

I don't understand what your WHERE clause is referring to when you check
Diverse.Zwemmen ... is that a text-type field? If so, is your 'ab' a
variable or are you checking Diverse.Zwemmen for exactly that value (in
which case it is text and needs to be delimited with quotes)?

There's nothing in what you've described that makes me suspect an issue with
the relationship. Can you confirm that the database itself is not
"read-only"?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jean-Paul]

The sql_zwem statement returns following query:

SELECT leerlingen.*, Diverse.* FROM Diverse INNER JOIN leerlingen ON
Diverse.ID = leerlingen.ID WHERE Diverse.Zwemmen ='43' ;

where indeed '43' is a text-type string.
When I run this I get 3 records which is correct.

I recently split some tables so now I have to link them through that
"ID" field
Since then I get this error "causing be constant headache"<BG>

As far as I know the database isn't read-only

the headach is getting wors so all help is more then welcome!
Thanks
JP

 

[Jean-Paul]

problem not solved yet!!!!
ANYBODY?

The sql_zwem statement returns following query:

SELECT leerlingen.*, Diverse.* FROM Diverse INNER JOIN leerlingen ON
Diverse.ID = leerlingen.ID WHERE Diverse.Zwemmen ='43' ;

where indeed '43' is a text-type string.
When I run this I get 3 records which is correct.

I recently split some tables so now I have to link them through that
"ID" field
Since then I get this error "causing be constant headache"<BG>

As far as I know the database isn't read-only

the headach is getting wors so all help is more then welcome!
Thanks
JP

 

[Jeff Boyce]

Jean-Paul

Folks here are volunteering their time, so there may be someone who
understands your issue, someone who has dealt with it, or no one who's
looked into this newsgroup for a while.

If you have some urgency in resolving this, consider hiring someone ...
otherwise, there's no telling how long it might take ...

I've not run across this kind of behavior, so I can only offer a generic
approach - save a backup copy and use the Recompile function in VBA and the
Compact and Repair feature in Access.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jean-Paul]

let me try to explain this one thing (just give me a chance)...

When I run following query:
SELECT leerlingen.*, Diverse.* FROM Diverse INNER JOIN leerlingen ON
Diverse.ID = leerlingen.ID WHERE Diverse.Zwemmen ='43'

I get 3 records which is correct

I have always noticed that, when you get records form linked tables, and
you go to one field in this recordset, you are not allowed to change
anything... without any code...
So, just running the sql from above and going to one field trying to
change it, it never works, you are not allowed to chnage any field...

See what I try to understand why and how to solve this?

Thanks

 

[John W. Vinson]

let me try to explain this one thing (just give me a chance)...

When I run following query:
SELECT leerlingen.*, Diverse.* FROM Diverse INNER JOIN leerlingen ON
Diverse.ID = leerlingen.ID WHERE Diverse.Zwemmen ='43'

I get 3 records which is correct

I have always noticed that, when you get records form linked tables, and
you go to one field in this recordset, you are not allowed to change
anything... without any code...
So, just running the sql from above and going to one field trying to
change it, it never works, you are not allowed to chnage any field...

See what I try to understand why and how to solve this?

I have two suggestions:

1. Use a Form based on leerigen with a Subform based on diverse, assuming that
leerigen is the "one" table and Diverse the "many" (reverse the forms if
Diverse is the "one"). This will let you update both tables.

2. If (for some strange reason) you want to update a two-table query, be sure
that the joining field is the Primary Key of the "one" side table, and that a
relationship with referential integrity enforced is defined between the
tables.