Access VBA Can't set focus

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

In my Access VBA I run the following code behind a Combo Box and I get the error
"Can't set focus to Customer_Code"
What am I doing wrong?

Private Sub xcmbCustomer_AfterUpdate()

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

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jorge PaulinoIT Pro/Developer
Top Expert 2008

Commented:
Is the control Customer_Code enabled?
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
You don't need to change the focus.  Just use the Value property of the controls (which is the default):


   Private Sub xcmbCustomer_AfterUpdate()

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

Open in new window


See this article for a more detailed explanation:
Value vs Text Properties of controls
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
You need to get rid of all of the Docmd.SetWarnings False/True commands in your program and add error handling to every procedure.  

Use of docmd.SetWarnings False will eventually bite you in the a@@, especially when you have no error handling in your code.

Dale
Ensure you’re charging the right price for your IT

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

mbizupNerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
>>>  Docmd.SetWarnings False/True

I assume that's being done to suppress Access's "You are about to add N records" warning in the call to oInsert_New_Invoice_Number.  

An alternative way to suppress those warnings is to use CurrentDB.execute instead of OpenQuery or RunSQL, coupled with good error handling as Dale suggested:
CurrentDB.Execute  "INSERT INTO yourTable ... etc", dbFailOnError

Open in new window

Distinguished Expert 2017
Commented:
You use the .text property in the change event to capture the values chracter by character.  In all other places you would use the default prperty which is .value and can be omitted.

These three lines:

    oCustomerCode = Me.xcmbCustomer.Text
    Me.Customer_Code.SetFocus
    Me.Customer_Code.Text = oCustomerCode

Should be:

Me. Me.Customer_Code = Me.xcmbCustomer

Or probably isn't even necessary at all since it would be very unusual to have the same field bound to two controls.

Also, if by chance you have used a naming standard to name the controls differently from the bound field, then you need to use the names appropriately in code.  For example of the control Name for Customer_Code is txtCustomer_Code then Access will insist that you use Me.txtCustomer_Code when referring to the properties of the control and Me.Customer_Code when referring to the bound field.
Murray BrownASP.net/VSTO Developer

Author

Commented:
Thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial