[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Sub routine problem when used with Detail_Click event and error 2166

Posted on 2011-03-22
5
Medium Priority
?
570 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 58
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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 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 58
ID: 35201932
<<Bascially the click event isn't moving the focus. >>

 BINGO!

JimD.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

649 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