Checking for an open form to close

[adamroof]

I have an Add Client form that after you type in a name it runs a VB query for a match in the Old Clients table, if it matches, it opens the Old Clients form so i can have the option to "rehire", if no match it just continues on in the Add Client Form. Problem is that when i get a match, the form stays open in the background. I cant code a close acForm because of the query running. If i put a close elsewhere, but the form is not open, it will err obviously.
How can i code like "IF AddClient Form is open, Close it. Else Don't"

 

[vbajock]

"If i put a close elsewhere, but the form is not open, it will err obviously."

Docmd.close acform, "frmWhatever" never returns an error, whether the form is not open or even if it doesn't exist.

 

[adamroof]

Figured someone would say that...I'll post the code in a sec for you to try and you'll see what i mean.

 

[adamroof]

**Begin Code**
Private Function ExecuteSearch(strCriteria As String) As Boolean
On Error GoTo Err_ExecuteSearch

Dim strSQL As String
Dim strSQLWhere As String
Dim lngCount As Long

strSQL = "SELECT * FROM ClientTerminated"
strSQLWhere = "ClientName IN(SELECT ClientName " _
& "FROM ClientTerminated WHERE " _
& "ClientName LIKE '*" & strCriteria & "*')"

strSQL = strSQL & " WHERE " & strSQLWhere

' find out if there are any matching records
lngCount = FindRecord(strSQL)

' open results form if records found, otherwise return false
If lngCount = 0 Then
ExecuteSearch = False
Else
ExecuteSearch = True
DoCmd.OpenForm "ClientsT", , , strSQLWhere
DoCmd.GoToControl "ClientName"
(***Here is where i want to close the AddClient Form***)
(***It will fail using docmd.close acForm "AddClient"***)
(***Which is this form***)
End If

Exit_ExecuteSearch:
Exit Function

Err_ExecuteSearch:
MsgBox Err.Description
ExecuteSearch = False
Resume Exit_ExecuteSearch

End Function
**End Code**

 

[vbajock]

I guess I don't know what your saying. Are you saying, you try to close the form and you get some type of error? Or when you execute the close command, the form doesn't close and no error is returned?

 

[adamroof]

Run-time error '2585'

This action can't be carried out while processing a form or report event

***
Even though im done with the event.

 

[vbajock]

ok, this is tricky. You might have to try a couple of things. Lets try this first

DoCmd.GoToControl "ClientName"
'====> put:
Doevents
Docmd.close acform "whatever"

 

[adamroof]

Now its a prettier
"This action can't be carried out while processing a form or report event."
('Microsoft Access' Title 'vbOKOnly' message box)

 

[vbajock]

The problem might be in your sql statement
strSQL = "SELECT * FROM ClientTerminated"
strSQLWhere = "ClientName IN(SELECT ClientName " _
& "FROM ClientTerminated WHERE " _
& "ClientName LIKE '*" & strCriteria & "*')"

strSQL = strSQL & " WHERE " & strSQLWhere

The logic of this seems circular. Why did you use the IN statement?

Wouldn't
strSQL = "SELECT * FROM ClientTerminated"
strSQL =strSQL & "WHERE ClientName LIKE '*" & strCriteria & "*')"
accomplish the same thing?

 

[adamroof]

I had to use the IN for the strSQLWhere to work in the execute search function so that it only checked for ClientName field out of the table. I think i have a workaround that will be fine for my accomplishments.
I left the AddClient form open in the back until a user clicks Close or ReHire button in the ClientTerminated form.
If they went to add a new record, i added the close acForm on the button of the Clients form, which as you say did not error if it wasnt open. On the AddClient, i added this to the event right after executeSearch = True
****
Select Case MsgBox(strMsg, vbQuestion + vbYesNoCancel, "FBBH Database&quot
Case vbNo
DoCmd.OpenForm "Clients", acNormal, , , acFormAdd, , Me!ClientName.Value
DoCmd.GoToControl "ClientEmail"
Case vbYes
DoCmd.OpenForm "ClientsT", , , strSQLWhere
DoCmd.GoToControl "ClientName"
Case Else
Me!CancelAddClientBtn.SetFocus
Exit Function
End Select
****
and this to the Client On Load event to carry over the name
****
Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me!ClientName.Value = Me.OpenArgs
Me!ClientEmail.SetFocus
ElseIf IsNull(Me!ClientName) Then
Me!ClientName.SetFocus
End If
End Sub

 

[younguser]

Pass the form name to the following function. Function will return True if form is open and False if it's not.

'****** Code Start ********
Function fIsLoaded(ByVal strFormName As String) As Integer
'Returns a 0 if form is not open or a -1 if Open
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then
If Forms(strFormName).CurrentView <> 0 Then
fIsLoaded = True
End If
End If
End Function

 

[adamroof]

Thanks! Heres what i did. If frmAssets is open, paste info into field in form, if not don't! Works great! I had to modify younguser, but sent me in right direction!
******
Private Sub cmdClose_Click()
Dim boolRet As Boolean
Dim total As String
Dim strForm As String

Const conObjStateClosed = 0

strForm = &quot;frmAssets&quot;
boolRet = ChangeProperty(&quot;calcDisplay&quot;, dbDouble, Val(txtDisplay.Caption))
boolRet = ChangeProperty(&quot;calcMem&quot;, dbDouble, Val(lblMem.Caption))
boolRet = ChangeProperty(&quot;calcTempStore&quot;, dbDouble, Val(lblTempStore.Caption))
total = Me!txtDisplay.Caption

If SysCmd(acSysCmdGetObjectState, acForm, strForm) <> conObjStateClosed Then
Forms![frmAssets]![PurchPrice].Value = total
DoCmd.Close acForm, Me.Name
Else
DoCmd.Close acForm, Me.Name
End If
End Sub
*******
without declaring a constant, if would err randomly.