Formatting TextBox 2185 Error 2

[joel009]

I have this bit ofcode to force alpha characters to ucase. It works a few times then gives me a 2185 error "can't reference an object unless it has the focus." I have the code in the Key-Up event and have tried it in the Change event with the same results.

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

Dim intStrLen As Integer
Dim strNewValue As String

'match text and value
Me.cmdClose.SetFocus
Me.txtSearchPN.SetFocus
If Me.txtSearchPN.Text = "" Then
Me.txtSearchPN.Value = ""
Exit Sub
End If

If IsNumeric(Right(Me.txtSearchPN.Text, 1)) = True Then

Else
intStrLen = 1
Do Until intStrLen = Len(Me.txtSearchPN.Text) + 1
If IsNumeric(Mid(Me.txtSearchPN.Text, intStrLen, 1)) = True Then
strNewValue = strNewValue + Mid(Me.txtSearchPN.Text, intStrLen, 1)
Else
strNewValue = strNewValue + StrConv(Mid(Me.txtSearchPN.Text, intStrLen, 1), vbUpperCase)
End If
intStrLen = intStrLen + 1
Loop
Me.txtSearchPN.Value = Left(strNewValue, 10)

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

If Len(Me.txtSearchPN.Value) >= 8 Then
ChangeRecordsource
Me.txtSearchPN.SetFocus
Me.txtSearchPN.SelStart = Len(Me.txtSearchPN.Value) + 1

End If

End Sub

The .text value does not match the .value value at this point. Am I doing it in the wrong event or ????

Thanks for looking. Access 2010 in 2007 mode, XP SP3.

Joel

 

[MajP]

The .text property requires the control to have focus the .value property does not.

 

[joel009]

MajP - As you can see in the key-up event I have set the focus to the control and still I am getting the 2185 error. I am unable (in this event anyway) to set the focus to the control. The control is unbound and the .value and .text values do not match until the After Update event (which seems to happen after the lost focus event).
Am I doing this in the wrong event? Or am I just doing this wrong?

Joel

 

[MajP]

I would do it in the afterupdate. Playing with the focus is always a pain.

 

[MajP]

BTW you can use the me.dirty = false to commit changes at the controls. What you see in the text is the same as the value.

 

[joel009]

MajP - The Afterupdate event happens only on the lostfocus that I can see.Iwant the user to key in alpha characters and convert them to upper case and then return the user to the end of the string. Seems so simple but it is kicking my butt.
In the Afterupdate event I can use the .value reference but I need to return to the text field and have the cursor at the end of the string.It is a Part Number and I want to change the forms recordsource if the Part Number is > 8 characters and I wildcard the end. It seems to work fine until I change the recordsource and then I can't set focus back to the field to set the Selstart.

Joel

 

[MajP]

If I understand the error is only after you change recordsource.
As the you change recordsource there are probably some other events taking place. Try

ChangeRecordsource
doevents
Me.txtSearchPN.SetFocus
Me.txtSearchPN.SelStart = Len(Me.txtSearchPN.Value) + 1

 

[MajP]

So my guess is when you change the recordsource not all the associated events have processed yet. So your next line of code executes
Me.txtSearchPN.SetFocus
But then the cached events finish taking place. As the data loads, the focus then goes to the 1st control in the tab order.
Then your next line executes
Me.txtSearchPN.SelStart = Len(Me.txtSearchPN.Value) + 1
Hence the error
So doevents should let the form finishing reloading before you set focus.

 

[joel009]

MajP - I was suspecting something of that order but couldn't figure out how to locate where the focus was. Even if I tried to set the focus to the object at the command line in breakmode it would not accept it though. I still feel I am doing something wrong though I've gotten a lot further with your advice THANK YOU!
1. Is there a way to trigger the After update event without loosing the focus? Currently in the keyup event I am setting the focus to another button to trigger the Afterupdate event and then returning the focus to the original button after it returns.

It seems I am chasing the .value and .text values. The events I want are being triggered before the .value is what is in the text box. Also running into the same situation with combo boxes. I tigger the ChangeRecordSource sub from their Change events.

Really feel like a newbie on this one but I am used to doing this in Access 2003. Brain fart or ???

I am investigating setting the .value from something other than the .text.

Joel

Joel

 

[MajP]

You cannot trigger the event but as I said you can commit the changes to the control so that the text and value are the same.

When you edit a record you will see a little pencil in the record selector (if you have them). This symbolizes the form is dirty. There are changes not committed to the control. To commit the changes
Me.dirty = false
Then the changes are committed.

test it
on some key event
msgbox ctrlName.text = ctrlName.value
me.dirty = false
msgbox ctrlName.text = ctrlName.value

 

[TheAceMan1]

How are ya joel009 . . .

FYI here's the sequence of events for tabbing into a control, typing the letter a, then tabbing out of the control

[blue]OnEnter
OnGotFocus
OnKeyPress 9 Tab
OnKeyUP

OnKeyDown
OnKeyPress 97 a
OnChange
OnKeyUP

OnKeyDown
BeforeUpdate
AfterUpdate
OnExit
OnLostFocus[/blue]

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

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

joel009 . . .

The bulk of your code is involved in parsing [blue]txtSearchPN.Text[/blue] and converting any lower case letters to upper case, in an [blue]as you type manner[/blue]. You can easily perform this directly with little code. Copy/paste the following in the [blue]On Key Press[/blue] event of [blue]txtSearchPN[/blue]:

[blue]   If KeyAscii > 96 And KeyAscii < 123 Then
      KeyAscii = (KeyAscii And 223)
   End If[/blue]

Be sure to rem out the code in the [blue]On Key Up[/blue] event to prevent interaction.

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

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[joel009]

here is one of my combo's
Private Sub cmbPartStatus_Change()

MsgBox cmbPartStatus.Text = cmbPartStatus.Value
Me.Dirty = False
MsgBox cmbPartStatus.Text = cmbPartStatus.Value


''' Me.cmbPartStatus.Value = Me.cmbPartStatus.Text
If blnFormLoaded = False Then

Else
'call sub to refresh form
ChangeRecordsource
' DoEvents
Me.cmbPartStatus.SetFocus
Me.cmbPartStatus.SelStart = Len(Me.cmbPartStatus.Value) + 1
End If

End Sub

MajP - the msgbox caused an erroron the .text reference that you can't reference the cmbPartStatus.Text because it doesn't have the focus. When I call the sub ChangeRecordsource the .value is not the same as the .text so I'm haveing trouble using the new entry.Feel Like VB 101 and I'm failing or flailing.

Hey AceMan - will investigate!!!

Thanks
Joel

Joel

 

[TheAceMan1]

joel009 . . .

Why are you pinging [blue].text[/blue] against [blue].value[/blue] in the 1st place? ... Please answer this question!

You need to post your infamous [blue]ChangeRecordsource[/blue] routine.

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[joel009]

MajP - The Me.Dirty = false solved a lot of the problems. Thanks.

it appears the problem is related to the new recordsource query not having any values. After that I requery/refresh the from and I can't seem to get Access to believe it already has the focus. If I skip the step to Me.cmbPartStatus.SelStart = 0
The form displays with the entire text selected in the combo (that's why I think the field already had the focus).

I would like the focus to return to the combo like the user just clicked on it. Stumping me for now.

Joel

 

[MajP]

AceMan,
That was probably my fault. He is doing a lot of setting focus and reading the text property, and I suggested just getting the value property. I think the whole concept is fixed with your solution.

 

[joel009]

Still having the problem when the recordsource returns no records.The focus is then not able to be set with setfocus.

Private Sub ChangeRecordsource()

'refresh form from various change events
'PlantCode, ModelYear, Status, txtSearchPN

Dim strSearchPN As String
Dim strPlantCode As String
Dim strPartStatus As String
Dim strModelYear As String

'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 = Nz(Me.txtSearchPN.Value, "")
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 strPartStatus = "" Then
Forms!frmPartInfo.RecordSource = "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
Forms!frmPartInfo.RecordSource = "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
Forms!frmPartInfo.RecordSource = "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
Forms!frmPartInfo.RecordSource = "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

Forms!frmPartInfo.Requery
Forms!frmPartInfo.Refresh
DoEvents
Me.cmdClose.SetFocus

End Sub

I have added the Me.cmdClose.SetFocus in an effort to set it somewhere before it finishes the event:

Private Sub cmbPartStatus_Change()

Me.Dirty = False

If blnFormLoaded = False Then

Else
'call sub to refresh form
ChangeRecordsource

Me.cmbPartStatus.SetFocus
Me.cmbPartStatus.SelStart = 0
End If

End Sub

This gives an error if the recordsouce is empty at Me.cmbPartStatus.SelStart = 0 that you can't set without having the focus. It works up until the returned recordsource is zero records.
Where is the focus? If I step over it and f5 it completes the sub and cmdPartStatus has the focus on the full length of the string. It (Access) appears to have thefocus and not know it only when the recordsource is empty.

Any ideas?and thanks for your time!


Joel

 

[joel009]

Adding that the Me.Dirty = False has taken care of the .text to .value mix up Thank you MajP!!!

AceMan - still checking it out!

This current problem is taking up too much of my time...lol.

Joel

 

[TheAceMan1]

[blue] After that I [blue]requery/refresh[/blue] the from and I can't seem to get Access to believe it already has the focus.[/blue]

Be aware: whenever you write to the [blue]RecordSource[/blue] of a form the form automatically requeries! So there's no need to perform an another.

In case [blue]ChangeRecordsource[/blue] happens to involve a large recordset (delay in loading), you may want to try the following modification of what [blue]MajP[/blue] provided:

[blue]Else
   [green]'call sub to refresh form[/green]
   ChangeRecordsource
      
   Do While Screen.ActiveControl.Name = "cmbPartStatus"
      DoEvents
      Me.cmbPartStatus.SetFocus
   Loop
   
   Me.cmbPartStatus.SelStart = Len(Me.cmbPartStatus.Value) + 1
End If[/blue]

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

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Woops!

Should be:

[blue]Else
   'call sub to refresh form
   ChangeRecordsource
      
   Do While Screen.ActiveControl.Name [red]<>[/red] "cmbPartStatus"
      DoEvents
      Me.cmbPartStatus.SetFocus
   Loop
   
   Me.cmbPartStatus.SelStart = Len(Me.cmbPartStatus.Value) + 1
End If[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]