Set focus error 2110

[ErinL]

Hello everyone -

Can anyone tell me why I can't set focus on a text box after I update a combo box?

I have a form (frmLeanAssignment) with a combo box (AssignedTo). This combo box is set up so the user can select mulitple names from a list in the drop down box by checking a box next to the name (what I mean by this is there is a lookup reference for this field in the table with the "allow multiple values" property set to yes). When I have selected all the necessary names and I click "OK" I want the focus to go to a text box (CompletionNeededByTextBox).

I have the following code on the AfterUpdate event of the combo box:

Private Sub AssignedTo_AfterUpdate()
Me.CompletionNeededByTextBox.SetFocus
End Sub

When I select my names and click "OK", I get an error that says:

"Run-time error '2110': Microsoft Office Access can't move the focus to the control CompletionNeededByTextBox"

If I click Debug from this message, it opens Visual Basic and highlights the one line of code I have above.

If I click End from this message, it goes back to the form and I am able to manually click on CompletionNeededByTextBox and it does what I want.

There is probably something simple I am overlooking but it's got me tearing my hair out.

Thanks in advance for your time.

Oops...I forgot. I am using Access 2007.

 

[John Big Booty]

Is the Enabled property of CompletionNeededByTextBox set to False by any chance?

 

[John Big Booty]

Is the CompletionNeededByTextBox on a Sub-Form?

 

[ErinL]

CompletionNeededByTextBox is enabled and it is not on a subform.

 

[John Big Booty]

Can you post a copy of your DB?

 

[vbaInet]

* Are you sure that's the name of the textbox? Perhaps it's the name of the name or caption of the label attached to the textbox.
* Is the textbox Visible?

 

[ErinL]

Yes that is the name of the text box. I originally had "CompletionNeededBy" as the name. In my research of this problem, I saw someone write that if the name of the text box is the same as the control source "Access may become confused" so I changed the name adding "TextBox" to the end but it didn't make a difference.

 

[John Big Booty]

Can you post a copy of your DB?

If you post a copy of the DB we'll have a better chance of figuring out what's happening.

 

[F.I.G.]

If you post a copy of the DB we'll have a better chance of figuring out what's happening.

What is the out come of this thread because I have a similar problem. What I try to achieve is when the user makes a selection from a combo box "LoanDocListID", I need "RequestDate", a text box, can't be blank. I achieve so, I insert following code in the on change event, unfortunately nothing happens. What did I do wrong again? Thank you all in advance.

Private Sub cboLoanDocListID_Change()
'CHECK IF THERE IS A REQUEST DATE ENTERED
If IsNull(RequestDate) Then
MsgBox "No Request Date Provided!!!"
Exit Sub
End If

cboLoanDocListID = Null
RequestDate.SetFocus


'CHECK IF THERE IS A TRANSACTION TYPE SELECTED
If IsNull(cboTransactionType) Then
MsgBox "You did not selected transaction type"
Exit Sub
End If

cboLoanDocListID = Null
cboTransactionType.SetFocus
cboTransactionType.Dropdown

End Sub

 

[missinglinq]

A couple of things, F.I.G.:

To be honest, I'm a little confused by much of your code, as given. In the cboLoanDocListID_Change event, you've set cboLoanDocListID back to Null, not once but twice! Aside from the fact that you've done this twice, you've done it in the OnChange event, before the Value of the the Control has been saved/committed! Resetting a Control's Value like this needs to be done after the Value is saved, usually in the Control's AfterUpdate event.

Secondly, users frequently have different individual routines that they use in entering data. Some can look over a hard copy form, take in all of the data that needs to be input, and then enter it in the Access Form, going from Control to Control in the order you've laid it out in. Others will move through the hard copy, entering each bit of data as they come to it in the hard copy, totally disregarding the Tab Order that you've laid out. Forcing them to enter data in a particular manner is very user-unfriendly. Because of this, Validation of one Control, based on the state/value of another Control, should be done in the Form_BeforeUpdate event.

Assuming that I have read your purpose here correctly, and that

• You want to set cboLoanDocListID back to Null
• If a selection has been made in the cboLoanDocListID Combobox, you want to insure that a TransactionType has been selected and that a RequestDate has been entered

something like this would be appropriate.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Nz(Me.cboLoanDocListID, "") <> "" Then

[COLOR="Red"]'CHECK IF THERE IS A REQUEST DATE ENTERED[/COLOR]
 If Nz(Me.RequestDate, "") = "" Then
   Cancel = True
   MsgBox "No Request Date Provided!!!"
   RequestDate.SetFocus
   Exit Sub
 End If

[COLOR="Red"]'CHECK IF THERE IS A TRANSACTION TYPE SELECTED[/COLOR]
 If Nz(Me.cboTransactionType, "") = "" Then
   Cancel = True
   MsgBox "You did not select a transaction type!!!"
   cboTransactionType.SetFocus
   cboTransactionType.Dropdown
   Exit Sub
 End If

End If

End Sub

Private Sub cboLoanDocListID_AfterUpdate()
  cboLoanDocListID = Null
End Sub

Under certain conditions, 'empty' Controls, rather than being Null, can actually contain Zero-Length Strings ( "" ) so when trying to test for an 'empty' Control it's better to check for both Null and a ZLS. There are several ways to do this, one being

If Nz(Me.ControlName, "") = "" Then

If I've misread your intentions here, post back with a correction.

Linq ;0)>

 

[F.I.G.]

Hi Linq, Thank you for getting back,

I am learning Access and my knowledge is very limited, currently I am trying to build my FIRST data base. Due to my limited knowledge I understand that my data base won't be efficience as suppose to be. I am very thankfull all of you in this forum for taking to explain me what I did wrong and how should be done.

Your assumption is correct, whenever a selection has been made in the cboLoanDocListID Combobox, I want to insure that a TransactionType has been selected and that a RequestDate has been entered.

My apology not been very explicite in my description. I have a countinous form which on the top of the form has 2 unbound boxes (TransactionType and RequestDate), info from those fields will serve as default value for other fields.

Your suggestion only solve the problem partially, when user go to comply with msgbox, changes made will only after new records, but not for those which user already made a selection in the cboLoanDocListID Combobox.

My intend if TransactionType or RequestDate is missing info, I want to reset cboLoanDocListID back to a new record (untouch).

I hope attached print screen will explain a bit better.
Again thank you for your time.
Regards,
Van

 

[missinglinq]

Sorry, but screen shots seldom mean anything to anyone other than the sender.

It really makes little sense, to me, to have this kind of data Unbound. I've been doing this for a fairly long time, as well as answering questions on this and half a dozen other Access forums, and I simply cannot image a situation when the type of transaction and request date for this kind of thing would not be part of each record.

Perhaps someone else can grasp this situation better and give you a helping hand.

Linq ;0)>

 

[F.I.G.]

Hi Linq,

They do, but having those unbound control on the top of the form this will allow user to enter one time only and will eliminate the repetitiveness for the requirement for each record. Information from those fields will automatically feed as default value for the lower section of the form. Hope this revised print screen will give you a beter idea.

 

[missinglinq]

...those unbound control on the top of the form this will allow user to enter one time only and will eliminate the repetitiveness for the requirement for each record...

You can easily set the DefaultValue for Fields dynamically without going thru all that song and dance!

You can use the AfterUpdate event of the Control holding your data to set the DefaultValue for the Field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each new record.

Private Sub RequestDate_AfterUpdate()
   Me.RequestDate.DefaultValue = """" & Me.RequestDate.Value & """"
End Sub

Private Sub TransactionType_AfterUpdate()
   Me.TransactionType.DefaultValue = """" & Me.TransactionType.Value & """"
End Sub

Just make sure the Control names I've used as are correct.

Linq ;0)>

 

[F.I.G.]

Thank you for your time and explanation!



I learned something new. Again thanks


Van

 

[missinglinq]

Glad we could help!

Linq ;0)>

 

[F.I.G.]

Private Sub RequestDate_AfterUpdate()
Me.RequestDate.DefaultValue = """" & Me.TxtBoxRequestDate.Value & """"
End Sub

Private Sub TransactionType_AfterUpdate()
Me.TransactionType.DefaultValue = """" & Me.cboTransactionType.Value & """"
End Sub

Linq ;0)>

Hi Linq,

What is the meanning of "Method or data member not found"?

Your suggested code works fine for the text box, but I keep getting an error above after making a selection in combo box. The combo box contain 2 columns, but the column for ID number is hidden. What did I do wrong?
Is it because, I am trying feeding from one combo box to another combo box?


Please help.
Regards,
Van

 

[missinglinq]

Just for the enlightenment of others who may pass by here, in their search for truth the error 'Method or data member not found' generally means that Access cannot find a Control that has been referenced in code, or does not recognize some other entity, such as a Property. For instance,

Me.ControlName.Designation = "Yada Yada Yada"

would pop this error because there is no Property named Designation.

I suspect that the problem in this case is that, in both code bits, you're referring to the Textboxes as RequestDate in one case and TxtBoxRequestDate in another! Likewise, you have TransactionType and cboTransactionType!

In each case, one of the names used is incorrect! The devil, as they say, is in the details!

Linq ;0)>

 

[F.I.G.]

You can easily set the DefaultValue for Fields dynamically without going thru all that song and dance!

You can use the AfterUpdate event of the Control holding your data to set the DefaultValue for the Field. From that time forward, until you either manually change the data or close your form, the data will be entered automatically in each new record.

Private Sub RequestDate_AfterUpdate()

[B]Me.RequestDate.DefaultValue = """" & Me.RequestDate.Value & """"[/B]
[B]End Sub[/B]

Private Sub TransactionType_AfterUpdate()

[B]Me.TransactionType.DefaultValue = """" & Me.TransactionType.Value & """"[/B]
[B]End Sub[/B]

Just make sure the Control names I've used as are correct.

Linq ;0)>

Hi Linq,

How can I modify your suggested code so that values in the unbound boxes not only update new record but also those are dirty.

Thank you.
Van