Requery Sub after update of another sub both on an unbound form

[Chris Belcher]

I have a unbound form with 2 subforms. It looks something like this

_______________ _______________ _______________ __
| [Main} (Some Controls) |
| |
|______________ _______________ _______________ _|
| [Sub 1] | [Sub 2] |
| | |
| AIKey Other Data | Detail |
| AIKey Other Data | Detail |
| AIKey Other Data | Detail |
| AIKey Other Data | Detail |
| AIKey Other Data | Detail |

| | |
| | |
| | |
| | |
| | |
| | |
| | |
|______________ _______________ __|____________ _|

BY setting the Control Source Property to a SQL Statement I'm able to
get the correct values in each Subform. Sub 2, when it loads, looks at
the AIKey value and pulls the correct detail. The initial display
operates exactly like I want. The problem is when I go to the next
record in [Sub1], [Sub 2] doesn't update. If I manually select a field
in [sub 2] and do a Shift+F9 the field updates. Ok, I think, all I have
to do is a requery of [Sub 2]. I can't for the life of me figure out
how to do that through a Macro and any attempt in VBA seems to meet the
same fate.

Before someone asks... This form is read only. It is used to get an
overview of the status of "action items" .

Any Help would be appreciated!
Chris

[James Fortune]

Chris Belcher <ch******@bells outh.net> wrote in message news:<9r******* *********@bigne ws1.bellsouth.n et>...

I have a unbound form with 2 subforms. It looks something like this

_______________ _______________ _______________ __
| [Main} (Some Controls) |
| |
|______________ _______________ _______________ _|
| [Sub 1] | [Sub 2] |
| | |
| AIKey Other Data | Detail |
| AIKey Other Data | Detail |
| AIKey Other Data | Detail |
| AIKey Other Data | Detail |
| AIKey Other Data | Detail |

| | |
| | |
| | |
| | |
| | |
| | |
| | |
|______________ _______________ __|____________ _|

BY setting the Control Source Property to a SQL Statement I'm able to
get the correct values in each Subform. Sub 2, when it loads, looks at
the AIKey value and pulls the correct detail. The initial display
operates exactly like I want. The problem is when I go to the next
record in [Sub1], [Sub 2] doesn't update. If I manually select a field
in [sub 2] and do a Shift+F9 the field updates. Ok, I think, all I have
to do is a requery of [Sub 2]. I can't for the life of me figure out
how to do that through a Macro and any attempt in VBA seems to meet the
same fate.

Before someone asks... This form is read only. It is used to get an
overview of the status of "action items" .

Any Help would be appreciated!
Chris

In the Form_Current() code of subform1 I have:

Call Form_frmMain.Re querySubform2(C Str(Me.IdIDO))

Then in the frmMain code:

Public Sub RequerySubform2 (strIdIDO As String)
'Set visible = false and make visible to keep subform OnCurrent from
running when the main form first opens
If Subform2.Visibl e = False Then
Subform2.Visibl e = True
Exit Sub
End If
Subform2.Form.R ecordSource = "SELECT * FROM tblIDOItems WHERE IdIDO =
" & strIdIDO & " ORDER BY IdItem;"
Subform2.Form.R efresh
Subform2.Form.R epaint 'this line may not be needed
If Subform1.Form.M aterialProcesse d = True Then
Subform2.Form!P rocess.Locked = True
Else
Subform2.Form!P rocess.Locked = False
End If
End Sub

I don't remember why I did it this way, but it worked.

James A. Fortune