Can't make textbox invisible

[ragtopcaddy via AccessMonster.com]

Here's my code:

Private Sub txtFindText_AfterUpdate()
Dim strSearch As String
Dim strQuote As String
Dim lngQuoteID As Long
Dim lngAuthID As Long
Dim rstQuotes As DAO.Recordset

Set rstQuotes = dbLocal.OpenRecordset("tblQuotes")
strSearch = Me.txtFindText

With rstQuotes
.OpenRecordset
.MoveLast
.MoveFirst
Do Until .EOF
strQuote = .Fields("Quote")
If InStr(1, strQuote, strSearch) > 0 Then
lngQuoteID = .Fields("QUOTID")
lngAuthID = .Fields("authorid")
Exit Do
Else
.MoveNext
End If
Loop
.Close
End With
Set rstQuotes = Nothing

If Nz(lngQuoteID, 0) > 0 Then
Me.Tag = lngQuoteID
With DoCmd
.GoToControl "txtAUTHID"
.FindRecord lngAuthID
Me.Refresh
.GoToControl "sfrmQuotes"
Me.Refresh
Me.sfrmQuotes.Form.txtQUOTID.Visible = True
.GoToControl "txtQUOTID"
Me.Refresh
.FindRecord lngQuoteID
Me.SetFocus
Me.txtLName.SetFocus
Debug.Print Me.ActiveControl.Name
Me.Refresh
Me.sfrmQuotes.Form.txtQUOTID.Visible = False
End With
Else
MsgBox "The text you are searching for was not found in tblQuotes." &
vbCrLf & _
"Try a different search string. - The quote may not be in the
table.", _
vbOKOnly, "String Not Found"
End If



End Sub


It works great, insofar as it displays the correct author and quote. But the
txtQUOTD textbox I use just for searching purposes can't be made invisible at
the end of the routine. I keep getting a message saying that it can't hide
the control that has the focus. However, the code that sets the focus on
txtLName works and when I enter ?me.activecontrol.name in the debug window, I
get "txtLName". How could txtLName be the active control and yet txtQUOTID
can't be set to invisible because it "Has the focus"?

 

[Dirk Goldgar]

Here's my code:

Private Sub txtFindText_AfterUpdate()
Dim strSearch As String
Dim strQuote As String
Dim lngQuoteID As Long
Dim lngAuthID As Long
Dim rstQuotes As DAO.Recordset

Set rstQuotes = dbLocal.OpenRecordset("tblQuotes")
strSearch = Me.txtFindText

With rstQuotes
.OpenRecordset
.MoveLast
.MoveFirst
Do Until .EOF
strQuote = .Fields("Quote")
If InStr(1, strQuote, strSearch) > 0 Then
lngQuoteID = .Fields("QUOTID")
lngAuthID = .Fields("authorid")
Exit Do
Else
.MoveNext
End If
Loop
.Close
End With
Set rstQuotes = Nothing

If Nz(lngQuoteID, 0) > 0 Then
Me.Tag = lngQuoteID
With DoCmd
.GoToControl "txtAUTHID"
.FindRecord lngAuthID
Me.Refresh
.GoToControl "sfrmQuotes"
Me.Refresh
Me.sfrmQuotes.Form.txtQUOTID.Visible = True
.GoToControl "txtQUOTID"
Me.Refresh
.FindRecord lngQuoteID
Me.SetFocus
Me.txtLName.SetFocus
Debug.Print Me.ActiveControl.Name
Me.Refresh
Me.sfrmQuotes.Form.txtQUOTID.Visible = False
End With
Else
MsgBox "The text you are searching for was not found in tblQuotes." &
vbCrLf & _
"Try a different search string. - The quote may not be in the
table.", _
vbOKOnly, "String Not Found"
End If



End Sub


It works great, insofar as it displays the correct author and quote. But
the
txtQUOTD textbox I use just for searching purposes can't be made invisible
at
the end of the routine. I keep getting a message saying that it can't hide
the control that has the focus. However, the code that sets the focus on
txtLName works and when I enter ?me.activecontrol.name in the debug
window, I
get "txtLName". How could txtLName be the active control and yet txtQUOTID
can't be set to invisible because it "Has the focus"?

As I read it, txtQUOTD is on the subform sfrmQuotes, correct? The subform
and the parent form each have their own active control, the control that has
the focus on that object. Even though you have moved the parent form's
focus to a different control, so that the subform control named "sfrmQuotes"
no longer has the focus main form's focus, the form object displayed by that
subform control still has its focus set to txtQUOTD. Therefore, you can't
hide it.

To make this code work, you would need to add a line moving the focus to
some other control on the subform, before moving the focus to txtLName on
the main form. It would be something like this:

Me.sfrmQuotes.Form.txtQUOTID.Visible = True
.GoToControl "txtQUOTID"
.FindRecord lngQuoteID
Me.sfrmQuotes.Form.SOMEOTHERCONTROL.SetFocus
Me.txtLName.SetFocus
Me.sfrmQuotes.Form.txtQUOTID.Visible = False

That said, I wouldn't go about the search this way, anyway. I don't see any
good reason to show and hide controls, except that you are using the
FindRecord method -- but you don't have to. How about something like this
(air code):

'------ start of revised (air) code ------
Private Sub txtFindText_AfterUpdate()

Dim strSearch As String
Dim lngQuoteID As Long
Dim lngAuthID As Long
Dim rstQuotes As DAO.Recordset

strSearch = Me.txtFindText

Set rstQuotes = dbLocal.OpenRecordset( _
"SELECT QUOTID, authorid FROM tblQuotes " & _
"WHERE Quote Like ""*" & _
Replace(strSearch, """", """""") & "*""", _
dbOpenSnapshot)

With rstQuotes
If Not .EOF Then
lngQuoteID = !QUOTID
lngAuthID = !authorid
End If
.Close
End With
Set rstQuotes = Nothing

If lngQuoteID > 0 Then
Me.Tag = lngQuoteID
With Me.Recordset
.FindFirst "authorid = " & lngAuthID
If Not .NoMatch Then
Me.sfrmQuotes.Form.Recordset.FindFirst _
"QUOTID = " & lngQuoteID
End If
End With
Else
MsgBox _
"The text you are searching for was not found in tblQuotes." & _
vbCrLf & _
"Try a different search string. - The quote may not be in the
table.", _
vbOKOnly, "String Not Found"
End If

End Sub
'------ end of revised (air) code ------

Although it may need tweaking and may bnot be correct in every particular,
it seems to me that something like the above would be much simpler and more
efficient.