Solved

Sub routine problem when used with Detail_Click event and error 2166

Posted on 2011-03-22
5
558 Views
Last Modified: 2013-11-27
I have a strange problem were a sub routine operates differently when called from two different events.
I have sProcessPOID that is being called from a keypress event and the detail_click event of the form. Code works fine when called from the keypress event but not the click.

This is a typical PO form and the sub basicly is performing as a lostfocus or exit handler for tbPOID. I'm not using the actual built in events because I don't want the exit code to run in certain situations such as if the user clicked the "Exit" button while textbox has focus.

There are 3 ways of exiting the texbox, Tab, Return, and clicking into the Details portion of the form where I have a tab control. When the user does this tbPOID can have 3 possible conditions:

It's Null or Empty - msgbox the user to enter a value
"P" this a placeholder saying we're starting a new record and we'll get the next sequencial number on Save.
Any other value - go see if this is a existing PO, if so goto that record. If not msgbox the user and make sure they want to start a new record using the value entered.

When called from the click event the value of tbPOID seems to be magically set to "P" which causes the error 2166. I'm fairly sure the value isn't really getting changed to "P".

I can enter existing PO "P2001", trigger the click event, it fails,  click "OK" on the error message, the textbox gets focus again with "P2001" still showing so I  Tab/Return out without changing anything and the code runs as expected.

Here is the results of my debug:

Detail_Click() Results
-------------------------
--tbPOID value was "P"
Detail Click
tbPOID = P
Post Processing

--tbPOID value was Empty ""
Detail Click
tbPOID = P
Post Processing
Error 2166: You can't lock a control while it has unsaved changes.

--tbPOID value was "P2001" a valid existing record
Detail Click
tbPOID = P
Post Processing
Error 2166: You can't lock a control while it has unsaved changes.

tbPOID_KeyPress Results
-------------------------
--tbPOID value was "P"
tbPOID = P
Post Processing

--tbPOID value was Empty ""
tbPOID =
Null Value
(Message box code ran fine)

--tbPOID value was "P2001" a valid existing record
tbPOID = P2001
Match Found
Post Processing

I even cut/pasted the exact same code into a button event and it worked perfect so something is happening in conjuction with the detail click event but I don't have a clue what. TIA
-Ralph
Private Sub tbPOID_KeyPress(KeyAscii As Integer)
   On Error GoTo err_Handler
   LimitFieldSize KeyAscii, ELookup("LengthLimit", "tblVoucher", "Voucher = 'P'")
   KeyAscii = UpperCaseText(KeyAscii)
   Select Case KeyAscii
   Case 9, 13   'Tab or Return ie. Exiting the Textbox so process content before moving focus
      sProcessPOID
   End Select
exit_Handler:
   Exit Sub
err_Handler:
   Call LogError(Err.Number, Err.Description, Me.Form.Name & " tbPOID_KeyPress", , True)
   Resume exit_Handler
End Sub

Private Sub Detail_Click()
   On Error GoTo err_Handler
   'If the POID box is unlocked we need to process the contents before moving focus
   If Not Me.tbPOID.Locked Then
      Debug.Print "Detail Click"
      sProcessPOID
   End If

exit_Handler:
   Exit Sub
err_Handler:
   Call LogError(Err.Number, Err.Description, Me.Form.Name & " Detail_Click", , True)
   Resume exit_Handler
End Sub

Private Sub sProcessPOID()
   On Error GoTo err_Handler
   Dim strMsg, iResponse As String
   Dim rs As Recordset

   Debug.Print "tbPOID = " & Me.tbPOID

   'Deal with nulls and empty then evaluate real values
   If Nz(Me.tbPOID, "") = "" Then
      Debug.Print "Null Value"
      iResponse = MsgBox("Please enter a PO number", vbExclamation + vbOKOnly, "Enter PO")
      Exit Sub
   End If

   If Me.tbPOID <> "P" Then
      Set rs = Forms!frm_PO.RecordsetClone
      rs.FindFirst "[POID] = '" & Me.tbPOID & "'"
      If rs.NoMatch Then
         Debug.Print "No Match"
         strMsg = "You entered '" & Me.tbPOID & "' as a new PO." & _
         vbCrLf & "Is this correct?"
         iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "New Order?")
         If iResponse = vbNo Then
            With Me.tbPOID
               .SetFocus
               .SelStart = 0
               .SelLength = Len(.Text)
            End With
            Exit Sub
         End If
      Else
         Debug.Print "Match Found"
         Me.Undo
         Forms!frm_PO.Bookmark = rs.Bookmark
         rs.Close
      End If
   End If

   Debug.Print "Post Processing"
   sLockPOID
   Me.TabPO.Enabled = True
   Me.cbCompany.SetFocus

exit_Handler:
   Exit Sub
err_Handler:
   Call LogError(Err.Number, Err.Description, Me.Form.Name & "Sub: sProcessPOID", , True)
   Resume exit_Handler
End Sub

Private Sub sLockPOID()
   Me.tbPOID.Locked = True
   Me.tbPOID.BackColor = RGB(230, 228, 226)
End Sub

Open in new window

PO-Form.png
0
Comment
Question by:Raland9966
  • 3
  • 2
5 Comments
 
LVL 57
ID: 35197187
Ralph,

  It's hard to tell from your description alone what's going on.  When you start playing around within Access events on a bound form, you can run into all kinds of issues. The further you go in trying to control things, the more issues you'll have.  Given the extent you going, I would not be using a bound forml.

  What I would suggest is putting a STOP or a Debug.Print "<routine name>" at the top of each procedure, along with a debug.print me.dirty, the value of the PO ID, etc.  Then execute and test.   Looks like you've tried to do this to a point in order to debug, but I would carry it a bit further.

  Keep in mind that one difference between the two situations is that the control has the focus and the other it does not.

  Failing coming up with anything, if you can do up a small sample DB and post (or e-mail to me directly if you don't want to post it - e-mail is in my profile), I'll have a look at it.

JimD.
0
 

Author Comment

by:Raland9966
ID: 35199648
Actually I have this working in my curreny database on an unbound form. This is my attempt to make it work with a bound form. I attached a slightly stripped down copy of the database. I have debug print statements throughout. Best I can determine is that there is an Undo being performed in conjunction with the Detail_Click event.
If I set the value to "P" when clicking the clear button, the detail click event debug always shows the value as "P" no matter what I enter. If I leave tbPOID null on clear then the debug comes back as null no matter what the current value is.
I may simply remove the click event so the only way to exit the texbox is through Tab/Return or clicking on a button. I find this to be very odd, I can assign all kinds of other processes to the Detail_click event and they complete as expected but it completely fails reading the value of a textbox.
Check out the sample and let me know if you have any questions. thanks!
Rip2.6.zip
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 35199999
<<but it completely fails reading the value of a textbox.>>

  I missed that.  Try using Me.tbPOID.text in your checks.

JimD.
0
 

Author Closing Comment

by:Raland9966
ID: 35201840
Using the .Text for tbPOID did the trick. In reading the help file on the difference between .Text and .Value of a control it's a wonder I never ran into this situation before.
I did further testing. The .Value is updated before the keypress event but isn't updated before the Click event is triggered. Bascially the click event isn't moving the focus. Thanks for the help.
-Ralph
0
 
LVL 57
ID: 35201932
<<Bascially the click event isn't moving the focus. >>

 BINGO!

JimD.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now