Adding rows in subform Access 2000-2003

[nikos dimitrakas]

I am working with Access 2003, but the problem persisted when I tried to do
the same with Access 2000:

I am creating a form with one field (the name of a composer) and I have a
subform with the composer's songs. The subform is based on a table Song that
has the name of a song and the name of the composer (as a foreign key)

Tables:
Composer(Name string PK)
Song(ID autonumber PK, Name string, Length number, Composer string)

Song.composer << Composer.Name

When I create a form for a composer with a subform for songs (linked
according to the foreign key relationship), I should be able to insert new
Song rows and the value of the foreign key should be automatically set to
the current value of the composer. In Access 2000 and 2003 the foreign key
seems to be filled in by Access with the name of the form (which of course
violates the referential integrity and doesn't work). I have looked around
in the properties of the form, subform, and other components and I have
compared with other forms that work and after hours I realized that it all
depends on the actual word "Name" that my column is called. The same problem
exists in Access 2000and 2003, so if it has been around for so many years
there must be a way to tell Access that this is a field name.
Is there a fix for this?
Is there a work-around?
Any chance of a Service Pack coming out soon that addresses this problem?

I know I can fix this by "just" changing the column "Name" in table Composer
to say "TheName", but there are 100 pages of text and diagrams that are
based on the model where the attribute is called "Name".

Are there any other words that can cause similar problems that should be
avoided? Normally it is enough to just write for example [Date] in order to
instruct Access that this is not the reserved word Date, but a column or
table name. This didn't work in the case with the form.

Thanks in advance
/nikos

 

[Chris]

In General you should avoid using any "reserved" words for
field or variable names.

In your case, putting [name] instead of just name should
fix your problem.

-----Original Message-----
I am working with Access 2003, but the problem persisted when I tried to do
the same with Access 2000:

I am creating a form with one field (the name of a composer) and I have a
subform with the composer's songs. The subform is based on a table Song that
has the name of a song and the name of the composer (as a foreign key)

Tables:
Composer(Name string PK)
Song(ID autonumber PK, Name string, Length number, Composer string)

Song.composer << Composer.Name

When I create a form for a composer with a subform for songs (linked
according to the foreign key relationship), I should be able to insert new
Song rows and the value of the foreign key should be automatically set to
the current value of the composer. In Access 2000 and 2003 the foreign key
seems to be filled in by Access with the name of the form (which of course
violates the referential integrity and doesn't work). I have looked around
in the properties of the form, subform, and other components and I have
compared with other forms that work and after hours I realized that it all
depends on the actual word "Name" that my column is called. The same problem
exists in Access 2000and 2003, so if it has been around for so many years
there must be a way to tell Access that this is a field name.
Is there a fix for this?
Is there a work-around?
Any chance of a Service Pack coming out soon that addresses this problem?

I know I can fix this by "just" changing the

column "Name" in table Composer

to say "TheName", but there are 100 pages of text and diagrams that are
based on the model where the attribute is called "Name".

Are there any other words that can cause similar problems that should be
avoided? Normally it is enough to just write for example [Date] in order to
instruct Access that this is not the reserved word Date, but a column or
table name. This didn't work in the case with the form.

Thanks in advance
/nikos




.

 

[nikos dimitrakas]

I already wrote in the original message that the [name] notation does not
work.

And actually the word "name" does not appear in the reserved words list in
Access Help.

Any other suggestions?

In General you should avoid using any "reserved" words for
field or variable names.

In your case, putting [name] instead of just name should
fix your problem.

-----Original Message-----
I am working with Access 2003, but the problem persisted when I tried to do
the same with Access 2000:

I am creating a form with one field (the name of a composer) and I have a
subform with the composer's songs. The subform is based on a table Song that
has the name of a song and the name of the composer (as a foreign key)

Tables:
Composer(Name string PK)
Song(ID autonumber PK, Name string, Length number, Composer string)

Song.composer << Composer.Name

When I create a form for a composer with a subform for songs (linked
according to the foreign key relationship), I should be able to insert new
Song rows and the value of the foreign key should be automatically set to
the current value of the composer. In Access 2000 and 2003 the foreign key
seems to be filled in by Access with the name of the form (which of course
violates the referential integrity and doesn't work). I have looked around
in the properties of the form, subform, and other components and I have
compared with other forms that work and after hours I realized that it all
depends on the actual word "Name" that my column is called. The same problem
exists in Access 2000and 2003, so if it has been around for so many years
there must be a way to tell Access that this is a field name.
Is there a fix for this?
Is there a work-around?
Any chance of a Service Pack coming out soon that addresses this problem?

I know I can fix this by "just" changing the

column "Name" in table Composer

to say "TheName", but there are 100 pages of text and diagrams that are
based on the model where the attribute is called "Name".

Are there any other words that can cause similar problems that should be
avoided? Normally it is enough to just write for example [Date] in order to
instruct Access that this is not the reserved word Date, but a column or
table name. This didn't work in the case with the form.

Thanks in advance
/nikos




.

 

[Chris]

Sorry, missed the [Date] reference.
Could it be that one of the control names on your form has
the name "Name"?
Or maybe its just one of the Access things.
I know you can refer to the form height property by using
the word "height" ("Me." being implied). Perhaps its the
same with "Name"

-----Original Message-----
I already wrote in the original message that the [name] notation does not
work.

And actually the word "name" does not appear in the reserved words list in
Access Help.

Any other suggestions?

"Chris" <anonymous.chris@mercury- projects.co.nz.discussions.microsoft.com>
wrote in message news:15e601c3df8d$b5bbc370 [email protected]...

In General you should avoid using any "reserved" words for
field or variable names.

In your case, putting [name] instead of just name should
fix your problem.

-----Original Message-----
I am working with Access 2003, but the problem

persisted
when I tried to do

the same with Access 2000:

I am creating a form with one field (the name of a composer) and I have a
subform with the composer's songs. The subform is based on a table Song that
has the name of a song and the name of the composer

(as a
foreign key)

Tables:
Composer(Name string PK)
Song(ID autonumber PK, Name string, Length number, Composer string)

Song.composer << Composer.Name

When I create a form for a composer with a subform for songs (linked
according to the foreign key relationship), I should be able to insert new
Song rows and the value of the foreign key should be automatically set to
the current value of the composer. In Access 2000 and 2003 the foreign key
seems to be filled in by Access with the name of the

form
(which of course

violates the referential integrity and doesn't work). I have looked around
in the properties of the form, subform, and other components and I have
compared with other forms that work and after hours I realized that it all
depends on the actual word "Name" that my column is called. The same problem
exists in Access 2000and 2003, so if it has been around for so many years
there must be a way to tell Access that this is a field name.
Is there a fix for this?
Is there a work-around?
Any chance of a Service Pack coming out soon that addresses this problem?

I know I can fix this by "just" changing the

column "Name" in table Composer

to say "TheName", but there are 100 pages of text and diagrams that are
based on the model where the attribute is called "Name".

Are there any other words that can cause similar

problems
that should be

avoided? Normally it is enough to just write for

example
[Date] in order to

instruct Access that this is not the reserved word

Date,
but a column or

table name. This didn't work in the case with the form.

Thanks in advance
/nikos




.

.