New record does not appear in form

[Guest]

I have a parent form (Default View: Single Form, Data Entry: No, RecordSet
Type: Dynaset, Record Locks: None) based on a table that include a few simple
fields and a unique id (the primary key) and I have several subforms, each
including a few based on separate tables with simple unique fields and the
same unique id is passed to each subform from the parent form (from the
parent/child link settings). My problem is, now that I have a few hundred
unique entries into my dataset (478 to be exact), whenever I create a new
record, it does not display the new record in the form. The new record is
present in the underlying table and is present in the form immediately after
I create the record with the form, but if I close the form and reopen it or
if I perform a sort or filter on the form, the new record disappears from the
form view (while still remaining in the underlying table).

I know this is still a little vague, so I am hoping to get a few general
explanations for what causes this to occur and then dig a little deeper
(instead of wandering aimlessly) and possibly repost with an updated and more
appropriately detailed question.

 

[Dirk Goldgar]

I have a parent form (Default View: Single Form, Data Entry: No,
RecordSet Type: Dynaset, Record Locks: None) based on a table that
include a few simple fields and a unique id (the primary key) and I
have several subforms, each including a few based on separate tables
with simple unique fields and the same unique id is passed to each
subform from the parent form (from the parent/child link settings).
My problem is, now that I have a few hundred unique entries into my
dataset (478 to be exact), whenever I create a new record, it does
not display the new record in the form. The new record is present in
the underlying table and is present in the form immediately after I
create the record with the form, but if I close the form and reopen
it or if I perform a sort or filter on the form, the new record
disappears from the form view (while still remaining in the
underlying table).

I know this is still a little vague, so I am hoping to get a few
general explanations for what causes this to occur and then dig a
little deeper (instead of wandering aimlessly) and possibly repost
with an updated and more appropriately detailed question.

Open the form in design view and check its RecordSource propery. Is it
just a single table name, or is it a SQL statement or the name of a
stored query? If either of the latter two, what is the SQL of the
query?

 

[Guest]

Open the form in design view and check its RecordSource propery. Is it
just a single table name, or is it a SQL statement or the name of a
stored query? If either of the latter two, what is the SQL of the
query?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Mr. Goldgar,

I believe you were the one who responded to my previous thread posted on
08/21/2006 titled "Static Record Set" causing an inability to view other
records in the form due to my use of VB code to calculate and store a net
value in a field from two other fields. Your explanation gave me an
excellent understanding of what was occurring and I was able to correct the
problem; the correction I implemented was to just remove the "Private Sub
Form_AfterUpdate()" and "Private Sub Form_Current()" events. However, the
currently described problem immediately occurred upon correcting that
previous issue, since that problem was in a subform to the parent form
described in this current problem.

Now, to answer your question, the following line is what is present in the
RecordSource property of the parent form:

SELECT [Grade Info].ComicID, [Grade Info].GradeID, [Grade Info].Grade,
[Grade Info].[Label Type], [Grade Info].[Page Quality], [Issue Info].[Comic
Title], [Issue Info].Issue, [Issue Info].[Issue Designator] FROM [Issue Info]
INNER JOIN ([Grade Info] INNER JOIN [Scan (Graded)] ON [Grade
Info].GradeID=[Scan (Graded)].GradeID) ON [Issue Info].ComicID=[Grade
Info].ComicID ORDER BY [Issue Info].[Comic Title], [Issue Info].Issue, [Issue
Info].[Issue Designator];

 

[Dirk Goldgar]

Mr. Goldgar,

I believe you were the one who responded to my previous thread posted
on 08/21/2006 titled "Static Record Set" causing an inability to view
other records in the form due to my use of VB code to calculate and
store a net value in a field from two other fields. Your explanation
gave me an excellent understanding of what was occurring and I was
able to correct the problem; the correction I implemented was to just
remove the "Private Sub Form_AfterUpdate()" and "Private Sub
Form_Current()" events. However, the currently described problem
immediately occurred upon correcting that previous issue, since that
problem was in a subform to the parent form described in this current
problem.

Now, to answer your question, the following line is what is present
in the RecordSource property of the parent form:

SELECT [Grade Info].ComicID, [Grade Info].GradeID, [Grade Info].Grade,
[Grade Info].[Label Type], [Grade Info].[Page Quality], [Issue
Info].[Comic Title], [Issue Info].Issue, [Issue Info].[Issue
Designator] FROM [Issue Info] INNER JOIN ([Grade Info] INNER JOIN
[Scan (Graded)] ON [Grade Info].GradeID=[Scan (Graded)].GradeID) ON
[Issue Info].ComicID=[Grade Info].ComicID ORDER BY [Issue
Info].[Comic Title], [Issue Info].Issue, [Issue Info].[Issue
Designator];

I'm guessing that's your problem. Your main form is based on a query
that joins three tables, [Grade Info], [Issue Info], and [Scan
(Graded)]. The joins are all "inner joins", meaning that the query will
return only records that have a match in all three tables. I'm not sure
which of these tables is the primary table, but I'm pretty sure it's not
[Scan (Graded)], because there are no fields from [Scan (Graded)] in the
list of selected fields.

My guess is that two of these three tables are represented by subforms
on your main form. As such, they should not be included in the main
form's recordsource. Although there can certainly be exceptions, in a
main form/subform arrangement the main form will typically be based on
one table (or a simple query of one table), with each of the subforms
based on another table (or simple query).

So my first suggestion is that you remove the secondary tables from the
main form's recordsource, and see if that fixes your problem while still
giving you what you want from the form. If you included the secondary
tables only because you thought you had to in order to have them on
subforms, that should be all you need to do.

If you do need info from two tables to appear on the main form, use an
outer join(LEFT or RIGHT JOIN) to connect the tables so that all the
records from the primary table are returned, and only the matching
records from the secondary table are returned. If that doesn't give you
the set of records you want, reverse the direction of the join.