can't create new record

[quaddawg via AccessMonster.com]

I cannot add a record through query datasheet view or a form. Here's the
setup:

I have two tables linked to a frontend. The relationship between the tables
is defined in the backend as one to many; referential integrity, cascade
update, and cascade delete checked. The tables are related by StudentID
field which is type autonumber in tblStudent and Number in tblTests.
TblTests has an autonumber primary key field TestID.

This design did not seem to cause me trouble in the past, but since I've
overhauled some things I'm having trouble. In the datasheet view of a simple
query having fields tblStudents.StudentID, tblStudents.LastName, tblTests.
TestID, tblTests.TestDate, if I try to add a new record (last name) , the
autonumber field generates a number for StudentID, but I can't enter a test
date and no autonumber is generated for TestID. On a form using this query,
I cannot add anything in any field belonging to tblTests.
Any ideas why I cannot create a new test record?

 

[Guest]

The best way to handle this is a form/subform combination. The parent table
(tblStudent) is in the form and your child records (tblTest) in the subform.
That way the primary key information (StudentID) is also entered into the
foreign key field of the child table if the form/subform are created by the
wizard correctly. Unless there is a matching StudentID in tblTest you can not
enter a record.

Other than that, you may have a corrupt database. Try a compact and repair
on a copy - not the original - of the database and see if that fixes the
problem.

 

[quaddawg via AccessMonster.com]

Wow, that's really unfortunate. I'd already tried it the way you mentioned
to no avail. Here goes my first experience with corruption, wish me luck.

The best way to handle this is a form/subform combination. The parent table
(tblStudent) is in the form and your child records (tblTest) in the subform.
That way the primary key information (StudentID) is also entered into the
foreign key field of the child table if the form/subform are created by the
wizard correctly. Unless there is a matching StudentID in tblTest you can not
enter a record.

Other than that, you may have a corrupt database. Try a compact and repair
on a copy - not the original - of the database and see if that fixes the
problem.

I cannot add a record through query datasheet view or a form. Here's the
setup:

[quoted text clipped - 13 lines]

I cannot add anything in any field belonging to tblTests.
Any ideas why I cannot create a new test record?

 

[Guest]

One generally does not / can not add a record to a Table via the Query - plus
also - in a Form's properties one can restrict edits and adds as well.

Can not say which is which just based on your description below.

I will hazard the guess that directly in the Table itself (not via the
Query) you can indeed add a record without problem.

 

[Naeem Azizian]

In the table Students, insert the tests table as a subdatasheet. that
way, you dont see test table rows in the same row as the students but
below the student table rows.
that's faster and you dont need to use query.

One generally does not / can not add a record to a Table via the Query - plus
also - in a Form's properties one can restrict edits and adds as well.

Can not say which is which just based on your description below.

I will hazard the guess that directly in the Table itself (not via the
Query) you can indeed add a record without problem.
--
NTC

I cannot add a record through query datasheet view or a form. Here's the
setup:

I have two tables linked to a frontend. The relationship between the tables
is defined in the backend as one to many; referential integrity, cascade
update, and cascade delete checked. The tables are related by StudentID
field which is type autonumber in tblStudent and Number in tblTests.
TblTests has an autonumber primary key field TestID.

This design did not seem to cause me trouble in the past, but since I've
overhauled some things I'm having trouble. In the datasheet view of a simple
query having fields tblStudents.StudentID, tblStudents.LastName, tblTests.
TestID, tblTests.TestDate, if I try to add a new record (last name) , the
autonumber field generates a number for StudentID, but I can't enter a test
date and no autonumber is generated for TestID. On a form using this query,
I cannot add anything in any field belonging to tblTests.
Any ideas why I cannot create a new test record?

 

[quaddawg via AccessMonster.com]

No dice unfortunately--I was working with the query in order to diagnose why
the form was not working (and edits and adds are not restricted). I can add
a record to the student table, but cannot add one to Tests (nor is one
automatically added).
I've exported the data to excel, reimported it and am starting over. So far,
so good.

One generally does not / can not add a record to a Table via the Query - plus
also - in a Form's properties one can restrict edits and adds as well.

Can not say which is which just based on your description below.

I will hazard the guess that directly in the Table itself (not via the
Query) you can indeed add a record without problem.

I cannot add a record through query datasheet view or a form. Here's the
setup:

[quoted text clipped - 13 lines]

I cannot add anything in any field belonging to tblTests.
Any ideas why I cannot create a new test record?