Adding rows in subform Access 2000-2003

  • Thread starter Thread starter nikos dimitrakas
  • Start date Start date
N

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
 
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
Click to expand...
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




.
Click to expand...
 
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 said:
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
Click to expand...
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




.
Click to expand...
Click to expand...
 
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
Click to expand...
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
Click to expand...
(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
Click to expand...
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
Click to expand...
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
Click to expand...
problems
that should be
avoided? Normally it is enough to just write for
Click to expand...
example
[Date] in order to
instruct Access that this is not the reserved word
Click to expand...
Date,
but a column or
table name. This didn't work in the case with the form.

Thanks in advance
/nikos




.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top