Unbound TextBox Character Limit

[Boltie]

I've searched all over for a solution to my problem and had no luck at all, so apologies if this has been solved elsewhere.

I started off with a blank form and added a command button, three labels and a text box. None of these are bound to anything.

The sole purpose of this form is to show the progress of a VB script I've written that retrieves all the table names and the number of records contained in each. This information is also exported to Excel.

Everything works great apart from the log I'm producing inside the text box. This log is just a replica of what is being exported to Excel. Once the log reaches a length of 1,837 characters, it fails to have anything else added to it. The code below is where I have an error returned.

Me.LogList1.Text = Me.LogList1.Text & vbCrLf & tdf.Name & "|" & intRecCount

The error I receive is "Run-time error '2176': The setting for this property is too long.". From what I've read elsewhere though, an unbound text box is meant to have a character limit of a good few thousand. Around 60,000 from what I remember.

I did try a few other options such as using a label or a listbox but they weren't great. The label doesn't support a scroll bar and if the text goes past the size of the label it doesn't scroll down by default. A list box worked but I want the user to be able to copy the text after in-case the Excel report doesn't work for whatever reason.

I could possibly setup a table with a single field set to 'memo' and bound the text box to that. I don't know if that works though as I don't want to have to create an extra table. This form is to be used across other databases when required and this solution would add an unnecessary table to the list (which I could code out) and also means the table has to be exported to the other database along with the form.

Any ideas as to why my text box is limited to around 1,837 characters?

 

[jdraw]

Why does it have to be a text box on a form?
You could write to a table or to a file.
You could put a message to screen every so many whatevers.

There is a limit to the number of controls and sizes, but I wouldn't have thought 1837 characters would be that limit.

 

[Boltie]

Like I said, everything is exported to Excel so there is no real need apart from if Excel fails for whatever reason. It's a backup in-case I'm not here to resolve any problems and also an indication as to the progress. The user will still be able to take a copy and paste where necessary.

The main piece of code loops through each table, grabs the name and record count and populates Excel in the background. Everything around this works fine.

I could just have the Excel window up as it progresses but it doesn't give any indication to the user when the script has finished. I know it sounds pointless, but I'm dealing with many hundreds of tables with some containing a couple of million records. This means the process isn't too quick. The users therefore require some initial idea of how far along the process is.

I don't want to export to a table as I want people to be able to just export this form to other databases for use elsewhere. They may not realise that they would also need to export the table associated with it.

I'm not restricting myself to a text box though. If you have any suggestions for other form controls that may work just as well or better, please let me know. I just find it strange that I found posts mentioning that an unbound text box had no limit unlike a bound text box.

 

[stumac]

The .text property has a size limit of 2048 characters

try changing it to .value:

Me.LogList1.value = Me.LogList1.value & vbCrLf & tdf.Name & "|" & intRecCount

 

[Boltie]

Thanks stumac! That worked perfectly.

I can't believe how much time I've spent looking at other posts and that was all I needed to change.