Help With IsLoaded on Forms

[gnarpeggio]

Hello,

I'm working on a feature I'd like to include in my database that utilizes the IsLoaded property to check for an open form and complete an action depeding on which form is currently open.

I have two forms, both of which can pull Party Information from a Parties table. My problem has been getting the DB to tell the truth and correctly run the If statement I built when the form IsLoaded.

When entering a new record, users can choose a previously entered party by opening the Parties form and clicking the Select Party cmd button. Once the party is selected, my If statement will determine which form is open (IsLoaded) and runs an update query that adds the Party ID to the new record on the table of the open form.

Here is what my code is looking like at the moment:

Private Sub cmdSend_Data_Click()
On Error GoTo Err_cmdSend_Data_Click

Dim BatchSQL As String
Dim AcctSQL As String
Dim WROpen As String
Dim AcctOpen As String

AcctSQL = "UPDATE tbl_frm_Single_Payment_Acct SET tbl_frm_Single_Payment_Acct.Party_ID = tbl_Parties.Party_ID WHERE tbl_Single_Payment_Acct= " & Forms!frm_Single_Payment_Acct.Item_ID

BatchSQL = "UPDATE tbl_WR_Batch_Deposits SET tbl_WR_Batch_Deposits.Party_ID = tbl_Parties.Party_ID WHERE tbl_WR_Batch_Deposits.Item_ID= " & Forms!frm_Batch_Deposit_WR.Item_ID

WROpen = CurrentProject.AllForms("frm_Batch_Deposit_WR").IsLoaded

AcctOpen = CurrentProject.AllForms("frm_Single_Payment_Acct").IsLoaded

If WROpen = True Then
DoCmd.RunSQL BatchSQL
ElseIf AcctOpen = True Then
DoCmd.RunSQL AcctSQL
ElseIf WROpen And AcctOpen = True Then
MsgBox "Please close one of the forms to continue"
Else
Exit Sub
End If

Exit_cmdSend_Data_Click:
Exit Sub

Err_cmdSend_Data_Click:
Select Case Err
Case 2450
MsgBox "You must have one of the following forms open to select a party:" & vbNewLine & vbNewLine & "Single Payments - Accounting" & vbNewLine _
& vbNewLine & "Batch Deposits", 7
DoCmd.Hourglass False
Resume Exit_cmdSend_Data_Click

End Select
End Sub

This code is built on the Select Party command button on my Parties form. It’s supposed to 1) check if the “frm_Batch_Deposit_WR” form is open, if True then run BatchSQL. If False, move onto ElseIf AcctOpen. If True run AcctSQL, if False go to next line and check if both are open. If True, produce MsgBox warning.

Any help on resolving this issue would be greatly appreciated.

Thanks!

 

[boblarson]

Your variables should be Booleans not strings for those since you want true or false.

Dim WROpen As Boolean
Dim AcctOpen As Boolean

 

[pbaldy]

Well, if either variable is true code will never get to the 3rd test. An If/Then will stop processing the tests as soon as one is true. Put the test for both being open first.

 

[gnarpeggio]

The If order pbaldy suggested makes perfect sense, but I'm getting my error MsgBox when I try to execute the button (Run-time Error 2450 - Microsoft Access can't find the form "frm_Batch_Deposit_WR" referred to in a macro expression or visual basic code). When the Parties form opens, the frm_Batch_Deposit_WR form evaluates to False for some reason, even though the form IsLoaded (still loaded behind the Parties form window). Any reason why this seems to be happening? And I did change those variables to Booleans, thanks Bob!

Thanks again,

 

[boblarson]

Does the form name actually have the underscores or are they really spaces?

 

[gnarpeggio]

The names on the forms include the underscores. I was told to avoid spaces in names when working with objects. I was thinking about shortening the names up regardless since they trail a bit too long. The DB I have is still in its development stages so there's room to improve

 

[boblarson]

Paul has the answer and I missed it.

Put the test for both being open first.

You are trying to assign their values to your SQL String variables before testing to see whether they are open.

 

[gnarpeggio]

I switched the order of the If statement as indicated, but my error handler is catching the Run-time error 2450 (MS Access can't find the form "form name") when I run the command button. This error occurs even when the form is open! Would this whole command I'm trying to accomplish be better as a function passed to the sub?

 

[pbaldy]

What is the code now and where exactly does it error (you may need to temporarily comment out the "On Error" line)?

 

[gnarpeggio]

I commented out the error handler, and now my tells me that one of my form names is incorrectly named, which I've checked numerous times and that's not the case!

The debugger runs to this line every time:

AcctSQL = "UPDATE tbl_Single_Payment_Acct SET tbl_Single_Payment_Acct.Party_ID = tbl_Parties.Party_ID WHERE tbl_Single_Payment_Acct= " & Forms!frm_Single_Payment_Acct.Item_ID

The name is correct, should I remove the underscores or shorten it to help?

 

[pbaldy]

Presumably you're getting that when that form isn't the one open? I'd build the SQL inside your If/Then test so you know it's open.