Form closing and it's not suppose to be!

  • Thread starter Thread starter Nadine
  • Start date Start date
N

Nadine

Ok so I have in the onLoad of a form (results) a query that gets run
based on criteria selected in the form that opens it (search). What I
am trying to do is if based on the search criteria there are no
results to display close the results form and reopen the search form
and then have a message display . For some reason it closes the
results form opens the search form displays the message and then
closes the search form and I don't know why??? Anyone have any
ideas???

Here is the code for the form load of the Results form which is where
all this is taking place:

Private Sub Form_Load()

Dim strMessage As String
Dim strDialogType As String
Dim strTitle As String
Dim strViewChangesBy

strViewChangesBy = "ViewChangesBy"

Dim strSQL As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection

strSQL = "Select * From [tblChangeHistory] "

If Forms!ViewChangesBy![txtHospID] <> " " Then
strSQL = strSQL & "Where [hosp_id] = '" &
Forms!ViewChangesBy![txtHospID] & "' "
Else
strSQL = strSQL & "WHERE (1=1) "
End If

If Forms!ViewChangesBy![txtChangeID] <> " " Then
strSQL = strSQL & " AND [change_request_id] = '" &
Forms!ViewChangesBy![txtChangeID] & "' "
End If

strSQL = strSQL & " Order By [change_request_id]"

rs.Open strSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdText

**This seems to be where the problem is**
If rs.EOF Then
DoCmd.Close acForm, Me.Name
DoCmd.OpenForm strViewChangesBy
strMessage = "There are no results to display based on your
cirteria."
strDialogType = vbOKOnly + vbExclamation
strTitle = "Hospital List"
MsgBox strMessage, strDialogType, strTitle
Else
Set Me.Recordset = rs
End If

rs.Close
Set rs = Nothing

End Sub

I do have some code in the form load of the Search form but it is
simply to restore from Maximized size which I set in the Results form:

Private Sub Form_Load()

DoCmd.Restore

End Sub

I tried removing this bit of code to see if it was the problem and it
still closes the form so I am assuming it is something in the first
chunk of code. Again this has been driving me nuts so any help is
muchly appreciated! Thank.
 
Nadine said:
Ok so I have in the onLoad of a form (results) a query that gets run
based on criteria selected in the form that opens it (search). What I
am trying to do is if based on the search criteria there are no
results to display close the results form and reopen the search form
and then have a message display . For some reason it closes the
results form opens the search form displays the message and then
closes the search form and I don't know why??? Anyone have any
ideas???
Click to expand...

What code are you running to open the results form? A button click on the
search form I would imagine. Does that code have a line that tells the
search form to close?
 
Back
Top