Set Focus Problems

[JezLisle]

I have a search form that has this code behind below... The problem I'm having is that once I select an option from my ComboBox and an error pops up...
Run-time Error 2185
you cant reference a property or method for a control unless the control has the focus.
I thought in my code I had set the focus on the ComboBox, hovering over the debug line it points to this

sQRY = _
                "SELECT * " & vbCrLf & _
                "FROM jez.HaH_ReferralReason " & vbCrLf & _
                "WHERE jez.HaH_ReferralReason." & Me.cboSearchOn.Text & " LIKE '*" & Me.txtInputSearch.Text & "*' "

Highlighting the section it shows the error message over the part Me.txtInpustSearch.text. but I've been unable to enter anything in that TextBox as the error has popped up before the opportunity to do so.

Private Sub txtInputSearch_Change()
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sQRY As String
'    On Error GoTo Err
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cnn.Open "Provider=sqloledb;Data Source=CISSQL1;Initial Catalog=CORPINFO;Integrated Security=SSPI;"
    If Not IsNull(cboSearchOn) Then
        If Not IsNull(Me.txtInputSearch.Text) Then
        Me.cboSearchOn.SetFocus
        sQRY = _
                "SELECT * " & vbCrLf & _
                "FROM jez.HaH_ReferralReason " & vbCrLf & _
                "WHERE jez.HaH_ReferralReason." & Me.cboSearchOn.Text & " LIKE '*" & Me.txtInputSearch.Text & "*' "
            rs.CursorLocation = adUseClient
            rs.Open sQRY, cnn, adOpenForwardOnly, adLockReadOnly
            Me.lstSearch.RowSource = sQRY
        End If
    Else
        Me.cboSearchOn.SetFocus
        Me.cboSearchOn.Dropdown
    End If
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
    Exit Sub
'Err:
'    basError.LogError VBA.Err, VBA.Error$, "Form_frmTest- txtInputSearch_Change()"
End Sub

Where am I going wrong?

 

[boblarson]

Don't use the .Text part as a control needs the focus to use that. Just reference your control:

Me.cboSearchOn

or, if you want to be explicit (which isn't necessary because the .Value is the default for most controls):
Me.cboSearchOn.Value

 

[nIGHTmAYOR]

ok you have two problems:
1 - onchange event will trigger at every key stroke leading you to trigger search dialogue several times with incomplete search parameter.
2 - you need form to refresh before onchange txtInputSearch can report full string as since if not refreshed it would report string before change (string before typing final keystroke).

I would suggest using after update event of txtInputSearch which requires you to lose focus of control which is as good as my suggestion to refresh form or maybe use a button , i wouldnt recommend using code triggered by on change in an input text box.

best regards.

 

[JezLisle]

I changed the Me.cboSearchOn & the Me.txtInputSearch and still found I have a problem, as when I start to put data into the txtInputSearch text box it then reverts back to the ComboBox.

This is my code

Private Sub txtInputSearch_Change()
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sQRY As String
'    On Error GoTo Err
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cnn.Open "Provider=sqloledb;Data Source=CISSQL1;Initial Catalog=CORPINFO;Integrated Security=SSPI;"
    If Not IsNull(cboSearchOn) Then
        If Not IsNull(Me.txtInputSearch.Text) Then
        Me.cboSearchOn.SetFocus
        sQRY = _
                "SELECT * " & vbCrLf & _
                "FROM jez.HaH_ReferralReason " & vbCrLf & _
                "WHERE jez.HaH_ReferralReason." & Me.cboSearchOn & " LIKE '*" & Me.txtInputSearch & "*' "
            rs.CursorLocation = adUseClient
            rs.Open sQRY, cnn, adOpenForwardOnly, adLockReadOnly
            Me.lstSearch.RowSource = sQRY
        End If
    Else
        Me.cboSearchOn.SetFocus
        Me.cboSearchOn.Dropdown
    End If
    rs.Close
    cnn.Close
    Set rs = Nothing
    Set cnn = Nothing
    Exit Sub
'Err:
'    basError.LogError VBA.Err, VBA.Error$, "Form_frmTest- txtInputSearch_Change()"
End Sub
Private Sub cboSearchOn_Change()
  Me.txtInputSearch.SetFocus
  Call txtInputSearch_Change
End Sub

I also tried the AfterUpdate and found the same error, where have I gone wrong?

 

[JezLisle]

ok you have two problems:
1 - onchange event will trigger at every key stroke leading you to trigger search dialogue several times with incomplete search parameter.
1 - you need form to refresh before onchange txtInputSearch can report full string as it would report string before change if not refreshed.

I would suggest using after update event which requires you to lose focus of control or maybe use a button , i wouldnt recommend using code triggered by on change in an input text box.

best regards.

How would you do this as from my other thread I have used the AfterUpdate() from this everytime I type a letter into the TextBox it then reverts back to the ComboBox and this then needs clicking back onto the TextBox. How can I input the word and it then sorts through the list box and only shows me the records that contain the Text in the TextBox??

 

[boblarson]

Well, getting rid of these would be a start:

Me.cboSearchOn.SetFocus (there are 2 of these to get rid of)

Me.cboSearchOn.Dropdown (one of these)