Better way of writing this code?

Webskater

Registered User.
Local time
Today, 13:20
Joined
Aug 29, 2006
Messages
14
Hello all

Further to an earlier post asking how to check if forms were open ...

I have a form EditCompanyForm which pops up to let people edit the details of a company. The form can be opened from one of three forms.

When the EditCompanyForm is closed I want to update the form that opened it.

So I have this rather ungainly bit of code.
_____________________________________________________________
Private Sub Form_Unload(Cancel As Integer)
Dim oAccessObject As AccessObject
Dim FormName As String
FormName = "CompaniesForm"
Set oAccessObject = CurrentProject.AllForms(FormName)
If oAccessObject.IsLoaded Then
Form_CompaniesForm.Requery
End If

Dim oAccessObject1 As AccessObject
Dim FormName1 As String
FormName1 = "CompaniesCallForm"
Set oAccessObject1 = CurrentProject.AllForms(FormName1)
If oAccessObject1.IsLoaded Then
Form_CompaniesCallForm.Requery
End If

Dim oAccessObject2 As AccessObject
Dim FormName2 As String
FormName2 = "CallListForm"
Set oAccessObject2 = CurrentProject.AllForms(FormName2)
If oAccessObject2.IsLoaded Then
Form_CallListForm.Requery
End If
End Sub
___________________________________________________

Do I need 3 separate AccessObjects?

Should I be using ... Set oAccessOjbect = nothing ... at the end?

Is there a more elegant way of doing this?

Thanks for any insights.
 
One way that you could do it is to update the details in the source forms.

i.e.

Code:
DoCmd.OpenForm "EditCompanyForm ", , , , , acDialog

'Place code to refresh the form details here

When you open a form in dialog view it does not run the code below the OpenForm command untill you close EditCompanyForm.
 
The shortening/simplification would entail this:

1. create a public variable in a module (not a form module) like strFormName. Then, on each form's event that opens the EditCompanyForm form place this code:
Code:
strFormName = Me.Name

2. On the unload event of the EditCompanyForm form place:
Code:
Dim oAccessObject As AccessObject
Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
Application.Forms(strFormName).Requery
End If
 
boblarson said:
The shortening/simplification would entail this:

1. create a public variable in a module (not a form module) like strFormName. Then, on each form's event that opens the EditCompanyForm form place this code:
Code:
strFormName = Me.Name

2. On the unload event of the EditCompanyForm form place:
Code:
Dim oAccessObject As AccessObject
Set oAccessObject = CurrentProject.AllForms(strFormName)
If oAccessObject.IsLoaded Then
Application.Forms(strFormName).Requery
End If

Thanks for that.
 
I did this in a similar way for years, however I found I was better off if I passed the name of the opening form either straight into the tag property of the form being opened, alternatively, create a property in the form and store the name of the opening form in that.

You can see a working example of this here:


This code is in the "OPENING FORM" --- It opens the date selection form and sends the opening form, its name straight into the tag property:
Code:
 strDocName = "frmDateSelection"
    DoCmd.OpenForm strDocName
        With Forms(strDocName)
            .Tag = Me.Name  'send this form's name to the form "frmDateSelection" the form "frmDateSelection" then uses the
                            'information Stored in its Tag property to send information back to this form.

This code (below) is in the form "BEING OPENED" --- The date selection form, it grabs the name of the form that opened it (From the Tag Property), the form that opened it also has a function in it called "fCalledFromOtherForm" the information is passed right back into the opening form without ever HARD CODING the forms name in the opening form.

In other words one form can be opened from any number of other forms, with exactly the same code in all of the forms. I think that's what you're trying to get to!
Code:
Private Function fPassBack(strSQLBetweenDate As String)
    With Forms(Me.Tag)
        .fCalledFromOtherForm (strSQLBetweenDate)       'Passes SQL to a Private Variable on the "Called Form"
        .Tag = strSQLBetweenDate                        'Passes SQL to the Tag on the "Called Form"
        .lblDisplayStrSQL.Caption = strSQLBetweenDate   'Passes SQL to a Label on the "Called Form"
        .lblDisplayStrSQL.BackColor = 255
        
    End With
End Function

Also the above example is quite old now, I have superseded it with a new system based on custom properties. However I am not ready to publish it yet!
 
Last edited:

Users who are viewing this thread

Back
Top Bottom