By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,402 Members | 1,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,402 IT Pros & Developers. It's quick & easy.

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

waynetheengineer
P: 26
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(frmProjecTotal).CurrentView <> 0 Then
Forms![frmProjectsTotal]![cboCity].Requery
Else
Forms![frmProjectsSearch]![cboCity].Requery
End If

End Sub

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

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

I think my code might be wrong.
Any suggestions?

Thanks,

Wayne.
Apr 3 '07 #1
Share this Question
Share on Google+
10 Replies


jamjar
P: 50
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(frmProjecTotal).CurrentView
If Err=0 Then
Forms![frmProjectsTotal]![cboCity].Requery
Else
Forms![frmProjectsSearch]![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
Apr 3 '07 #2

waynetheengineer
P: 26
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 frmProjectsTotal is not open). hmmmmm.............

hmmm

any more suggestions?
Apr 3 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Pass the form name to a Global variable and then use that to decide which combobox to requery.

Mary
Apr 4 '07 #4

waynetheengineer
P: 26
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
Apr 5 '07 #5

waynetheengineer
P: 26
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(frmProjectsTotal).CurrentView <> 0 Then
Forms![frmProjectsTotal]![cboCity].Requery
Else
Forms![frmProjectsSearch]![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
Apr 5 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Apr 5 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
Forget the code in your last post.

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

Expand|Select|Wrap|Line Numbers
  1. WhichForm = Me.Name ' this will pass the current form name to the global variable.
Now in the Form close event of the other form ...
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Close()
  2.  
  3.    If WhichForm = "frmProjectsTotal" Then
  4.       Forms![frmProjectsTotal]![cboCity].Requery
  5.    ElseIf WhichForm = "frmProjectsSearch" Then
  6.       Forms![frmProjectsSearch]![cboCity].Requery
  7.    End If
  8.  
  9. End Sub
Mary
Apr 5 '07 #8

ADezii
Expert 5K+
P: 8,677
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(frmProjecTotal).CurrentView <> 0 Then
Forms![frmProjectsTotal]![cboCity].Requery
Else
Forms![frmProjectsSearch]![cboCity].Requery
End If

End Sub

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

This code works for requerying frmProjectsTotal when it's open, but it won't work for requerying frmProjectsSearch when that's the form that is open. It gives an error message that it can't find frmProjectsTotal. I don't understand it; this is what the code is supposed to do: of course it can't find frmProjectsTotal, 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':

Expand|Select|Wrap|Line Numbers
  1. Dim frm As Form
  2.  
  3. For Each frm in Forms
  4.   If frm.Name = "frmProjectsTotal" Then
  5.     Forms![frmProjectsTotal]![cboCity].Requery
  6.   Else
  7.     Forms![frmProjectsSearch]![cboCity].Requery
  8.   End If
  9. Next
Apr 5 '07 #9

waynetheengineer
P: 26
Thanks guys! :) I got it.

This is the code I ended up using:

Expand|Select|Wrap|Line Numbers
  1. Dim frm As Form
  2.  
  3. For Each frm In Forms
  4.   If frm.Name = "frmProjectsTotal" Then
  5.     Forms![frmProjectsTotal]![cboCity].Requery
  6.   Else
  7.      If frm.Name = "frmProjectsSearch" Then
  8.         Forms![frmProjectsSearch]![cboCity].Requery
  9.      End If
  10.   End If
  11. Next
I had to use an extra nested if statement for it to work.

This court is adjourned!
Apr 7 '07 #10

ADezii
Expert 5K+
P: 8,677
Thanks guys! :) I got it.

This is the code I ended up using:

Expand|Select|Wrap|Line Numbers
  1. Dim frm As Form
  2.  
  3. For Each frm In Forms
  4.   If frm.Name = "frmProjectsTotal" Then
  5.     Forms![frmProjectsTotal]![cboCity].Requery
  6.   Else
  7.      If frm.Name = "frmProjectsSearch" Then
  8.         Forms![frmProjectsSearch]![cboCity].Requery
  9.      End If
  10.   End If
  11. Next
I had to use an extra nested if statement for it to work.

This court is adjourned!
This is a little cleaner:
Expand|Select|Wrap|Line Numbers
  1. Dim frm As Form
  2.  
  3. For Each frm In Forms
  4.   If frm.Name = "frmProjectsTotal" Then
  5.     Forms![frmProjectsTotal]![cboCity].Requery
  6.   ElseIf frm.Name = "frmProjectsSearch" Then
  7.     Forms![frmProjectsSearch]![cboCity].Requery
  8.   Else
  9.      'None of the above
  10.   End If
  11. Next
Apr 8 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.