Can't enter text after join tables HELP

[JoanOC]

SELECT Clients.[FirstName Father], Clients.[ClientCaseID#] AS
[Clients_ClientCaseID#], NotesTable.[NotesID#], NotesTable.[ClientCaseID#] AS
[NotesTable_ClientCaseID#], NotesTable.[Notes Text]
FROM Clients INNER JOIN NotesTable ON Clients.[ClientCaseID#] =
NotesTable.[ClientCaseID#];
Everything works ok until I try to join the 2 tables then I cannot enter
data in the notes text memo type field.
I have tried every relationship combination.
Need this for law firm where I work. Want to put command button on main form
so attorneys can enter notes in this notes form
Please HELP.Thanks. Joan

 

[sally casler]

SELECT Clients.[FirstName Father], Clients.[ClientCaseID#] AS
[Clients_ClientCaseID#], NotesTable.[NotesID#], NotesTable.[ClientCaseID#] AS
[NotesTable_ClientCaseID#], NotesTable.[Notes Text]
FROM Clients INNER JOIN NotesTable ON Clients.[ClientCaseID#] =
NotesTable.[ClientCaseID#];
Everything works ok until I try to join the 2 tables then I cannot enter
data in the notes text memo type field.
I have tried every relationship combination.
Need this for law firm where I work. Want to put command button on main form
so attorneys can enter notes in this notes form
Please HELP.Thanks. Joan

 

[John Vinson]

SELECT Clients.[FirstName Father], Clients.[ClientCaseID#] AS
[Clients_ClientCaseID#], NotesTable.[NotesID#], NotesTable.[ClientCaseID#] AS
[NotesTable_ClientCaseID#], NotesTable.[Notes Text]
FROM Clients INNER JOIN NotesTable ON Clients.[ClientCaseID#] =
NotesTable.[ClientCaseID#];
Everything works ok until I try to join the 2 tables then I cannot enter
data in the notes text memo type field.
I have tried every relationship combination.
Need this for law firm where I work. Want to put command button on main form
so attorneys can enter notes in this notes form
Please HELP.Thanks. Joan

The simplest way to do this would be to, instead of using a Query, use
a Form based on [Clients] with a Subform based on [NotesTable]. Use
the ClientCaseID# as the master/child link field (you don't even need
to display it on either form, unless you wish to do so).

Is ClientsCaseID# the Primary Key of the Clents table? The query above
should be updateable if it is; if it isn't - perhaps it should be!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[JoanOC]

Thanks for input. I will try as suggested.
No, ClientCaseID# is not the primary key. Because I sometime have multi
party cases involving law suits, I need to use the same record# for each
party, but each needs its own record for the individual attorneys and
insurance companies.
So, the ClientCaseID# can't be unique.
I just checked:Cases is my primary table and I don't have any primary key.
Perhaps I should. All my other tables have an auto recID#.
I have over 200 cases. Would it be a problem to create a field called recid#
as a primary key?
Again, thanks for input. Joan

SELECT Clients.[FirstName Father], Clients.[ClientCaseID#] AS
[Clients_ClientCaseID#], NotesTable.[NotesID#], NotesTable.[ClientCaseID#] AS
[NotesTable_ClientCaseID#], NotesTable.[Notes Text]
FROM Clients INNER JOIN NotesTable ON Clients.[ClientCaseID#] =
NotesTable.[ClientCaseID#];
Everything works ok until I try to join the 2 tables then I cannot enter
data in the notes text memo type field.
I have tried every relationship combination.
Need this for law firm where I work. Want to put command button on main form
so attorneys can enter notes in this notes form
Please HELP.Thanks. Joan

The simplest way to do this would be to, instead of using a Query, use
a Form based on [Clients] with a Subform based on [NotesTable]. Use
the ClientCaseID# as the master/child link field (you don't even need
to display it on either form, unless you wish to do so).

Is ClientsCaseID# the Primary Key of the Clents table? The query above
should be updateable if it is; if it isn't - perhaps it should be!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[JoanOC]

CORRECTION:

Thanks for input. I will try as suggested.
No, ClientCaseID# is not the primary key. Because I sometime have multi
party cases involving law suits, I need to use the same record# for each
party, but each needs its own record for the individual attorneys and
insurance companies.
So, the ClientCaseID# can't be unique.
I just checked:Cases is my primary table and I don't have any primary key.
Perhaps I should. All my other tables have an auto recID#.
I have over 200 cases. Would it be a problem to create a field called recid#
as a primary key?
Again, thanks for input. Joan

SELECT Clients.[FirstName Father], Clients.[ClientCaseID#] AS
[Clients_ClientCaseID#], NotesTable.[NotesID#], NotesTable.[ClientCaseID#] AS
[NotesTable_ClientCaseID#], NotesTable.[Notes Text]
FROM Clients INNER JOIN NotesTable ON Clients.[ClientCaseID#] =
NotesTable.[ClientCaseID#];
Everything works ok until I try to join the 2 tables then I cannot enter
data in the notes text memo type field.
I have tried every relationship combination.
Need this for law firm where I work. Want to put command button on main form
so attorneys can enter notes in this notes form
Please HELP.Thanks. Joan

The simplest way to do this would be to, instead of using a Query, use
a Form based on [Clients] with a Subform based on [NotesTable]. Use
the ClientCaseID# as the master/child link field (you don't even need
to display it on either form, unless you wish to do so).

Is ClientsCaseID# the Primary Key of the Clents table? The query above
should be updateable if it is; if it isn't - perhaps it should be!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[JoanOC]

CORRECTION: I work too jobs using Access in both. I meant to write that my
primary table is Clients not Cases. Sorry.
Joan

Thanks for input. I will try as suggested.
No, ClientCaseID# is not the primary key. Because I sometime have multi
party cases involving law suits, I need to use the same record# for each
party, but each needs its own record for the individual attorneys and
insurance companies.
So, the ClientCaseID# can't be unique.
I just checked:Cases is my primary table and I don't have any primary key.
Perhaps I should. All my other tables have an auto recID#.
I have over 200 cases. Would it be a problem to create a field called recid#
as a primary key?
Again, thanks for input. Joan

SELECT Clients.[FirstName Father], Clients.[ClientCaseID#] AS
[Clients_ClientCaseID#], NotesTable.[NotesID#], NotesTable.[ClientCaseID#] AS
[NotesTable_ClientCaseID#], NotesTable.[Notes Text]
FROM Clients INNER JOIN NotesTable ON Clients.[ClientCaseID#] =
NotesTable.[ClientCaseID#];
Everything works ok until I try to join the 2 tables then I cannot enter
data in the notes text memo type field.
I have tried every relationship combination.
Need this for law firm where I work. Want to put command button on main form
so attorneys can enter notes in this notes form
Please HELP.Thanks. Joan

The simplest way to do this would be to, instead of using a Query, use
a Form based on [Clients] with a Subform based on [NotesTable]. Use
the ClientCaseID# as the master/child link field (you don't even need
to display it on either form, unless you wish to do so).

Is ClientsCaseID# the Primary Key of the Clents table? The query above
should be updateable if it is; if it isn't - perhaps it should be!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

I just checked:Cases is my primary table and I don't have any primary key.
Perhaps I should.

There's no "perhaps" about it. Every table - EVERY TABLE! - should
have a Primary Key.

In this case, if there are multiple records in this table with the
same value of ClientCaseID#, and you have one or more records with
Notes for that ClientCaseID# - HOW can you link the note to the
correct record? Since the link is not unique, *you cannot*. You have
notes but no way of knowing which ClientCase record they belong to.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[JoanOC]

I worked so long on this I gave up.
I did link the notes by doing what you suggested:Making it a subform in my
main form.
Re primary id: I couldn't get the proper cases to join with the correct
invoice.
I used auto# and then tried #. I tried every choice re joining.
Nothing worked.
I tried to link by caseid#. Still no luck.
Could this be because there already is data in both tables?

 

[G. Vaught]

If you use an autonumber (long) as a primary key in one table and use that
same fieldname in another table, the other table must be a datatype of
'number', which is also long.

Remember that primary keys (one side) that relates to the foreign key (many
side) must match in size, datatype and preferably the same field name. If
you use the same naming convention, Access will automatically make the link
for you. If you use a different name, you have to make the link manually.

Also, once you have the relationship joined correctly between the two tables
under the relationship window, you can then create a main form/subform. If
you create this through the form wizard and create it correctly, Access will
do all the linking for you. If you get an error here, it means your many
side has a record that the one side does not have. Once you fix this
problem, they should link properly. You also have to make sure that when
setting the relationship that you check "Enforce Referential Integrity",
otherwise you loose the benefit.

First, move all the fields from the main table into the box. Then select the
second table and move all the fields over into the box. Then proceed through
the wizard. When you get to the part about Subform or Linked form, choose
Subform. Continue through the wizard.