Solved

Sub routine problem when used with Detail_Click event and error 2166

Posted on 2011-03-22
5
560 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

929 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

10 Experts available now in Live!

Get 1:1 Help Now