Screen.ActiveControl.Name Problem giving me Error 2474.

[joel009]

I am having a problem with a forms behavior. I have a Part Munber search text box that, when there are no rows returned from the search, makes the form detail section invisible. The problem is that when there are no rows returned, the Detail section is made invisible but I can't seem to get the focus back to the txtSearchPN. Any help/guidance is appreciated. My code -

Private Sub txtSearchPN_KeyUp(KeyCode As Integer, Shift As Integer)

On Error GoTo ErrorHandler

Me.Dirty = False

If IsNull(Me.txtSearchPN.Value) = True Then
ChangeRecordsource
ElseIf Len(Me.txtSearchPN.Value) >= 4 Then
ChangeRecordsource
End If

DoEvents

Do While Screen.ActiveControl.Name <> "txtSearchPN"
DoEvents
Me.txtSearchPN.SetFocus
Loop

If IsNull(Me.txtSearchPN.Value) = True Then
'do nothing
Else

Me.txtSearchPN.SelStart = Len(Me.txtSearchPN.Value) + 1
End If

ErrorHandler:
Select Case Err.Number
Case 0
'do nothing
Case 2110 'error generated if object does not have focus
Resume Next
Case Else
MsgBox "txtSearchPN_KeyUp Error" & vbCr & vbLf & "Record error number and description" _
& vbCr & vbLf & "Error Number = " & Err.Number _
& vbCr & vbLf & "Error Desc. = " & Err.Description, vbOKOnly + vbExclamation, "txtSearchPN_KeyUp Error"
End Select

End Sub


Private Sub ChangeRecordsource()

'refresh form from txtSearchPN change events

Dim strSearchPN As String
Dim strPlantCode As String
Dim strModelYear As String
Dim strSQL As String

'set variables
If Nz(Me.txtSearchPN.Value, "") = "" Then
strSearchPN = ""
Else
strSearchPN = Me.txtSearchPN.Value & "*"
End If

strPlantCode = Me.txtPlantCode.Value

strModelYear = Me.txtModelYear.Value

'conditions
If strSearchPN = "" Then
strSQL = "SELECT tblPartDumpImport.PART, tblPartDumpImport.DOCK, tblPartDumpImport.DESCRIPTION, tblPartDumpImport.BLD_RATE," _
& " tblPartDumpImport.PARTWT, tblPartDumpImport.CONTAINER, tblPartDumpImport.Supplier, tblKanBanImport.ROUTE," _
& " tblKanBanImport.STATION, tblKanBanImport.PACKSIZE, tblPartInfo.PartStatus" _
& " FROM (tblPartInfo RIGHT JOIN tblPartDumpImport ON (tblPartInfo.PartNumber = tblPartDumpImport.PART)" _
& " AND (tblPartInfo.PlantCode = tblPartDumpImport.PLANT))" _
& " LEFT JOIN tblKanBanImport ON (tblPartDumpImport.PLANT = tblKanBanImport.PLANT)" _
& " AND (tblPartDumpImport.BasePart = tblKanBanImport.BasePart)" _
& " WHERE (((tblPartDumpImport.PLANT)=[Forms]![frmMainMenu]![cmbPlantName])" _
& " AND ((tblPartDumpImport.ModelYear)=[Forms]![frmMainMenu]![cmbModelYear]));"

Else
strSQL = "SELECT tblPartDumpImport.PART, tblPartDumpImport.DOCK, tblPartDumpImport.DESCRIPTION, tblPartDumpImport.BLD_RATE," _
& " tblPartDumpImport.PARTWT, tblPartDumpImport.CONTAINER, tblPartDumpImport.Supplier, tblKanBanImport.ROUTE," _
& " tblKanBanImport.STATION, tblKanBanImport.PACKSIZE, tblPartInfo.PartStatus" _
& " FROM (tblPartInfo RIGHT JOIN tblPartDumpImport ON (tblPartInfo.PartNumber = tblPartDumpImport.PART)" _
& " AND (tblPartInfo.PlantCode = tblPartDumpImport.PLANT))" _
& " LEFT JOIN tblKanBanImport ON (tblPartDumpImport.PLANT = tblKanBanImport.PLANT)" _
& " AND (tblPartDumpImport.BasePart = tblKanBanImport.BasePart)" _
& " WHERE (((tblPartDumpImport.PLANT)=[Forms]![frmMainMenu]![cmbPlantName])" _
& " AND ((tblPartDumpImport.PART) LIKE " & "'" & strSearchPN & "'" & ")" _
& " AND ((tblPartDumpImport.ModelYear)=[Forms]![frmMainMenu]![cmbModelYear]));"
End If

Me.RecordSource = strSQL

Debug.Print Screen.ActiveForm.Name
Debug.Print Screen.Application.Name
' Debug.Print Screen.ActiveControl.Name

If Me.Recordset.RecordCount <> 0 Then
Me.Detail.Visible = True
Else
Me.Detail.Visible = False
End If

Debug.Print Screen.ActiveForm.Name
Debug.Print Screen.ActiveControl.Name


End Sub

Everything works fine until I enter somethiing in txtSearchPN that returns no records, then the detail section is hidden but I can not set the cursor or the focus back to txtSearchPN. Is it because the object that last had the focus was hidden?
Stumped - I have 2 forms with same problem now.

Any help is greatly appreciated.

Joel

 

[dhookom]

You can't set the focus in code to the control that already has the focus. It seems you might be attempting to do so. You could try determine if the current control already has the focus and if not, set the focus.

Duane
Hook'D on Access
MS Access MVP

 

[joel009]

Duane - I thought that was what I was doing with the routine:
Do While Screen.ActiveControl.Name <> "txtSearchPN" DoEvents Me.txtSearchPN.SetFocus Loop

I can check in the immediate window with Debug.Print Screen.ActiveControl.Name and Debug.Print Screen.ActiveForm.Name gives me the correct form,
even when it says the Active Control is txtSearchPN, it will error out on:
Me.txtSearchPN.SelStart = Len(Me.txtSearchPN.Value) + 1
with Error 2185 "You can't reference a property or method of a control unless it has the focus."
It only happens when the Detail section is set to visible = false, even if I do not set the Detail section visible = False I get the same Error 2185.

Is using the Screen.ActiveControl.Name not giving me the control that has the focus?

Joel

 

[dhookom]

Do you realize the "Value" property of a control is not updated during the key up event?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya joel009 . . .

[blue]When you write to the RecordSoure of a form, [purple]the form automatically requeries.[/purple][/blue]

This also means the focus will automatically change to the first control in the [blue]Tab Order[/blue] of the detail section. I believe this is exactly what happens as shown below:

[blue]Me.RecordSource = strsql [green][b]'Focus moves to 1st control in
                         'the Tab Order of the Detail Section![/b][/green]
    
   Debug.Print Screen.ActiveForm.Name
   Debug.Print Screen.Application.Name
   'Debug.Print Screen.ActiveControl.Name
   
   If Me.Recordset.RecordCount <> 0 Then
       Me.Detail.Visible = True
   Else
       Me.Detail.Visible = False [red][b]'Why no error here!
                                 'You can't hide a control while
                                 'it has the focus![/b][/red]
   End If[/blue]

As I've shown, I don't understand why no error is generated. There's also the situation of where does the focus go if the detail was previously hidden and you enter follow-up data that still returns no records.

I'll have to get back to this in the mourning ... time for bed.

[blue]Your Thoughts? . . .[/blue]



See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[joel009]

Hi Duane - I am not trying to change the value of the text box. Are you saying the Me.Dirty = False will not work while in the KeyUp event?
AceMan1 - How are you? I realized the focus was being switched with the Detail section, the first in the tab order. That is why -
Do While Screen.ActiveControl.Name <> "txtSearchPN"
DoEvents
Me.txtSearchPN.SetFocus
Loop

Even though it sets the focus to the txtSearchPN, when it gets to -
If IsNull(Me.txtSearchPN.Value) = True Then
'do nothing
Else
Me.txtSearchPN.SelStart = Len(Me.txtSearchPN.Text) + 1
End If

it throws a 2185 error.

The txtSearchPN is unbound and in the Form Header, I don't have any problems with hiding or unhidng the Detail Section, only in reliably returning focus to txtSearchPN and then setting the cursor to the end of the existng text.
Access will tell me the Active Control is txtSearchPN but will still give the 2185 error - what's up with that?
So far it seems it only happens when the detail section is invisible.

Joel

 

[dhookom]

I would try set the focus to another control and then back to where you want it.

Duane
Hook'D on Access
MS Access MVP

 

[joel009]

Duane - tried that, no change in behavior.

Joel

 

[dhookom]

IMO, I would find a different solution based on your requirements and move on. I don't have the time to recreate your environment to do any testing.

If you aren't ready to give up, I would consider using the Text property and get rid of the me.dirty.

I only have time to make WAGs in the dark ;-)

Duane
Hook'D on Access
MS Access MVP

 

[joel009]

I find the .Text Property too unreliable. I finally figured out something that works but not sure about it's elegance. Silly thing is I have one form that works beatifully with hiding the detail section and 2 other forms that will not. Darned if I can figure out why but here is what I did for the other forms.

Private Sub txtSearchPN_KeyUp(KeyCode As Integer, Shift As Integer)

On Error GoTo ErrorHandler

If IsNull(Me.txtSearchPN.Value) = True Then
ChangeRecordsource
ElseIf Len(Me.txtSearchPN.Value) >= 4 Then
ChangeRecordsource
End If

Me.cmdClose.SetFocus
Me.txtSearchPN.SetFocus
Me.txtSearchPN.SelStart = Len(Nz(Me.txtSearchPN.Value, 0)) + 1

ErrorHandler:
Select Case Err.Number
Case 0
'do nothing
Case 2185 'error generated if object does not have focus
Resume Next
Case Else
MsgBox "txtSearchPN_KeyUp Error" & vbCr & vbLf & "Record error number and description" _
& vbCr & vbLf & "Error Number = " & Err.Number _
& vbCr & vbLf & "Error Desc. = " & Err.Description, vbOKOnly + vbExclamation, "txtSearchPN_KeyUp Error"
End Select

End Sub

Private Sub ChangeRecordsource()

'refresh form from various change events
'PlantCode, ModelYear, Status, txtSearchPN
On Error GoTo ErrorHandler

Dim db As Database
Dim rsTemp As Recordset
Dim strSearchPN As String
Dim strPlantCode As String
Dim strPartStatus As String
Dim strModelYear As String
Dim strSQL As String

Me.Dirty = False

'clear cmbselectdock
Me.cmbSelectDOCK = ""
Me.cmdArchiveByDock.Visible = False

'validate fields
If Me.PlantCode.Value = "" Then
MsgBox "No Value for PlantCode," & vbCrLf & vbCrLf & "Please select a value from the list." _
, vbOKOnly + vbExclamation, "Empty PlantCode Field."
Me.cmbPartStatus.SetFocus
Exit Sub
End If

If Me.cmbModelYear.Value = "" Then
MsgBox "No Value for Model Year." & vbCrLf & vbCrLf & "Please select a value from the list." _
, vbOKOnly + vbExclamation, "Empty Model Year Field."
Me.cmbModelYear.SetFocus
Exit Sub
End If

'set variables
If Nz(Me.txtSearchPN.Value, "") = "" Then
strSearchPN = ""
Else
strSearchPN = Me.txtSearchPN.Value & "*"
End If

strPlantCode = Me.cmbPlantCode.Value

strPartStatus = Nz(Me.cmbPartStatus.Value, "")

strModelYear = Me.cmbModelYear.Value

'conditions
If strSearchPN = "" Then
'values PartStatus null
If Nz(Forms!frmPartInfo!cmbPartStatus.Value, "") = "" Then
strSQL = "SELECT tblPartInfo.PlantCode, tblPartInfo.PartNumber, tblPartInfo.DOCK, tblPartInfo.Storage, tblPartInfo.Description," _
& " tblPartInfo.PartWeight, tblPartInfo.Bld_Rate, tblPartInfo.ValidatedBld_Rate," _
& " tblPartInfo.PackDensity, tblPartInfo.Container, tblPartInfo.Length, tblPartInfo.Width," _
& " tblPartInfo.Height, tblPartInfo.DataSource, tblPartInfo.PartStatus, tblPartInfo.NumberofStations, tblPartInfo.ModelYear" _
& " From tblPartInfo" _
& " WHERE (((tblPartInfo.PlantCode)=" & "'" & strPlantCode & "'" & ")" _
& " AND ((tblPartInfo.ModelYear)=" & "'" & strModelYear & "'" & "))" _
& " ORDER BY tblPartInfo.DOCK, tblPartInfo.Description;"

Else
strSQL = "SELECT tblPartInfo.PlantCode, tblPartInfo.PartNumber, tblPartInfo.DOCK, tblPartInfo.Storage, tblPartInfo.Description," _
& " tblPartInfo.PartWeight, tblPartInfo.Bld_Rate, tblPartInfo.ValidatedBld_Rate," _
& " tblPartInfo.PackDensity, tblPartInfo.Container, tblPartInfo.Length, tblPartInfo.Width," _
& " tblPartInfo.Height, tblPartInfo.DataSource, tblPartInfo.PartStatus, tblPartInfo.NumberofStations, tblPartInfo.ModelYear" _
& " From tblPartInfo" _
& " WHERE (((tblPartInfo.PlantCode)=" & "'" & strPlantCode & "'" & ")" _
& " AND ((tblPartInfo.PartStatus)=" & "'" & strPartStatus & "'" & ")" _
& " AND ((tblPartInfo.ModelYear)=" & "'" & strModelYear & "'" & "))" _
& " ORDER BY tblPartInfo.DOCK, tblPartInfo.Description;"
End If
Else
'values PartStatus null
If strPartStatus = "" Then
strSQL = "SELECT tblPartInfo.PlantCode, tblPartInfo.PartNumber, tblPartInfo.DOCK, tblPartInfo.Storage, tblPartInfo.Description," _
& " tblPartInfo.PartWeight, tblPartInfo.Bld_Rate, tblPartInfo.ValidatedBld_Rate," _
& " tblPartInfo.PackDensity, tblPartInfo.Container, tblPartInfo.Length, tblPartInfo.Width," _
& " tblPartInfo.Height, tblPartInfo.DataSource, tblPartInfo.PartStatus, tblPartInfo.NumberofStations, tblPartInfo.ModelYear" _
& " From tblPartInfo" _
& " WHERE (((tblPartInfo.PlantCode)=" & "'" & strPlantCode & "'" & ")" _
& " AND ((tblPartInfo.ModelYear)=" & "'" & strModelYear & "'" & ")" _
& " AND ((tblPartInfo.PartNumber) LIKE " & "'" & strSearchPN & "*'" & "))" _
& " ORDER BY tblPartInfo.DOCK, tblPartInfo.Description;"

Else
strSQL = "SELECT tblPartInfo.PlantCode, tblPartInfo.PartNumber, tblPartInfo.DOCK, tblPartInfo.Storage, tblPartInfo.Description," _
& " tblPartInfo.PartWeight, tblPartInfo.Bld_Rate, tblPartInfo.ValidatedBld_Rate," _
& " tblPartInfo.PackDensity, tblPartInfo.Container, tblPartInfo.Length, tblPartInfo.Width," _
& " tblPartInfo.Height, tblPartInfo.DataSource, tblPartInfo.PartStatus, tblPartInfo.NumberofStations, tblPartInfo.ModelYear" _
& " From tblPartInfo" _
& " WHERE (((tblPartInfo.PlantCode)=" & "'" & strPlantCode & "'" & ")" _
& " AND ((tblPartInfo.PartStatus)=" & "'" & strPartStatus & "'" & ")" _
& " AND ((tblPartInfo.ModelYear)=" & "'" & strModelYear & "'" & ")" _
& " AND ((tblPartInfo.PartNumber) LIKE " & "'" & strSearchPN & "*'" & "))" _
& " ORDER BY tblPartInfo.DOCK, tblPartInfo.Description;"
End If

End If

Set db = DBEngine.Workspaces(0).Databases(0)
Set rsTemp = db.OpenRecordset(strSQL)

If rsTemp.EOF = True And rsTemp.BOF = True Then
Me.cmdAddPartToRoute.Visible = False
Me.txtPlantCode.Visible = False
Me.txtPartNumber.Visible = False
Me.txtDescription.Visible = False
Me.txtDOCK.Visible = False
Me.txtStorage.Visible = False
Me.txtPartWeight.Visible = False
Me.txtBLD_Rate.Visible = False
Me.txtPackDensity.Visible = False
Me.txtContainer.Visible = False
Me.txtLength.Visible = False
Me.txtWidth.Visible = False
Me.txtHeight.Visible = False
Me.txtDataSource.Visible = False
Me.cmbPartStatusDetail.Visible = False
Else
Forms!frmPartInfo.RecordSource = strSQL
Me.cmdAddPartToRoute.Visible = True
Me.txtPlantCode.Visible = True
Me.txtPartNumber.Visible = True
Me.txtDescription.Visible = True
Me.txtDOCK.Visible = True
Me.txtStorage.Visible = True
Me.txtPartWeight.Visible = True
Me.txtBLD_Rate.Visible = True
Me.txtPackDensity.Visible = True
Me.txtContainer.Visible = True
Me.txtLength.Visible = True
Me.txtWidth.Visible = True
Me.txtHeight.Visible = True
Me.txtDataSource.Visible = True
Me.cmbPartStatusDetail.Visible = True
End If

rsTemp.Close
Set rsTemp = Nothing
Set db = Nothing

ErrorHandler:
Select Case Err.Number
Case 0
'do nothing
Case Else
Set rsTemp = Nothing
Set db = Nothing
End Select


End Sub

It works but I'm still confused, but that has never slowed me down yet...

Thanks for the help.

Joel