Access VBA code to apply text to a text box being influence by other event code

Hi

The following code is causing the error shown below it. This is no doubt caused by the code further on that I have in there because of an "OK"
and "Cancel" button. What can I do to make this error go away? The SetWarnings(False) doesn't stop it.

Private Sub xcmbCustomer_AfterUpdate()

On Error GoTo EH
    '-----------------------------
    DoCmd.SetWarnings (False)
    Dim oCustomerCode As String
    Me.xcmbCustomer.SetFocus
    oCustomerCode = Me.xcmbCustomer.Text
    Me.Customer_Code.SetFocus
    Me.Customer_Code.Text = oCustomerCode
    Me.xcmbCustomer.SetFocus
    DoCmd.SetWarnings (True)
    '-----------------------------
    Exit Sub
EH:
    MsgBox Err.Description
    
End Sub

Open in new window


1
Private blnSaved As Boolean

Private Sub btnCancel_Click()

On Error GoTo EH
    '-----------------------------
     DoCmd.Close
     MsgBox "Changes discarded!"
    '-----------------------------
    Exit Sub
EH:
    MsgBox Err.Description

End Sub

Private Sub btnNewInvoice_Click()

On Error GoTo EH
    '-----------------------------
    'First go to the last record...
      With Me.RecordsetClone
          .MoveLast
           Me.Bookmark = .Bookmark
      End With
     'Now mimic right click on arrow to create (New) record
      DoCmd.GoToRecord , , acNext 'This acts like the right arrow right on the bottom of the form
    '-----------------------------
    Call oInsert_New_Invoice_Number 'eg 2018-001
    
      Exit Sub
EH:
      MsgBox Err.Description

End Sub

Private Sub btnOK_Click()

On Error GoTo EH
    '-----------------------------
      blnSaved = True
      MsgBox "Changes saved!"
    '-----------------------------
      Exit Sub
EH:
      MsgBox Err.Description

End Sub



Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo EH
   '-----------------------------
    If blnSaved = False Then
        Cancel = True
    End If
   '-----------------------------
    Exit Sub
EH:
    MsgBox Err.Description

End Sub

Private Sub Form_Current()

On Error GoTo EH
    '-----------------------------
      blnSaved = False
    '-----------------------------
    Exit Sub
EH:
    MsgBox Err.Description

End Sub

Open in new window

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MacroShadowCommented:
In Form_Current you are setting blnSaved = False, that is preventing the saving of the record because of the following code in Form_BeforeUpdate:
    If blnSaved = False Then
        Cancel = True
    End If

Open in new window

0
Dale FyeOwner, Developing Solutions LLCCommented:
You never actually saved the form changes:

    '-----------------------------
      blnSaved = True
      MsgBox "Changes saved!"
    '-----------------------------

should be:

    '-----------------------------
      blnSaved = True
      me.dirty = false
      MsgBox "Changes saved!"
    '-----------------------------
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Murray,

 Just as a side comment, this is one of the reasons why you won't see many Access applications with buttons like this.    In general, the more you try to control what Access has built-in, the more problems you'll have.

 You can use the "unbound" approach in Access (no recordsource for the form or controlsource for controls) and have complete control over everything, but if your going to go through that amount of work, you might as well use something else.

 The whole point to Access as a product is that it builds a lot of things in to save you time.  But that is a double edged sword as when you try and insist on doing things in a way contrary to that, you start fighting Access all the way.

 The other thing is that despite many users insisting that they like an explicit save/cancel process, many users actually find that it slows them down.  In the past, I used to have applications like this and have moved away from them over the years.   Part of that decision was that I found it easier to use Access "as is" and not fight with it, but the main reason was that many users when forced to not use a explicit save/cancel process, actually liked it better.

 Some food for thought.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

PatHartmanCommented:
You have another open thread on a similar topic but in that thread you didn't mention that the code you were having a problem with was in the AfterUpdate event of a control.  When you are in the AfterUpdate event of a control, THAT control still has the focus so of course you cannot move focus to it.  That is what the error message is telling you.

I'm with Jim here.  Access is a RAD tool.  If you do not want to take advantage of the RAD features, you should probably be using a different tool.

Rather than forcing the people to use the save button, you can relax your standards.  In the Form's Current and AfterUpdate events you would set the blnSaved to False to indicate that the button hasn't been pressed.  Then in the click event of the button set blnSaved to True.  Finally, in the form's BeforeUpdate event, check the value of blnSaved.  If it is true, allow the process to continue.  If it is false, prompt the user to ask if they really want to save.  If they say yes, continue, otherwise undo the changes and cancel the Update:

Code in BeforeUpdate event:

If blnSaved = True Then
Else
    If Msgbox("You didn't press Save.  If you want to continue to save, press Yes, otherwise press No.", vbYesNo) = vbNo Then
        Cancel = True
        Me.Undo
    End If
End If

This gives you the ability to guide them and train them to use the save button without getting yourself all wrapped up in trying to control everything.  Leave the control to Access.  Go with the flow and do things the "Access" way.  Access takes it personally and will save your data whenever it thinks it needs to.  Let it.  As long as you have validation code in the Form's BeforeUpdate event that finds invalid data, you can prevent bad data from being saved.  That's all the control you need.
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi thanks a lot for the advice
0
PatHartmanCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.