Close Form once Another Opens

[Altin]

I am trying to build multi field search into an Access 2007 database I have with 2 forms & 1 query. I have a form for entering the details & a query, with criteria such as 'Like "*" & [Forms]![frmSearch]![txtFirstName] & "*"' and 'Like "*" & [Forms]![frmSearch]![txtLastName] & "*"'. Rather than running the query from the form, & getting a datasheet display of results, I have made a 2nd form which will display the query results. The Search form has a button to open this Results form & all works well.

What I would like is to be able to close the Search form when I click the button to open the Results form. In the Search form, if I put DoCmd.Close before DoCmd.OpenForm in the code for the button, it closes my Search form which is feeding the query for the Results form before the results are displayed.

How could I get it to work the way I want? I hope Ive explained this well enough & its not too confusing.

The code is:

Private Sub cmdOpenResultsForm_Click()
On Error GoTo Err_cmdOpenResultsForm_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    DoCmd.Close
    stDocName = "frmResults"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
        

Exit_cmdOpenResultsForm_Click:
    Exit Sub

Err_cmdOpenResultsForm_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenResultsForm_Click

 

[Mr. B]

If the criteria for the record set to be displayed in the results form is in the first form you may want to consider just setting the Visible property of the first form to false, leaving it open but not visible. Then when the user closes the results form you can determine if the first form is open and decide if you want to make it visible again or also close it.

 

[Altin]

Yes that would work. How would I do this, make the search form invisible when the results form open up.
I also want to give users the choice to close the results form & go back to the search form to do another search (a simple close form button on the results form) or close both forms & go back to the home page. How would I code the close button to close both form in the 2nd case?

 

[mahenkj2]

I do not know if it is of any help to you or not but just for idea.

I am doing something similar like this. I have one query. I have made one form meant for filtering query results and acts as search. In this form, the command button which launches query, in macro code I used "close" action so as soon as query appears, search form closes.

Instead of this code, just use macro "OpenQuery" in first action and in second action use "Close". Did it work!

 

[Mr. B]

Yes that would work. How would I do this, make the search form invisible when the results form open up.

Add the following line of code to the code in the click event of the button that opens the Results form

[Forms]![frmSearch].Visible = False

Create a button on the Results form and add the following code in the Click event of that button:

'declare a string type variable to hold a message to display to user
Dim strMsg As String
'declare a variant type variable to store the results of the user input
Dim vbResponse

'assign the value to the string variable
strMsg = "Do you like to do another search?"

'display the message and get the user input
vbResponse = MsgBox(strMsg, vbQuestion + vbYesNo + vbDefaultButton1, _
     "Perform Another Search>")
'check the response by the user and take action as appropriate
If vbResponse = vbYes Then
    'check to make sure the Search form is loaded
    If CurrentProject.AllForms("frmSearch").IsLoaded = True Then
        'if the form is already open, make it visible
        Forms!frmSearch.Visible = True
    Else    'if the form is not open, open it
        DoCmd.OpenForm "frmSearch"
    End If
End If

'close the Results form
DoCmd.Close acForm, "frmResults"