access subform key field only intermittently populating subsubform

[Don Do]

Help

I built a form/subform/subsubform setup using the access forms
wizard. I have a table1 = parent, table2 = child, table3 =
(grandchild?). There will be multiple records in table2 that tie to
table1, and multiple records in table 3 that tie to table2. Both on
"1 to many" joins.

Each of the forms are bound to the fields in the respective tables.
The subform & subsubform are datasheet view. When I enter any data, I
do it through the main form only, which means I see all 3 forms on the
screen. I actually enter the data into all 3 forms [main, sub, &
subsub.]

The join between tables 1 & 2 is an autonumber index in table 1 and a
foreign number field in table2. This join seems to work fine. Also,
there is an autonumber primary index field in table 2 that joins to a
foreign number field in table 3. This join also seemed to behave as
expected for several weeks in testing.

The problem: Now when I add a record into this subsubform, sometimes
it works fine, but very often i do not get the number field in table 3
that would tie to the autonumberID field in table2.

I have deleted my provisional formatting and have added the autonumber
and foreign fields to each of the sub & subsub forms.

What have I done? Any help would be much appreciated.

Don

[Allen Browne]

Don, you say that both subforms are in datasheet view?'
If subform1 is in datasheet view, it cannot have a subform.
Therefore I have to assume that subform1 and subform2 are both sitting on
the main form. This may affect the way it picks up the value for its foreign
key field.

Suggestions.
1. Open Table3 in design view, and select the foreign key field.
If you see a zero beside the Default Value property, delete it.
Set the Required property to Yes for the foreign key field.
This will prevent Access saving the record when the key is not picked up,
and it will notify you which will help your debugging. (I assume you already
have a relationship between these 2 tables, with Referential Integrity
enforced.)

2. Open the main form in design view. Right-click the lower-level subform,
and choose Properties. On the Data tab of the Properties box, what do you
have for LinkMasterFields and LinkChildFields? If LinkMasterFields is an
expression such as:
[Forms].[MyMainForm].[Sub1].[Form].[ID]
then you might try changing it.
Add a text box to the main form, with properties like this:
Control Source [Sub1].[Form].[ID]
Format General Number
Then set LinkMasterFields to the name of this text box.

3. If none of this works, you can use the BeforeInsert event of the lower
level subform to either read and manually assign the foreign key field
value, or cancel the insert.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Don Do" <bi************@gmail.comwrote in message
news:11**********************@v45g2000cwv.googlegr oups.com...

Help

I built a form/subform/subsubform setup using the access forms
wizard. I have a table1 = parent, table2 = child, table3 =
(grandchild?). There will be multiple records in table2 that tie to
table1, and multiple records in table 3 that tie to table2. Both on
"1 to many" joins.

Each of the forms are bound to the fields in the respective tables.
The subform & subsubform are datasheet view. When I enter any data, I
do it through the main form only, which means I see all 3 forms on the
screen. I actually enter the data into all 3 forms [main, sub, &
subsub.]

The join between tables 1 & 2 is an autonumber index in table 1 and a
foreign number field in table2. This join seems to work fine. Also,
there is an autonumber primary index field in table 2 that joins to a
foreign number field in table 3. This join also seemed to behave as
expected for several weeks in testing.

The problem: Now when I add a record into this subsubform, sometimes
it works fine, but very often i do not get the number field in table 3
that would tie to the autonumberID field in table2.

I have deleted my provisional formatting and have added the autonumber
and foreign fields to each of the sub & subsub forms.

What have I done? Any help would be much appreciated.

Don

[Marshall Barton]

Don Do wrote:

>I built a form/subform/subsubform setup using the access forms
wizard. I have a table1 = parent, table2 = child, table3 =
(grandchild?). There will be multiple records in table2 that tie to
table1, and multiple records in table 3 that tie to table2. Both on
"1 to many" joins.

Each of the forms are bound to the fields in the respective tables.
The subform & subsubform are datasheet view. When I enter any data, I
do it through the main form only, which means I see all 3 forms on the
screen. I actually enter the data into all 3 forms [main, sub, &
subsub.]

The join between tables 1 & 2 is an autonumber index in table 1 and a
foreign number field in table2. This join seems to work fine. Also,
there is an autonumber primary index field in table 2 that joins to a
foreign number field in table 3. This join also seemed to behave as
expected for several weeks in testing.

The problem: Now when I add a record into this subsubform, sometimes
it works fine, but very often i do not get the number field in table 3
that would tie to the autonumberID field in table2.

Since a datasheet (or continuous) form can not have a
subform, I assume that both subforms are on the main form.
In this case, the secret is to set the LinkMaster/Child
properties appropriately.

Add a text box (named txtLink) to the main form. Then add a
line of code to the subform's Current event:
Parent.txtLink = Me.pkfield

Set subsubform's Link Master Fields property to txtLink and
the Link Child Fields property to table3's foreign key.

--
Marsh

[Don Do]

Allen & Marsh,

Thanks for your posts. I did Allen's steps 1 & 2, and the file
settled down.

Problem now is that when I do a search on a mainform item, I find many
instances of the same record. I have to keep paging through the found
items to get to the second mainform record. It seems that my search
now finds a mainform record for each level 2 index number.

So what is the trick I'm missing now?

Thanks again.

Don

[Allen Browne]

So the main form's RecordSource is an INNER JOIN query? And so it contains
the main record multiple times if there are mulitple related matches?

If that is the case, you could change the main form's RecordSource to a
query that does not include the related table(s). Instead use a subquery in
the WHERE clause to choose the records that have a match in the related
table.

For details, see:
Subquery Basics: Filters and Searches
at:
http://allenbrowne.com/subquery-01.html#Search

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Don Do" <bi************@gmail.comwrote in message
news:11**********************@l75g2000hse.googlegr oups.com...

Allen & Marsh,

Thanks for your posts. I did Allen's steps 1 & 2, and the file
settled down.

Problem now is that when I do a search on a mainform item, I find many
instances of the same record. I have to keep paging through the found
items to get to the second mainform record. It seems that my search
now finds a mainform record for each level 2 index number.

So what is the trick I'm missing now?

Thanks again.

Don