Open a sub form too last viewed record

[Hardworker]

I have a form with three nested subforms. I have used the Microsoft procedure
KB190515 "How to Open a Form to the Last Viewed Record" to open the main
form., but the subforms open to the first record not the last viewed record.
I have repeated the above MS KB procedure in the first sub form but it does
not work.
How can I modify the procedure so that the sub forms also open to the last
viewed record?

 

[Allen Browne]

This is probably a timing problem. Access loads the subforms before the main
form. Their Load event is therefore likely to run first (before they have
data), and then when the main form's Load event runs, the subforms are not
at the right place.

To solve this, create 2 procedures in a standard module where you can call
them from both your form and subform.

1. In the code window, create a new module (Insert on Module menu.)

2. Paste Micosoft's 2 procedures there.

3. Change the lines:
Private Sub Form_Unload(Cancel As Integer)
Private Sub Form_Load()
to:
Public Function FormLoad(frm As Form, strVarName As String)
Public Function FormUnload(frm As Form, strVarName As String)
respectively. In both cases, change:
End Sub
to:
End Function

4. In the 2 procedures, change each instance of the word:
Me
to:
frm

5. In each of your forms and subforms, change the unload code to this:
Private Sub Form_Unload(Cancel As Integer)
Call FormLoad(Me, "xxx")
End Sub
where xxx represents the name of the variable to store in your table for
that form (such as "CustomerIDLast".)

6. Remove the load code from the subforms.
Set the main form's load code to this:
Private Sub Form_Load()
Call FormLoad(Me, "xxx")
Call FormLoad(Me.[Sub1].Form, "yyy")
Call FormLoad(Me.[Sub2].Form, "zzz")
'etc
End Sub

Change Sub1 etc to the name of your subform control, yyy to the name of the
variable to store for that subform, and so on.

 

[Hardworker]

Hi There Allen
I have done what you outlined but am still hitting problems:
Dealing with the Unload sequence only, I have put the following code into a
module Just for the main form and the 1st sub form.

'This is the unload code for the main form
'The code below finds or creates a record in tblEnquiryFormRecordKeeperwhere
the
'field Variable contains "InstitutionNameLast", and stores the current
'InstitutionName in the field called Value
Public Function FormUnload(frm As Form, strVarName As String)
'Declare database object and recordset object
Dim db As Database, rst As Recordset
'If the current record has no InstitutionName, do nothing
If IsNull(frm!InstitutionName) Then Exit Function
Set db = CurrentDb
Set rst = db.OpenRecordset("EnquiryFormRecordKeeper")
rst.Index = "PrimaryKey"
rst.Seek "=", "InstitutionNameLast"
'If not found, create the entry
If rst.NoMatch Then
rst.AddNew
rst![Variable] = "InstitutionNameLast"
rst![Value] = frm![InstitutionName]
rst![Description] = "ID of last edited Institution record," &
frm.Name
rst.Update 'Update the recordset.
Else 'Else save the Institution Name of the current record
rst.Edit
rst![Value] = frm![InstitutionName]
rst.Update 'Update the recordset
End If
rst.Close 'Close the recordset

End Function
'This is the unload code for the 1st sub form
'The code below finds or creates a record in tblContactFormRecordKeeperwhere
the
'field Variable contains "ContactNameLast", and stores the current
'ContactName in the field called Value
Public Function FormUnload1(frm As Form, strVarName As String)
'Declare database object and recordset object
Dim db As Database, rst As Recordset

'If the current record has no ContactName, do nothing
If IsNull(frm![ContactName]) Then Exit Function
Set db = CurrentDb
Set rst = db.OpenRecordset("ContactFormRecordKeeper")
rst.Index = "PrimaryKey"
rst.Seek "=", "ContactNameLast"
'If not found, create the entry
If rst.NoMatch Then
rst.AddNew
rst![Variable] = "ContactNameLast"
rst![Value] = frm![ContactName]
rst![Description] = "ID of last edited Contact record," & frm.Name
rst.Update 'Update the recordset.
Else 'Else save the Contact Name of the current record
rst.Edit
rst![Value] = frm![ContactName]
rst.Update 'Update the recordset
End If
rst.Close 'Close the recordset

End Function

When I close the form the last viewed record on the main form is saved but
the record from the subform is not



Additionally:

In the form's OnLoad event I have called up the following.

Private Sub Form_Load()
Call FormLoad(Me, "InstitutionNameLast")
Call FormLoad(Me.[ContactName].Form, "ContactNameLast")
End Sub

I get an error mesage saying 'Can't find the field "l" in your expression.

If I delete the line
Call FormLoad(Me.[ContactName].Form, "ContactNameLast")
I don't get the error message

Can you see where I'm going wrong?

This is probably a timing problem. Access loads the subforms before the main
form. Their Load event is therefore likely to run first (before they have
data), and then when the main form's Load event runs, the subforms are not
at the right place.

To solve this, create 2 procedures in a standard module where you can call
them from both your form and subform.

1. In the code window, create a new module (Insert on Module menu.)

2. Paste Micosoft's 2 procedures there.

3. Change the lines:
Private Sub Form_Unload(Cancel As Integer)
Private Sub Form_Load()
to:
Public Function FormLoad(frm As Form, strVarName As String)
Public Function FormUnload(frm As Form, strVarName As String)
respectively. In both cases, change:
End Sub
to:
End Function

4. In the 2 procedures, change each instance of the word:
Me
to:
frm

5. In each of your forms and subforms, change the unload code to this:
Private Sub Form_Unload(Cancel As Integer)
Call FormLoad(Me, "xxx")
End Sub
where xxx represents the name of the variable to store in your table for
that form (such as "CustomerIDLast".)

6. Remove the load code from the subforms.
Set the main form's load code to this:
Private Sub Form_Load()
Call FormLoad(Me, "xxx")
Call FormLoad(Me.[Sub1].Form, "yyy")
Call FormLoad(Me.[Sub2].Form, "zzz")
'etc
End Sub

Change Sub1 etc to the name of your subform control, yyy to the name of the
variable to store for that subform, and so on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a form with three nested subforms. I have used the Microsoft
procedure
KB190515 "How to Open a Form to the Last Viewed Record" to open the main
form., but the subforms open to the first record not the last viewed
record.
I have repeated the above MS KB procedure in the first sub form but it
does
not work.
How can I modify the procedure so that the sub forms also open to the last
viewed record?

 

[Allen Browne]

Okay, it could be the same timing problem, in reverse (i.e. at unload.)

To test that idea, see if the table contains the correct data, for the last
record viewed in the subforms before closing the form. If not, the
information about the current record may already be gone when the subform's
Unload event fires. (You can add some Debug.Print statements to verify
this.)

If that's what's going on, you will need another way to track the last
record visited in each form. In each subform's module, declare a variable in
the General Declarations section (top, with the Option statments) to hold
the key value of the last record visited:
Private mvarID As Variant

In the Current event, populate it with the key value, e.g.:
mvarID = Me.InstitutionNameLast

Then use pass the value of mvarID to the function in Form_Unload.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi There Allen
I have done what you outlined but am still hitting problems:
Dealing with the Unload sequence only, I have put the following code into
a
module Just for the main form and the 1st sub form.

'This is the unload code for the main form
'The code below finds or creates a record in
tblEnquiryFormRecordKeeperwhere
the
'field Variable contains "InstitutionNameLast", and stores the current
'InstitutionName in the field called Value
Public Function FormUnload(frm As Form, strVarName As String)
'Declare database object and recordset object
Dim db As Database, rst As Recordset
'If the current record has no InstitutionName, do nothing
If IsNull(frm!InstitutionName) Then Exit Function
Set db = CurrentDb
Set rst = db.OpenRecordset("EnquiryFormRecordKeeper")
rst.Index = "PrimaryKey"
rst.Seek "=", "InstitutionNameLast"
'If not found, create the entry
If rst.NoMatch Then
rst.AddNew
rst![Variable] = "InstitutionNameLast"
rst![Value] = frm![InstitutionName]
rst![Description] = "ID of last edited Institution record," &
frm.Name
rst.Update 'Update the recordset.
Else 'Else save the Institution Name of the current record
rst.Edit
rst![Value] = frm![InstitutionName]
rst.Update 'Update the recordset
End If
rst.Close 'Close the recordset

End Function
'This is the unload code for the 1st sub form
'The code below finds or creates a record in
tblContactFormRecordKeeperwhere
the
'field Variable contains "ContactNameLast", and stores the current
'ContactName in the field called Value
Public Function FormUnload1(frm As Form, strVarName As String)
'Declare database object and recordset object
Dim db As Database, rst As Recordset

'If the current record has no ContactName, do nothing
If IsNull(frm![ContactName]) Then Exit Function
Set db = CurrentDb
Set rst = db.OpenRecordset("ContactFormRecordKeeper")
rst.Index = "PrimaryKey"
rst.Seek "=", "ContactNameLast"
'If not found, create the entry
If rst.NoMatch Then
rst.AddNew
rst![Variable] = "ContactNameLast"
rst![Value] = frm![ContactName]
rst![Description] = "ID of last edited Contact record," &
frm.Name
rst.Update 'Update the recordset.
Else 'Else save the Contact Name of the current record
rst.Edit
rst![Value] = frm![ContactName]
rst.Update 'Update the recordset
End If
rst.Close 'Close the recordset

End Function

When I close the form the last viewed record on the main form is saved
but
the record from the subform is not



Additionally:

In the form's OnLoad event I have called up the following.

Private Sub Form_Load()
Call FormLoad(Me, "InstitutionNameLast")
Call FormLoad(Me.[ContactName].Form, "ContactNameLast")
End Sub

I get an error mesage saying 'Can't find the field "l" in your expression.

If I delete the line
Call FormLoad(Me.[ContactName].Form, "ContactNameLast")
I don't get the error message

Can you see where I'm going wrong?

This is probably a timing problem. Access loads the subforms before the
main
form. Their Load event is therefore likely to run first (before they have
data), and then when the main form's Load event runs, the subforms are
not
at the right place.

To solve this, create 2 procedures in a standard module where you can
call
them from both your form and subform.

1. In the code window, create a new module (Insert on Module menu.)

2. Paste Micosoft's 2 procedures there.

3. Change the lines:
Private Sub Form_Unload(Cancel As Integer)
Private Sub Form_Load()
to:
Public Function FormLoad(frm As Form, strVarName As String)
Public Function FormUnload(frm As Form, strVarName As String)
respectively. In both cases, change:
End Sub
to:
End Function

4. In the 2 procedures, change each instance of the word:
Me
to:
frm

5. In each of your forms and subforms, change the unload code to this:
Private Sub Form_Unload(Cancel As Integer)
Call FormLoad(Me, "xxx")
End Sub
where xxx represents the name of the variable to store in your table for
that form (such as "CustomerIDLast".)

6. Remove the load code from the subforms.
Set the main form's load code to this:
Private Sub Form_Load()
Call FormLoad(Me, "xxx")
Call FormLoad(Me.[Sub1].Form, "yyy")
Call FormLoad(Me.[Sub2].Form, "zzz")
'etc
End Sub

Change Sub1 etc to the name of your subform control, yyy to the name of
the
variable to store for that subform, and so on.

I have a form with three nested subforms. I have used the Microsoft
procedure
KB190515 "How to Open a Form to the Last Viewed Record" to open the
main
form., but the subforms open to the first record not the last viewed
record.
I have repeated the above MS KB procedure in the first sub form but it
does
not work.
How can I modify the procedure so that the sub forms also open to the
last
viewed record?