Text property of Combo in Form Footer (Access 2013) (2 Viewers)

[Simon_C]

Your record source SQL is actually

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

That's interesting, I'd always assumed that WHERE False was marginally quicker than something like WHERE 1=0 because it wasn't having to evaluate a condition but merely checking a boolean value. That's useful to know.

 

[Simon_C]

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

Simply because the application is used by multiple people simultaneously (or potentially so) and can be used anywhere, where there may be no connection to the outside world. But the merging of data is all done manually (by which I mean in code rather than via any automated replication process). The only other solution I could think of would have been to allocate each individual user their own unique lists of long integer IDs to use, but the code to merge the separate databases would still have been necessary.

 

[isladogs]

I'm familiar with Allen Browne's code to manage combos with large numbers of records.
However I found it inadequate when managing a huge number e.g. 2.6 million UK postcodes. I prefer using cascading combos.

 

[Simon_C]

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.

There's definitely a "bug" there if only because it's putting out an incorrect message - because the control does actually have the focus. So even if there is a good reason for the situation being in place, it's telling you something that's factually incorrect about the problem and its potential solution.

I think it was Access 2003 where I first encountered this "feature", might have even been Access 2000. The system I'm trying to update now has been running since around 2003, but one combo box in particular now has so many rows in it because of the amount of data that's been input in the last 15 years or so that it's becoming a performance issue. I was looking at solutions to reduce the number of rows being displayed and that's when I happened upon Allen Browne's code. With a couple of minor modifications, it seems to work fine and quite speedily so long as the form I'm typing new data into already has some data there.

 

[theDBguy]

There's definitely a "bug" there if only because it's putting out an incorrect message - because the control does actually have the focus. So even if there is a good reason for the situation being in place, it's telling you something that's factually incorrect about the problem and its potential solution.

I think it was Access 2003 where I first encountered this "feature", might have even been Access 2000. The system I'm trying to update now has been running since around 2003, but one combo box in particular now has so many rows in it because of the amount of data that's been input in the last 15 years or so that it's becoming a performance issue. I was looking at solutions to reduce the number of rows being displayed and that's when I happened upon Allen Browne's code. With a couple of minor modifications, it seems to work fine and quite speedily so long as the form I'm typing new data into already has some data there.

I guess the only solution for now is to handle the error. Did you submit this "bug" at the User Voice website, by any chance? Just curious...

 

[Simon_C]

I'm familiar with Allen Browne's code to manage combos with large numbers of records.
However I found it inadequate when managing a huge number e.g. 2.6 million UK postcodes. I prefer using cascading combos.

I can't see cascading combos working in this context partly because I'm not sure there's a natural way to break up the data. I suppose I could have separate input combo boxes for forename and surname...
As I mentioned above, the performance of Allen's code seems fine (though I'm starting with only about 5% of the number of records you mention) but at the moment it only works in circumstances where there's already some data present.

 

[Micron]

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.

I guess what I'm not grasping is if the intent is data entry why is allow additions set to no? Anyway, you can get the Value which would be the same as getting text property if you can use the AfterUpdate event of your unbound footer textbox.

 

[Simon_C]

I guess what I'm not grasping is if the intent is data entry why is allow additions set to no? Anyway, you can get the Value which would be the same as getting text property if you can use the AfterUpdate event of your unbound footer textbox.

As I mentioned in the original post, the problem I'm really trying to solve (as opposed to the bug I'm trying to work-around) is to reduce the number of entries in the combo box by doing something akin to Allen Browne's "Combos with Tens of Thousands of Records" code.

The only way I can practically do this, I think, is to filter the combo rows based on what I start to type. So instead of the combo having an unmanageable 131,000 records all the while, it lets me type - for example - the letters Sim and then updates the row source to return just the records that start with those three letters (a more manageable 600 or so). But to do that, I need to access the Text property which picks up what I type as I type it, and not the Value property which can only be set once I've selected or typed-in a value that's in the combo's row source.

 

[Simon_C]

I guess the only solution for now is to handle the error. Did you submit this "bug" at the User Voice website, by any chance? Just curious...

I have a memory that back in 2003 or whenever it was, I did submit this as a bug using whatever facility was around then, but what happened subsequent to that I honestly can't remember.

The fact that no-one here seemed to know of the problem already despite it existing for more than 15 years suggests that's hardly one that's inconvenienced many people and so hardly likely to be high-up on Microsoft's list to fix.

In typing one of my answers up-thread, I realised I might have a solution, albeit one that's not very pretty. If I were to put my input controls, currently in the form footer, into the detail section of a new unbound form I could then replace them in the form footer with a single subform control, which contains that new unbound form. It would mean changing various references from Me... to Me.Parent...

 

[Micron]

I think I get your point but value might be usable if you can filter on that rather than a "filter as you type" kind of thing where you otherwise need Text.
Your subform idea is interesting but probably won't work if it's in the footer. If in the detail section as you state it will be interesting to see if it's any more visible when other controls are not when there's no data.

 

[isladogs]

In case its any help, here's how I do something similar with student names.
See attached screenshots.

Type in part of the last name or first name & press enter.
This triggers the after update event code so can be based on .Value
The listbox displays all related student names from which the required name is selected

Type additional letters in the combo to narrow the list displayed

In this case my recordset is about 1500 records rather than your 131,000.
But I think it could work just as well for you

Interestingly I'm also using the combo change event for other purposes.
Unlike your form, no error occurs in that event if the entered letters produce no hits.

 

[Micron]

well I'll be danged. You can set the text property of a control on a subform in main form footer. The odd colours are just from playing around with other properties.

 

[Simon_C]

Do you mean, even when the subform isn't displaying any records? Or do you mean it's surprising because the subform doesn't have the focus?

 

[Micron]

Either, I guess. I may have forgotten the goal or usefulness of my endeavor but after failing to set the text property of a textbox on the main form footer (where allow additions is false) I was surprised to learn it's possible if the textbox is on a subform in main form footer even if subform allow additions is false. Subform has no record source. Reagardless the approach ought to allow you to filter main form combo as you type.