Open form and setfocus to specific control

[Kregg]

Hi all -

I have a from that has information icons that opens a separate form to help users make the right choice on a form. With each information form users are able to choose "Yes" or "No" and then it returns them back to the form they started. I am great to this point however I am having trouble setting the focus to the control the user original left the form from. Here is some basic info to help visualize:

Form A
Control 1
Control 2
Control 3

Form B
Information for Control 1

Form C
Information for Control 2

Form D
Information for Control 3

For Forms B - C I would like the form to close, open Form A and then set focus to the control that the form was providing information for.

For example, if you are on Form C and you make a decision, I want Form C to close, Form A to open and focus to be set to Control 2.

Any help would be greatly appreciated. Thanks.

Below is what I currently have: The problem is the last line and I have a feeling this is a simple fix...

Private Sub image5_Click()
Dim sWHERE As String
Criminal_Background.Value = "Yes"
  ''If the ID is a number, built the WHERE clause like this:
  sWHERE = "[ID] = " & Me.ID
  'Open the form
  DoCmd.Close , ""
  DoCmd.OpenForm "Questionnaire_frm", acNormal, , sWHERE
  Me.Criminal_Background.SetFocus
End Sub

 

[here4real]

I had to do a similar thing and accomplished it through a function:

Public Sub CloseAndOpen(frmparm)

    Dim CloseObj As String
    Dim OpenObj As String

    CloseObj = Mid(frmparm, 1, InStr(1, frmparm, ",") - 1)
    OpenObj = Mid(frmparm, InStr(1, frmparm, ",") + 1)
    
    If CloseObj <> "" Then
        CloseType = DLookup("Type", "MSysObjects", "NAME = '" & CloseObj & "'")
    Else
        CloseType = 0
    End If
    
    If OpenObj <> "" Then
        OpenType = DLookup("Type", "MSysObjects", "NAME = '" & OpenObj & "'")
    Else
        OpenType = 0
    End If
    
    Select Case CloseType
        Case -32768
            DoCmd.Close acForm, CloseObj
        Case -32764
            DoCmd.Close acReport, CloseObj
    End Select
    
    Select Case OpenType
        Case -32768
            DoCmd.OpenForm OpenObj
        Case -32764
            DoCmd.OpenReport OpenObj, acViewPreview
    End Select
    
End Sub

You can call it by:

CloseAndOpen (",frmReports")

The first parameter is what to close and the second parameter is what to open.

 

[Kregg]

Thanks for the reply here4real. I am going to be honest I am a little lost when looking at your code. I can muddle through most code but I am not following what you have going on in this code. Is it possible to replace: Me.Criminal_History.Setfocus with [FormA]![Criminal_History].setfocus. I don't believe anything I placed is proper code in the last sentence but this just seems like a complex response to this process...but that could be again that I don't understand you code.

 

[here4real]

All the code did was close one form and open the second form. You can create an On Load event of the form to conditionally SetFocus depending on who is calling it.

 

[here4real]

Even better.

Create an On Load event for the second form. THERE you close the first form and do whatever you need to do. When done, reopen the first form passing a parameter indicating who is making the call. On the first form, create an On Load event that based on the parameter will SetFocus. Ignore what I said before - not necessary. I misunderstood.

 

[Kregg]

Even better.

Create an On Load event for the second form. THERE you close the first form and do whatever you need to do. When done, reopen the first form passing a parameter indicating who is making the call. On the first form, create an On Load event that based on the parameter will SetFocus. Ignore what I said before - not necessary. I misunderstood.

I am not sure what you mean by "Who is making the call"?

 

[here4real]

The first form is being opened 2 ways. The first way is however it normally opens. The second way is when it is called/opened by the second form. Pass a different parameter depending on the case. When it is opened by the second form, have an If statement that SetFocus to the control that you want.

 

[JHB]

..
Any help would be greatly appreciated. Thanks.

Closing the form isn't the way, either open the other forms as dialog or hide the calling form and unhide it when the other forms close.
But having 3 forms only for answering "Yes"/"No" sound for me as a design issuer.

 

[Kregg]

I finally found a simple solution to this. It is a simple switch to the last line of code as I thought.

Private Sub image5_Click()
Dim sWHERE As String
Criminal_Background.Value = "Yes"
  ''If the ID is a number, built the WHERE clause like this:
  sWHERE = "[ID] = " & Me.ID
  'Open the form
  DoCmd.Close , ""
  DoCmd.OpenForm "Questionnaire_frm", acNormal, , sWHERE
  Forms!Questionnaire_frm!Criminal_Background.SetFocus
End Sub

The Me! reference was the problem it was continuing to look for the original form that was already closed. By changing the line from Me! to Forms! I can reference the newly opened form and setfocus to the control that I want.