Adding a Record to a Table in a Sub-Form

[Don]

The database I am working on has an employee information form on which there
is a subform which lists education. The subform is linked using the
employee ID and used tblEducation as its source. In this configuration, the
subform would always show a blank record in which the user could enter new
education data.

Now, I added a Me.RecordSource which uses and SQL string. When I tested the
form, I noticed the blank record was gone. It then dawned on me that the
subform was no longer directly linked to the underlying table
(tblEducation). Before I go off to solve this issue, I would like to get
confirmation of a couple things:

1) Is it safe to assume that the blank/new record will not be in a subform
if the subform is not directly linked to it's underlying table?

2) Edits made in the subform will not be reflected in the underlying table
as the record source for the subform is actaully an SQL query?


Now for the design question ........

If I want to continue to use the SQL string (there are some sorting and
table linking going on), is the best design path for adding and editing
records to have pop-up forms with fields to fill in or edit respectively?
It seems like the only option to me, but I figured some of you advanced
Access users might have some comments and ideas.

Thanks!

Don

 

[Steve Schapel]

Don,

...
1) Is it safe to assume that the blank/new record will not be in a subform
if the subform is not directly linked to it's underlying table?

No, this is not correct. It depends on whether the Record Source of the
subform is updateable, and it also depends on the seting of the Allow
Additions property of the form that you use as the subform. You can
certainly base the subform on a Query or a SQL statement.

2) Edits made in the subform will not be reflected in the underlying table
as the record source for the subform is actaully an SQL query?

No, this is not correct.

If I want to continue to use the SQL string (there are some sorting and
table linking going on), is the best design path for adding and editing
records to have pop-up forms with fields to fill in or edit respectively?

No, this is not correct.

Maybe you could post back with the SQL that you are trying to use as the
Record Source of the subform, and also indicate the relationship between
the main form and the subform, and someone may be able to advise more
specifically. At this stage, I would really suspect that you are trying
to use a non-updateable query. To test this, make a query based on your
SQL string, open the resultant datasheet, and see if you can edit
existing data or add new records there.

 

[Don]

Don,


No, this is not correct. It depends on whether the Record Source of the
subform is updateable, and it also depends on the seting of the Allow
Additions property of the form that you use as the subform. You can
certainly base the subform on a Query or a SQL statement.


No, this is not correct.
respectively?

No, this is not correct.

Maybe you could post back with the SQL that you are trying to use as the
Record Source of the subform, and also indicate the relationship between
the main form and the subform, and someone may be able to advise more
specifically. At this stage, I would really suspect that you are trying
to use a non-updateable query. To test this, make a query based on your
SQL string, open the resultant datasheet, and see if you can edit
existing data or add new records there.

Steve,

Your comments are encouraging! However, I am not 100% sure how to proceed.
Here is the SQL I am using in the subform:

' Define record source for sub-form to use. Linked on LinkID.

'-------------------------------------------------------------------------
strSQL = "SELECT P.*, R.Rank, R.Abbreviation " _
& "FROM tblEducation AS P LEFT JOIN tlkpEducationDegrees AS R " _
& "ON P.Degree = R.Abbreviation " _
& "ORDER BY Rank DESC, GraduationDate DESC"

Me.RecordSource = strSQL


Among the fields in tblEducation there is a field called Employee_ID. That
is the field the main form and the Education subform get linked on. From a
"read-only" perspective, the sub form is working exactly as I would expect
in terms of what is displayed and the order it is displayed.

The "Allow Additions" property is set to true. However, I'm not sure how
one could set the SQL to be updatable. I tried making a regular query using
the above SQL and could not edit the fields. Nor could I find anything to
make the query updatable. I think my problem is that the query is drawing
from two tables to make a composite data source for the subform.

Any ideas will be greatly aprpeciated!

Thanks!

Don

 

[Steve Schapel]

Don,

Ok, thanks for the further explanation.

Updateability of a query is not something you can switch on or off.
There are a number of conditions which determine whether a query is
updateable or not. As I said before, the fact that your record source
includes more than one table is not a problem in itself. In your case,
I haven't tested this, but I would suspect that the Abbreviation field
in the tlkpEducationDegrees table needs to be unique for the query to be
updateable. Can you check this in table design, and set its Indexed
property to No Duplicates? If that doesn't fix it, we'll consider further!

 

[Don]

Steve,

Did as you suggested (and a little more):
1) made field Abbreviation a required field
2) require non-zero length entry
3) made the field indexed with duplicates not allowed

Tried the form and it works!

Thanks for the guidance!!

Don