Text property of Combo in Form Footer (Access 2013) (1 Viewer)

Simon_C · Jun 7, 2019

For a long while there's been a bug in Access whereby the Text property of a ComboBox or TextBox control is inaccessible, even when the control in question currently has the focus, if the control is in the form footer (or header too, I think) and the detail part of the form is currently displaying no records. The error raised is 2185, “You can’t reference a property or method for a control unless the control has the focus.”

I was just wondering if anyone's come up with a decent workaround for this issue.

What I'm hoping to do with a ComboBox in a particular subform is to implement some functionality along roughly the same lines as Allen Browne’s "Combos with Tens of Thousands of Records".

Ideally I would prefer to have the various input controls (including the ComboBox) in the form’s footer rather than in the form’s detail section, but the inability to access the value so far input (via the combo’s Text property) is a serious problem.

Thanks.

Micron · Jun 7, 2019

interesting. Never heard of it, had to try it. Worked for me on 3 different form set-ups. Is it only a problem on certain forms?

Simon_C · Jun 7, 2019

The form has to be bound to a query or table, if it's unbound then the problem doesn't occur. The error occurs whether the form is Single Record or Continuous and whether the form is used as a main form or a subform.

The other thing is that because I'm using controls in the footer to input data that will be displayed by the form, I've got the form's AllowAdditions property set to false. If the form's displaying no data but is showing an empty 'new' record then the problem doesn't occur either.

isladogs · Jun 7, 2019

I've never heard of this issue either.
Can you upload something that demonstrates the problem for us to look at.

Micron · Jun 7, 2019

will try some of the parameters you mention, but at least one of my trials was on a bound form - continuous and datasheet IIRC.

Simon_C · Jun 7, 2019

Must admit, I'd not tried it in Datasheet mode.

Simon_C · Jun 7, 2019

As requested, here's a stripped down database. Just open form frmPersonComboTest and try typing into the ComboBox. The Change event in the ComboBox in the footer is set to debug.print the combo's .Text value, so if you type into the combo box it will either do that (if, miraculously, it works for you) or it will error.

Micron · Jun 7, 2019

I removed the data source for a single form and can still set a footer textbox text value.

A db copy might be necessary as there are some things that don't sound right, like how a form bound to a table or query contains no records, has a footer for adding records, yet is set to not allow additions.

Simon_C · Jun 7, 2019

Micron said:
I removed the data source for a single form and can still set a footer textbox text value.

A db copy might be necessary as there are some things that don't sound right, like how a form bound to a table or query contains no records, has a footer for adding records, yet is set to not allow additions.

Let's face it, there aren't many forms in this world, especially continuous ones, where the data isn't filtered in some way. In my particular case, the original form having the error is a subform and so the records displayed there are determined by the master-child relationship it has with a value in another field. Sometimes it'll display a few dozen records, sometimes none and the rest of the time a number of records in between. But the error has nothing directly to do with the master-child relationship or with it being a sub-form as the attached (up-thread) example clearly shows. In the example, I've simply reduced the record count to zero by including a WHERE False condition in the Record Source, but it could just as easily be genuine search criteria which happen not to return any records.

The combo box in the real application contains thousands of entries (which is why I was looking at Allen Browne's code in the first place). There is one heck of a performance hit on having combos with large row lists in continuous forms, which is why I originally put the input controls in the footer, and after validation I manually add a record to the underlying table and requery the form. Of course from that point on, until something changes the set of records being displayed, the problem goes away (because the form is then displaying a record).

The attached (above) isn't part of the original application, it's just a dummy form and a few records to demonstrate the problem.

Micron · Jun 7, 2019

not sure how much of this is relevant but
- your form will never have records as there is no table field named false, nor is a field being compared to false
- in your posted db are trying to get the combo text property without setting the focus
- you are trying to get the text property from the bound field, which is not text but a GUID. It would have to be converted to text 1st.
Tell me you are not creating a replication database?

In the meantime, I'll see if converting the GUID to text will work, just out of curiosity.
I see our posts are crossing. Ignore what wasn't applicable.
EDIT2

the original form having the error is a subform

not trying to sound smart but you did have the subform control reference syntax right?

Simon_C · Jun 7, 2019

Micron said:
not sure how much of this is relevant but
- your form will never have records as there is no table field named false, nor is a field being compared to false

The example form's record source is "SELECT * FROM tblPersonX WHERE False;". Why would there be a table called 'False'? There's a table called tblPersonX, False (like True) is an in-built Access constant and "WHERE False" is a very standard construction to return no records in a query. This isn't my original code, this is just a simple way of forcing the issue that causes the error.

Micron said:
- in your posted db are trying to get the combo text property without setting the focus

Sorry, but you're completely wrong. The combo box Text property reflects the text part of the combo box, not the bound value (unless they happen to be the same column).

Micron said:
- you are trying to get the text property from the bound field, which is not text but a GUID. It would have to be converted to text 1st.

See above.

Micron said:
Tell me you are not creating a replication database?

I'm not, as such, but there is good reason.

And don't worry about the GUIDs, in the original application I convert them to strings in the query that fills the combo's row source in any case because some years ago there was (and possibly still is to this day, I don't know) a memory leak problem in Access when GUIDs were passed directly to a combo.

Simon_C · Jun 7, 2019

Sorry, I missed this one

Micron said:
- in your posted db are trying to get the combo text property without setting the focus

But I'm in the Change event for that very control, it already has the focus. If you don't believe me stick "Debug.Print Screen.ActiveControl.Name" above the existing Debug.Print statement.

Edit: That's odd. The forum software told me my previous comment had to be approved before it would appear, but this one's gone straight through.

Simon_C · Jun 7, 2019

Micron said:
not trying to sound smart but you did have the subform control reference syntax right?

I'm accessing the control from within the subform itself, so to my code it's a local control. I haven't got some long-winded Forms!frmSuchAndSuch.sbfrmSoAndSo.Form.cboCombo type construction, the controls can be accessed via Me.Name.Property.

Simon_C · Jun 7, 2019

Micron said:
not sure how much of this is relevant but
- your form will never have records as there is no table field named false, nor is a field being compared to false

No, the form's record source is "SELECT * FROM tblPersonX WHERE False;" There's a table here tblPersonX and False is an Access constant (so's True) so "WHERE False" is just a shortcut (for demonstration purposes) to produce no records.

Simon_C · Jun 7, 2019

Micron said:
- you are trying to get the text property from the bound field, which is not text but a GUID. It would have to be converted to text 1st.

No, you're mistaken. The Value property relates to the bound field, the Text property to the displayed value. Similarly, properties like SelText (which also fails in the same way and under the same circumstances as Text), SelLength, SelStart relate to the typed/displayed value and not the bound value (unless they're the same thing).

Simon_C · Jun 7, 2019

Micron said:
Tell me you are not creating a replication database?

I'm not, but there is good reason.

jdraw · Jun 7, 2019

Interesting. Don't know if it is significant, but it's what I tried.

When I adjusted the code

Code:

Private Sub cboPerson_Change()
    Debug.Print Screen.ActiveControl.Name
    Debug.Print Me.cboPerson.[COLOR="Red"]Value[/COLOR]
End Sub

I get the following
cboPerson
{00162D93-391C-4105-8559-7A1C13F0FC65}

isladogs · Jun 7, 2019

I'm unclear why you are using the Change event or the .Text property.
Instead use an after update event and .Value
No error occurs

Your record source SQL is actually

Code:

SELECT *
FROM tblPersonX
WHERE (((False)<>False));

Another way of getting no records is

Code:

SELECT * FROM tblPersonX WHERE 1=0;

Like Micron I'm unclear why you are using ReplicationID instead of Long Integer datatype for your autonumber field

EDIT
After posting I spotted and approved your moderated post. That sometimes happens for new members

Simon_C · Jun 7, 2019

Yes, the value property is no problem, but it doesn't return the same thing as the Text property and in the particular instance I'm talking about I need to trap what's being typed in rather than look at the resulting value once the combo value has been updated.

As I mentioned above, I'm looking to implement something similar to some code that Allen Browne had produced to reduce the number of records in the combo's row source, and to do that I need to access the value that the user has started typing in. So the value property doesn't help me there.

Micron · Jun 7, 2019

well, if you change the form allow additions property to yes, it works even if there are no records, but I have to concur with your assertion that otherwise, it does not. Altering that property allows bound controls to be visible for data entry that otherwise are not. Is that a bug, or a result of software engineering intent? Was that intent "Well if there are no controls visible on the form, there's no point in adding ANY form controls to the collection, thus no way of setting focus anywhere"? Not everything I see labelled as a "bug" is, IMHO, but this might be one. Not sure where that leaves you insofar as a fix is concerned.

Text property of Combo in Form Footer (Access 2013) (1 Viewer)

Registered User.

AWF VIP

Registered User.

MVP / VIP

AWF VIP

Registered User.

Registered User.

Attachments

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Registered User.

Super Moderator

MVP / VIP

Registered User.

AWF VIP

Similar threads

Users who are viewing this thread