Access not recognizing if one of two forms is open or not

[waynetheengineer]

Hi,

I'm trying to write code for a form when it closes. It's supposed to requery a combo box depending on which form is currenlty open in the background behind the current form, shown below:

Private Sub Form_Close()

If Forms(frmProjec Total).CurrentV iew <> 0 Then
Forms![frmProjectsTota l]![cboCity].Requery
Else
Forms![frmProjectsSear ch]![cboCity].Requery
End If

End Sub

If frmProjectsTota l is open behind this form, then it should requery the combo box on that form. If it's not open, that means frmProjectsTota l is open behind this form and should requery the combo box on that form instead. At any given time either only one of frmProjectsTota l or frmProjectsSear ch is open.

This code works for requerying frmProjectsTota l when it's open, but it won't work for requerying frmProjectsSear ch when that's the form that is open. It gives an error message that it can't find frmProjectsTota l. I don't understand it; this is what the code is supposed to do: of course it can't find frmProjectsTota l, that's because in this condition, it's not open.

I think my code might be wrong.
Any suggestions?

Thanks,

Wayne.

[jamjar]

I find this a pain in the A ... I think you have to back up to the documents collection or something like that to access the form if it's not open (and if it's not, the code will crash as you have found).

Alternatively, you could invoke your own error handling and test against that :
On Error Resume Next
i= Forms(frmProjec Total).CurrentV iew
If Err=0 Then
Forms![frmProjectsTota l]![cboCity].Requery
Else
Forms![frmProjectsSear ch]![cboCity].Requery
End If
On Error Goto 0
(DISCLAIMER: I didn't test this code!)

or maybe you could just call out Forms(2).Name and run your If statement against that?

James

[waynetheengineer]

hmmmm still workin at it. I did some error checking with error dialog boxes that pop up telling me which conditition the program entered. No matter which form is open, the program will always enter into the first condition, it never enters the else condition (even when frmProjectsTota l is not open). hmmmmm......... ....

hmmm

any more suggestions?

[MMcCarthy]

Pass the form name to a Global variable and then use that to decide which combobox to requery.

Mary

[waynetheengineer]

Hi Mary, good to hear from you, as usual,

Since I'm an Access noob, I'm not sure what you meant by pass the form name to a global variable. I read up on this, and apparently global variables are defined in modules that run at startup. So I created a module called WhichForm. Then I defined a string variable called:

Dim WhichForm As String

I'm not sure how my form would call this module.

Wayne.

Pass the form name to a Global variable and then use that to decide which combobox to requery.

Mary

[waynetheengineer]

Ok I tried some more stuff... Here is what I came up with so far:

In my module I wrote:

Option Compare Database

Option Explicit

Public Sub WhichForm()

If Forms(frmProjec tsTotal).Curren tView <> 0 Then
Forms![frmProjectsTota l]![cboCity].Requery
Else
Forms![frmProjectsSear ch]![cboCity].Requery
End If

End Sub

Then on the close command of my form I wrote:

Private Sub Form_Close()

Call WhichForm

End Sub

But I get an error when it tries to run the command: Call WhichForm

hmmmmmmm

[MMcCarthy]

Hi Mary, good to hear from you, as usual,

Since I'm an Access noob, I'm not sure what you meant by pass the form name to a global variable. I read up on this, and apparently global variables are defined in modules that run at startup. So I created a module called WhichForm. Then I defined a string variable called:

Dim WhichForm As String

I'm not sure how my form would call this module.

Wayne.

Define the variable as

Global WhichForm As String

[MMcCarthy]

Forget the code in your last post.

In the form Open event of the two forms 'frmProjecTotal ' and 'frmProjectsSea rch' put the following:

WhichForm = Me.Name ' this will pass the current form name to the global variable.

Now in the Form close event of the other form ...

Private Sub Form_Close()
 
   If WhichForm = "frmProjectsTotal" Then
      Forms![frmProjectsTotal]![cboCity].Requery
   ElseIf WhichForm = "frmProjectsSearch" Then
      Forms![frmProjectsSearch]![cboCity].Requery
   End If
 
End Sub

Mary

[ADezii]

Hi,

I'm trying to write code for a form when it closes. It's supposed to requery a combo box depending on which form is currenlty open in the background behind the current form, shown below:

Private Sub Form_Close()

If Forms(frmProjec Total).CurrentV iew <> 0 Then
Forms![frmProjectsTota l]![cboCity].Requery
Else
Forms![frmProjectsSear ch]![cboCity].Requery
End If

End Sub

If frmProjectsTota l is open behind this form, then it should requery the combo box on that form. If it's not open, that means frmProjectsTota l is open behind this form and should requery the combo box on that form instead. At any given time either only one of frmProjectsTota l or frmProjectsSear ch is open.

This code works for requerying frmProjectsTota l when it's open, but it won't work for requerying frmProjectsSear ch when that's the form that is open. It gives an error message that it can't find frmProjectsTota l. I don't understand it; this is what the code is supposed to do: of course it can't find frmProjectsTota l, that's because in this condition, it's not open.

I think my code might be wrong.
Any suggestions?

Thanks,

Wayne.

The correct, and most efficient, way of handling this situation is to reference the Forms Collection which contains an entry for each Form that is currently 'Open':

Dim frm As Form
 
For Each frm in Forms
  If frm.Name = "frmProjectsTotal" Then
    Forms![frmProjectsTotal]![cboCity].Requery
  Else
    Forms![frmProjectsSearch]![cboCity].Requery
  End If
Next

[waynetheengineer]

Thanks guys! :) I got it.

This is the code I ended up using:

Dim frm As Form
 
For Each frm In Forms
  If frm.Name = "frmProjectsTotal" Then
    Forms![frmProjectsTotal]![cboCity].Requery
  Else
     If frm.Name = "frmProjectsSearch" Then
        Forms![frmProjectsSearch]![cboCity].Requery
     End If
  End If
Next

I had to use an extra nested if statement for it to work.

This court is adjourned!