Get focus on unbound textbox when form returns no records

Question

I'm a little stumped. I've got an MS Access front end application for an SQL Server back end. I have an orders form with a list box that, when selected and a "Notes" button is clicked will open another form of notes. This is a continuous form and has a data source (linked table - a view) from the back end database.

When the notes button is clicked in the main orders form, it passes a filter and an OpenArgs string to the Notes form in this code:

Private Sub cmdItemNotes_Click()
Dim i As Integer
Dim ordLine As Boolean
Dim line As Integer
Dim args As String

If Me.lstOrders.ItemsSelected.count = 1 Then
    ordLine = False
With Me.lstOrders
      For i = 0 To .ListCount - 1
            If .selected(i) Then
                If .Column(16, i) = "Orders" Then
                ordLine = True
                line = .Column(0, i)
                End If
            End If
      Next i
End With

If ordLine Then
    
    args = "txtLineID|" & line & "|txtCurrentUser|" & DLookup("[User]", "tblUsers", "[Current] = -1") & "|txtSortNum|" & _
    Nz(DMax("[SortNum]", "dbo_vwInvoiceItemNotesAll", "[LineID] = " & line), 0) + 1 & "|"
    
    DoCmd.OpenForm "frmInvoiceItemNotes", , , "LineID = " & line, , , args
            
Else
'Potting order notes
End If

Else: MsgBox "Please select one item for notes."
End If

Here is my On Load code for the Notes form:

Private Sub Form_Load()
Dim numPipes As Integer
Dim ArgStr As String
Dim ctl As control
Dim ctlNam As String
Dim val As String
Dim i As Integer

ArgStr = Me.OpenArgs

numPipes = Len(ArgStr) - Len(Replace(ArgStr, "|", ""))

For i = 1 To (numPipes / 2)
    ctlNam = Left(ArgStr, InStr(ArgStr, "|") - 1)
    Set ctl = Me.Controls(ctlNam)
    ArgStr = Right(ArgStr, Len(ArgStr) - (Len(ctlNam) + 1))
    val = Left(ArgStr, InStr(ArgStr, "|") - 1)
    ctl.Value = val
    ArgStr = Right(ArgStr, Len(ArgStr) - (Len(val) + 1))
Next i

End Sub

This code executes fine. The form gets filtered to only see the records (notes) for the line selected back in the orders form.

Because this is editing a table in the back end, I use stored procedures in a pass through query to update the table, not bound controls. The bound controls in the continuous form are for displaying current records only. So... I have an unbound textbox (txtNewNote) in the footer of the form to type a new note, edit an existing note, or post a reply to an existing note.

As stated above, the form filters on load. Everything works great when records show. But when it filters to no records, the txtNewNote textbox behaves quite differently. For instance, I have a combo box to mention other users. Here is the code after update for the combo box:

Private Sub cmbMention_AfterUpdate()
Dim ment As String
If Me.txtNewNote = Mid(Me.txtNewNote.DefaultValue, 2, Len(Me.txtNewNote.DefaultValue) - 2) Then
Me.txtNewNote.Value = ""
End If
    
    If Not IsNull(Me.cmbMention) Then
    ment = " @" & Me.cmbMention & " "
    If Not InStr(Me.txtNewNote, ment) > 0 Then
        Me.txtNewNote = Me.txtNewNote & ment
    End If
    End If

With Me.txtNewNote
.SetFocus
.SelStart = Len(Nz(Me.txtNewNote, ""))
End With
End Sub

The problem occurs with the line

.SelStart = Len(Nz(Me.txtNewNote, ""))

When there are records to display, it works. When there are no records to display, it throws the Run-time error 2185 "You can't reference a property or method for a control unless the control has the focus." Ironically, if I omit this line and make the .SetFocus the last line of code in the sub, the control is in focus with the entire text highlighted.

Why would an unbound textbox behave this way just because the filter does not show records?

Thanks!

Get focus on unbound textbox when form returns no records

0 Answers