Open MS Access form or report in same location as when last closed

Question

I'm kind of a novice at Access and having some difficulty implementing something I saw in another post. MS-Access: How to open form in same location/size as closed/previous form?

I want to open my pop-up forms in the same location they were last closed in and I've created a table (sizpos) to store the 4 attributes for position and size (left, top, width, height) and the name of the form (object) and a unique id number.

I need some help figuring out the VBA code to store the attributes of the open form based on the form name on the close or unload event. I also need help with the code to retrieve the information from the table for the opening form on the open or load event.

I tried making use of dlookup in me.move and did not have any success, but may not have been implementing it correctly.

I came up with the following that almost works for the reports. I just can't figure out how to pull the report's name from the report being opened similar to the me.name function in a form. I've noted those areas with me.reportname???

Private Sub Report_Open(Cancel As Integer)
Dim l As Long
Dim t As Long
l = Nz(DLookup("Left", "SizPos", "Object = 'me.reportname???'"))
t = Nz(DLookup("Top", "SizPos", "Object = 'me.reportname???'"))
Me.Move Left:=l, Top:=t
End Sub

Private Sub Report_Close()
Dim strSQL As String
strSQL = "UPDATE SizPos SET SizPos.Left = " & Me.WindowLeft & ", SizPos.Top = " & Me.WindowTop & " WHERE SizPos.Object = 'me.reportname???';"
CurrentDb.Execute strSQL
End Sub

The below works great for the form.

Private Sub Form_Open(Cancel As Integer)
Dim l As Long
Dim t As Long
Dim w As Long
Dim h As Long
l = Nz(DLookup("Left", "SizPos", "Object = '" & Me.Name & "'"))
t = Nz(DLookup("Top", "SizPos", "Object = '" & Me.Name & "'"))
w = Nz(DLookup("Width", "SizPos", "Object = '" & Me.Name & "'"))
h = Nz(DLookup("Height", "SizPos", "Object = '" & Me.Name & "'"))
Me.Move Left:=l, Top:=t, Width:=w, Height:=h
End Sub

Private Sub Form_Close()
DoCmd.Save
Dim strSQL As String
strSQL = "UPDATE SizPos SET SizPos.Left = " & Me.WindowLeft & ", SizPos.Top = " & Me.WindowTop & ", SizPos.Width = " & Me.WindowWidth & ", SizPos.Height = " & Me.WindowHeight & " WHERE SizPos.Object = '" & Me.Name & "';"
CurrentDb.Execute strSQL
End Sub

Answer 1

Use a DAO.Recordset instead of 4 DLookup statements to fetch the values for the object's size and position. You can also use a DAO.Recordset to store them later instead of building and executing UPDATE and INSERT statements.

This is my sizpos table:

CREATE TABLE sizpos (
    id COUNTER PRIMARY KEY,
    obj_name TEXT(255) UNIQUE,
    lft INTEGER,
    tp INTEGER,
    wdth INTEGER,
    hght INTEGER
    );

I created a parameter query to fetch stored size and position values for a given form or report name:

PARAMETERS which_object Text (255);
SELECT s.obj_name, s.lft, s.tp, s.wdth, s.hght
FROM sizpos AS s
WHERE s.form_name=[which_object];

I could have saved that as a named query. But I created a function, SelectObjectSettings, to return that statement text.

So during Form Load, I can feed the form's name to that query and open a DAO.Recordset with the query's result. Then, if the recordset is not empty, use the recordset's values to set the form's size and position:

Private Sub Form_Load()
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set qdf = db.CreateQueryDef(vbNullString, SelectObjectSettings)
    qdf.Parameters("which_object").Value = Me.Name
    Set rs = qdf.OpenRecordset
    With rs
        If Not (.BOF And .EOF) Then
            Me.Move Left:=!lft, Top:=!tp, Width:=!wdth, Height:=!hght
        End If
    End With

    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing

End Sub

Note: If you have a saved parameter query, you can Set qdf to that ...

'Set qdf = db.CreateQueryDef(vbNullString, SelectObjectSettings)
Set qdf = db.QueryDefs("YourQueryName")

At Form Unload, save the form's size and position:

Private Sub Form_Unload(Cancel As Integer)
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set qdf = db.CreateQueryDef(vbNullString, SelectObjectSettings)
    qdf.Parameters("which_object").Value = Me.Name
    Set rs = qdf.OpenRecordset
    With rs
        If (.BOF And .EOF) Then
            ' no existing record for this object; create one
            .AddNew
            !obj_name = Me.Name
        Else
            ' a record exists for this object; edit it
            .Edit
        End If
        !lft = Me.WindowLeft
        !tp = Me.WindowTop
        !wdth = Me.WindowWidth
        !hght = Me.WindowHeight
        .Update
        .Close
    End With

    Set rs = Nothing
    Set qdf = Nothing
    Set db = Nothing

End Sub