SetFocus From One SubForm to Another's Control

[kdbailey]

I cannot seem to be able to get focus on a control within another SubForm.



I'll explain what I'm doing first.

I have a main form "Project".

First SubForm "subteam" where my code lies.

Second SubForm "subeditemployee" where the code will take me.

The trigger is a NotInList event in where the employee they are trying to add to their team is not found. It then takes them the employee creation subform. The error is not until the very end.

Private Sub listname_NotInList(NewData As String, Response As Integer)
Dim names As Variant, rsemps As DAO.Recordset
Me.listname = ""
Me.listdept = ""
Response = 3
names = Split(NewData, " ")
SQL = "SELECT [Employees].* FROM [Employees] WHERE (("
max = UBound(names)
If max = 0 Then
    formquery = "[Name] Like '*" & names(d) & "*'"
    SQL = "SELECT [Employees].* FROM [Employees] WHERE ((([Employees].[Name]) Like '*" & names(d) & "*'));"
Else
    For d = 0 To max
        If d = 0 Then
            formquery = "[Name] Like '*" & names(d) & "*'"
            SQL = "SELECT [Employees].* FROM [Employees] WHERE ((([Employees].[Name]) Like '*" & names(d) & "*'"
        Else
            formquery = formquery & " Or [Name] Like '*" & names(d) & "*'"
            SQL = SQL & " OR ([Employees].[Name]) Like '*" & names(d) & "*'"
        End If
    Next d
    SQL = SQL & "));"
End If


Set rsemps = CurrentDb.OpenRecordset(SQL)
With rsemps
    If .RecordCount <> 0 Then
        .MoveLast
        possibles = .RecordCount
        If possibles = 1 Then
            Response = MsgBox("Employee not found, did you mean '" & .Fields("Name") & "'?", vbYesNo, "Alert")
            If Response = 6 Then
                Me.listname = .Fields("Name")
                If Nz(Me.listname, "") <> "" Then
                    Me.listdept = CurrentDb.OpenRecordset("SELECT [Employees].Team FROM [Employees] WHERE ((([Employees].Name)='" & Me.listname & "'));").Fields("Team")
                End If
                DoCmd.RunCommand acCmdSaveRecord
                Me.Form.Requery
            Else
                Forms("Project").subpossibleemp.Form.Filter = formquery
                Forms("Project").subpossibleemp.Form.FilterOn = True
                Forms("Project").subpossibleemp.Form.Requery
                Forms("Project").subpossibleemp.Visible = True
                Forms("Project").subeditemployees.listemployees.SetFocus
            End If
        ElseIf possibles > 1 Then
            MsgBox "Employee not found, please check possible other names, or create a new employee.", vbOKOnly, "Alert"
            Forms("Project").subpossibleemp.Form.Filter = formquery
            Forms("Project").subpossibleemp.Form.FilterOn = True
            Forms("Project").subpossibleemp.Form.Requery
            Forms("Project").subpossibleemp.Visible = True
            Forms("Project").subeditemployees.listemployees.SetFocus
        End If
    Else
        Forms("Project").subeditemployees.listemployees.SetFocus
    End If
End With
Set rsemps = Nothing
End Sub

[Bulzie]

What is the error message?
If you do a compile on that code does if come back clean?
Is that 2nd subform open when this code is run?

Maybe also go into query designer, in criteria of a column, do build, go to open forms and drill down to that 2nd subform and select the listemployees field to make sure you have the right syntax.
Forms![Project]![subeditemployees]!
[listemployees].Setfocus

[John_G]

You don't have the right syntax to refer to a control on a subform; you don't use the name of the subform, you use the name of the control that contains it. In your case, I'll assume the name of the sub-form container control is sfrmContainer, so your statement would look something like this:


Forms("Project")!sfrmContainer.form!listemployees.SetFocus

[kdbailey]

John_G, the syntax I have typed is not the name of the forms. They are the names of the controls on my main form that contains the subs.

However, you indirectly solved my issue.

I was missing the .Form within the syntax. Final line of code looks like such...

Forms("Project").subeditemployees.form.listemployees.Setfocus

[Bulzie]

Just a tip, check syntax for referencing a form, field or option using the build option in the criteria of any query(right click in the criteria box). Sometimes I open a saved query, go to design and do this just to get the syntax I need to point to a field or option, then I cancel out of that query as I don't want to save anything.