Subform Issue: Datasheet fields display #Deleted upon subform focus

[jeffcunn]

Hi All,

In short, here is the problem: When entering a datasheet subform (a
subform three levels deep), all the fields in the datasheet appear as
#Deleted. However, right-clicking a field in the datasheet and
selecting "Remove Filter/Sort" brings the data back as normal. I have
not deleted any records before or during the form load and the data
appears as normal when looking at the source tables. Also, when
opening the form that is the next level up (i.e., the datasheet now is
only two levels deep), the fields appear normally.

Here is my setup:

Tables: I do not have set relationships in my tables as most of the
data comes from external sources and is loaded in. Most of the tables
do have a primary/composite key that is built off of text data
(alphanumeric codes). However, the largest table in the database, EE
Data, does not have a primary key because there is repeating data and
because the data isn't entirely clean when loaded (in fact, the error
described above occurs during one of the data validation/cleaning
steps).

Forms: I have a main form ("frmMainMenu") that is unbound and is
merely a container for many subforms (between 15-20 subforms). Since
the main form is unbound, there are no Master/Child relationships
between the main form and any of the subforms. The main form also has
navigation links driven by labels to hide/unhide the subforms (e.g.,
subform.Visible = True). I had to embed a hidden text box into the
main form to allow the subforms to hide/unhide; otherwise, I received
an error that the subforms could not give up/receive the focus.
Whenever I hide or unhide a subform, I set the focus to the hidden
text box.

Most of the subforms are one level deep (i.e., Main form --> subform),
but some of three and four levels deep as in the problem described
above (i.e., frmMainMenu --> frmFixStep2 -->
frmFixStep2_subfrm_Contracts). Again, there are no master/child
relationships between the subforms as the data only resides in the
lowest level subform. Many of the subforms are for user interface
purposes like the main form.

More detail on the problem: The subform in question
("frmFixStep2_subfrm_Contracts") is embedded in another subform
("frmFixStep2"). Along with the contracts subform, frmFixStep2 has
another child subform, "frmFixStep2_subfrm_Employees". "frmFixStep2"
includes radio (option) buttons to switch back and forth between the
contracts and employees subforms. The employees subform (which is a
datasheet like the contracts subform) displays properly, but the
contracts subform does not. This could be because the query
underlining the contracts datasheet uses an outer join to another
table, while the query underlining the employees subform uses an inner
join. I need to use the outer join on the contracts subform to show
records that are missing from one of the tables.

I have tried adding requerying and refreshing code to the contracts
subform to no avail. The only way to get the "#Deleted" out of the
fields is to remove the filter as described above. I'm at a loss
here.

Also, if anyone has suggestions on where my design is going wrong or
have tips for anything else, I'd love to hear them.

Thanks,

Jeff

 

[Tester]

Jeff,

Almost always, the reason you see the #Delete errors is due the Access can
not uniquely identify a record. Can you, at least temporarily add an
auto-number field to the table, make it the primary key. Then clean up dups
and assign a "True" primary key later? That should solve your problem.

 

[jeffcunn]

Tester,

I added autonum fields as primary keys to both tables involved in the
query behind the contracts form. Unfortunately, it didn't help at
all. Any other suggestions?

Thanks,

Jeff

 

[jeffcunn]

Tester,

I think I found the solution...during some previous research into the
situation, someone brought up the idea of using the Idle method to
allow the database to finish some processing tasks in the background.
Seeing that nothing else has worked, I added the following line of
code before opening the subform in question:

DBEngine.Idle [dbRefreshCache]
Forms!frmMainMenu!frmFixStep2!frmFixStep2_subfrm_Contracts.Requery

I'm a bit nervous that I didn't find the root of the problem, but at
least my subform works correctly.

Thanks for replying earlier,

Jeff

 

[Marshall Barton]

I think I found the solution...during some previous research into the
situation, someone brought up the idea of using the Idle method to
allow the database to finish some processing tasks in the background.
Seeing that nothing else has worked, I added the following line of
code before opening the subform in question:

DBEngine.Idle [dbRefreshCache]
Forms!frmMainMenu!frmFixStep2!frmFixStep2_subfrm_Contracts.Requery

Thank you for posting the final resolution to this subtle
problem. It's going in my Keepers folder.