related record needed in parent table

[Jonathan Brown]

I run into this problem all the time and I don't know what
I do to fix it but after fussing with it for a while
sometimes it goes away. I'll like someone to explain this
to me in a little better detail.

I've got a table that is keeping track of personal info.
This table already has a number of records in it. I've
created another table which keeps track of Visa
information. I've set up a one to many relationship (my
visa table being on the Many side) with the Personal
table. I've made it a habit to use referential integrity
and cascade updates and cascade deletes. But the problem
is I can't add any records to my visa table. I start
entering information for a new record and when I move off
that record I get an error message saying "You can not add
or change a record because a related record is required in
the table 'tblPersonal'".

If someone could shed some light on this I'd appreciate it.
thanks.

 

[John Vinson]

But the problem
is I can't add any records to my visa table. I start
entering information for a new record and when I move off
that record I get an error message saying "You can not add
or change a record because a related record is required in
the table 'tblPersonal'".

The simplest way to manage this is to not use table datasheets for
data entry; use a Form instead. If you have a Form based on the
personal data table, and a Subform based on the visa table, using the
linking field as the master/child field, Access will maintain the link
for you automatically.

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

 

[Jonathan Brown]

I've come to find though that if I can't enter any records
into the table directly then a subform based on the table
isn't going to be able to enter records either.

 

[John Vinson]

I've come to find though that if I can't enter any records
into the table directly then a subform based on the table
isn't going to be able to enter records either.

If the subform is properly set up - i.e. with the Master Link Field
being the primary key of the mainform's table, and the Child Link
Field being the foreign key linked field in the subform's table - this
problem should not arise. What are the Recordsource properties of the
main and subforms? What are the Master and Child Link Fields?


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

 

[Jonathan Brown]

My subform is linked with my master form using the Emp#
field. So my master form has the Emp# field and my
subform also has an Emp# field. It works as long as I
leave the Emp# field in my subform. But that seems a
little redundant to me. So I'd like to remove the Emp#
field from my subform, but still have the two linked to
that field.

Strangely enough, I've been able to successfully create
some other subforms, linked by the same field and have the
emp# field excluded. The problem only seems to occur when
I've created a new table, set up the relationship, and
then built the subform off that table. I don't know if
I've explained myself well enough but that's what going
on, it's kinda weird. I've sort of resolved to just leave
the Emp# field in the subform but lock it so that my users
don't accidentally change it.

 

[John Vinson]

My subform is linked with my master form using the Emp#
field. So my master form has the Emp# field and my
subform also has an Emp# field. It works as long as I
leave the Emp# field in my subform. But that seems a
little redundant to me. So I'd like to remove the Emp#
field from my subform, but still have the two linked to
that field.

The Emp# field must be in the subform's Recordsource - but there is no
need to display it. Simply don't put a textbox on the subform for it
(or, if you're using a datasheet, set its width to zero).

I prefer to use continuous forms rather than datasheets - it's easy
then, you simply don't include the Emp# as a textbox on the form.

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