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

 

[joel009]

Excellent idea AceMan!!!
I will try the idea of removing the requery/refresh and see how it flows.

The problem with the empty recordset I havetaken care of by checking the new recordsource to see if it returns row and if not making the detail invisible and not updating the recordset. It seems the focus goes to the detail and won't allow me to redirect.Here is the new mysterious ChangeRecordsource sub:

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
Dim strSQL As String

Dim db As Database
Dim rsNewRecordsource As Recordset

'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
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 rsNewRecordsource = db.OpenRecordset(strSQL)

If rsNewRecordsource.EOF = True And rsNewRecordsource.BOF = True Then
Me.Detail.Visible = False
Me.cmdAddPartToRoute.Visible = False
Else
Forms!frmPartInfo.RecordSource = strSQL
Forms!frmPartInfo.Requery
Forms!frmPartInfo.Refresh
Me.Detail.Visible = True
Me.cmdAddPartToRoute.Visible = True
End If

rsNewRecordsource.Close
Set rsNewRecordsource = Nothing
Set db = Nothing

DoEvents
Me.cmdClose.SetFocus

End Sub

Everything is working fine now, Thanks for the help!!!!!

Joel

 

[joel009]

MAjP and AceMan - as usual you both make me feel like a newbie.

The me.dirty = false solved so many problem with the .text and the .value not matchingmaking the rest of the coding sooo much easier.

Also the key press event and the loop work flawlessly. Much simpler and more effecient than the way I was hacking through it.

Thanks to you both!!!!

Joel

 

[TheAceMan1]

joel009 . . .

Your welcome!

BTW: Don't feel knowledge ... just soak it up.

See Ya! . . . . . .

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

 

[TheAceMan1]

joel009 . . .

This has nothing to do with this thread. For a few years now, I've been working on schema that automatically formats code and/or SQL. I'm near the end fighting with SQL.

Since your latest thread has a decent amount of SQL I decided to run it thru. I had to aligh some of the results but it came out great. The schema took 3:47 to complete. It turned out so good I spent another 2minutes manually making changes/additions ([green]should still run![/green]). Here's the results for your view:

[blue]Private Sub ChangeRecordsource()
   [green]'refresh form from various change events
   'PlantCode, ModelYear, Status, txtSearchPN[/green]
   Dim strSearchPN As String, strPlantCode As String
   Dim strPartStatus As String, strModelYear As String
   Dim db As [purple][b]DAO[/b][/purple].Database, rst As [purple][b]DAO[/b][/purple].Recordset
   Dim [purple][b]SQL[/b][/purple] As String, [purple][b]Cri[/b][/purple] As String, [purple][b]DL[/b][/purple] As String
   
   [purple][b]DL[/b][/purple] = vbNewLine & vbNewLine
   
   [green]'validate fields[/green]
   If Nz(Me.PlantCode, "") = "" Then
       MsgBox "No Value for PlantCode," & [purple][b]DL[/b][/purple] & _
              "Please select a value from the list.", _
              vbOKOnly + vbExclamation, _
              "Empty PlantCode Field."
       Me.cmbPartStatus.SetFocus
       Exit Sub
   End If
   
   If Nz(Me.cmbModelYear, "") = "" Then
       MsgBox "No Value for Model Year." & [purple][b]DL[/b][/purple] & _
              "Please select a value from the list.", _
              vbOKOnly + vbExclamation, _
              "Empty Model Year Field."
       Me.cmbModelYear.SetFocus
       Exit Sub
   End If
   
   [green]'set variables[/green]
   If Nz(Me.txtSearchPN, "") = "" Then
       strSearchPN = [purple][b]""[/b][/purple]
   Else
       strSearchPN = Me.txtSearchPN & "*"
   End If
   
   strPlantCode = Me.cmbPlantCode
   strPartStatus = Nz(Me.cmbPartStatus, "")
   strModelYear = Me.cmbModelYear
   
   [green]'conditions[/green]
   [purple][b]SQL[/b][/purple] = "SELECT PlantCode, PartNumber, DOCK, Storage, Description, " & _
                "PartWeight, Bld_Rate, ValidatedBld_Rate, PackDensity, " & _
                "Container, Length, Width, Height, DataSource, PartStatus, " & _
                "NumberofStations, ModelYear " & _
         "From tblPartInfo "
   
   If strSearchPN = "" Then
      [green]'values PartStatus null[/green]
      If strPartStatus = "" Then
         [purple][b]Cri[/b][/purple] = "WHERE (([PlantCode]='" & strPlantCode & "') AND " & _
                      "([ModelYear]='" & strModelYear & "')) "
      Else
         [purple][b]Cri[/b][/purple] = "WHERE (([PlantCode]='" & strPlantCode & "') AND " & _
                      "([PartStatus]='" & strPartStatus & "') AND " & _
                      "([ModelYear]='" & strModelYear & "')) "
      End If
   Else
       'values PartStatus null
      If strPartStatus = "" Then
         [purple][b]Cri[/b][/purple] = "WHERE (([PlantCode]='" & strPlantCode & "') AND " & _
                      "([ModelYear]='" & strModelYear & "') AND " & _
                      "([PartNumber] LIKE '" & strSearchPN & "')) "
   
      Else
         [purple][b]Cri[/b][/purple] = "WHERE (([PlantCode]='" & strPlantCode & "') AND " & _
                      "([PartStatus]='" & strPartStatus & "') AND " & _
                      "([ModelYear]='" & strModelYear & "') AND " & _
                      "([PartNumber] LIKE '" & strSearchPN & "')) "
      End If
   End If
   
   Set db = CurrentDb
   [purple][b]SQL[/b][/purple] = [purple][b]SQL[/b][/purple] & [purple][b]Cri[/b][/purple] & "ORDER BY DOCK, Description;"
   Set rst = db.OpenRecordset(SQL, [purple][b]dbOpenDynaset[/b][/purple])
   
   [purple][b]If rst.BOF Then[/b][/purple]
       Me.Detail.Visible = False
       Me.cmdAddPartToRoute.Visible = False
   Else
       Forms!frmPartInfo.RecordSource = SQL
       Forms!frmPartInfo.Requery
       Forms!frmPartInfo.Refresh
       Me.Detail.Visible = True
       Me.cmdAddPartToRoute.Visible = True
   End If
   
   Set rst = Nothing
   Set db = Nothing
   
   DoEvents
   Me.cmdClose.SetFocus
        
End Sub[/blue]

I have along way to go and hoping to release a software package at the end of this year. I intended to make it free but too many hours for that.

So what do you think ... Not Bad Ehhhhhhhh!

See Ya! . . . . . .

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

 

[joel009]

AceMan - very clean!

Joel