CHAR fields and spaces in textbox field

[brian]

When making a field datatype CHAR in SQL Server for my Access project I
found out that you always get trailing spaces in a bound textbox on the
form. In order for a user to enter anything they must first back out
the trailing spaces. I tried trimming the data first in the On Click
event, but Access ignored it. I tried doing an RTRIM in my subform
query, which only loaded the trimmed value to the textbox, but that
resulted in making the field read only on my form, I couldn't enter
anything. I even tried loading the string "junk" to my 10 byte CHAR
field but Access still loaded "junk " to the textbox. The only
thing that worked was using SENDKEYS to load the textbox, but that
command is so unreliable that that is not an option. It seems that
there is no way, short of using unbound fields, of using the CHAR
datatype field in my project w/o upsetting my users. And I use CHAR
since I have to link the data to my mainframe system where all these
fields are fixed length by definition. Before I give up and go back to
VARCHAR I thought I would ask one more time to see if there is any way
to get rid of the trailing spaces in my textbox. Does anybody have any
other ideas?

Thanks, Brian

 

[John Vinson]

When making a field datatype CHAR in SQL Server for my Access project I
found out that you always get trailing spaces in a bound textbox on the
form. In order for a user to enter anything they must first back out
the trailing spaces. I tried trimming the data first in the On Click
event, but Access ignored it. I tried doing an RTRIM in my subform
query, which only loaded the trimmed value to the textbox, but that
resulted in making the field read only on my form, I couldn't enter
anything. I even tried loading the string "junk" to my 10 byte CHAR
field but Access still loaded "junk " to the textbox. The only
thing that worked was using SENDKEYS to load the textbox, but that
command is so unreliable that that is not an option. It seems that
there is no way, short of using unbound fields, of using the CHAR
datatype field in my project w/o upsetting my users. And I use CHAR
since I have to link the data to my mainframe system where all these
fields are fixed length by definition. Before I give up and go back to
VARCHAR I thought I would ask one more time to see if there is any way
to get rid of the trailing spaces in my textbox. Does anybody have any
other ideas?

Thanks, Brian

You can use the textbox's GotFocus event to set its SelStart and
SelLength properties:

Private Sub txtCharField_GotFocus()
Me.txtCharField.SelStart = 0
Me.txtCharField.SelLength = Len(Me.txtCharField)
End Sub

will highlight the entire field and let you type over it, for example.

John W. Vinson[MVP]

 

[brian]

I tried a slight variation of what you suggested by highlighting just
the trailing spaces, and then I did a Sendkeys "{DEL}". Works great.

Thanks, Brian

 

[John Vinson]

I tried a slight variation of what you suggested by highlighting just
the trailing spaces, and then I did a Sendkeys "{DEL}". Works great.

I'd REALLY try to avoid using SendKeys - it's unreliable, buggy, and
not recommended! If you want to have the trailing blanks highlighed,
won't the appropriate combination of SelStart and SelLength work???

John W. Vinson[MVP]

 

[brian]

Yes that combination highlights the trailing spaces. And if all the
user wants to do is append some data the spaces get deleted and he can
overwrite, that works OK. But if they want to enter something within
the string they can't, because the minute they move the cursor
backwards into the string the highlighted spaces disappear and they
can't enter anything again. I suppose they could be trained to hit the
DEL key before they do anything but I was trying to save them that
step. I hate Sendkeys also, is there any other way to get rid of them
once they are highlighted?

Thanks, Brian

 

[John Vinson]

But if they want to enter something within
the string they can't, because the minute they move the cursor
backwards into the string the highlighted spaces disappear and they
can't enter anything again.

It sounds like the keyboard needs to be toggled from Insert mode into
Overtype mode - I don't know offhand how to do so though! Does
pressing the Insert key solve the problem?

John W. Vinson[MVP]

 

[brian]

Yes it does, but the "normal" behaviour for the keyboard, at least
here, in any application is Insert, whether one is in Word, Excel,
mainframe, etc. And there isn't any Textbox property to control that
anyway. To bad Access doesn't trim these trailing spaces off for you
when displaying the field in a textbox. Seems like Sendkeys is the
only solution I have, and if it fails it's not a show-stopper, the user
will have to do delete them manually in that case, unless... you have
another idea?

Thanks, Brian

 

[John Vinson]

Yes it does, but the "normal" behaviour for the keyboard, at least
here, in any application is Insert, whether one is in Word, Excel,
mainframe, etc. And there isn't any Textbox property to control that
anyway. To bad Access doesn't trim these trailing spaces off for you
when displaying the field in a textbox. Seems like Sendkeys is the
only solution I have, and if it fails it's not a show-stopper, the user
will have to do delete them manually in that case, unless... you have
another idea?

That's really frustrating!

I've been digging around, and there MIGHT be some sort of API call to
toggle the Insert/Overtype mode of the keyboard... but I sure can't
find it. If there were, you could set it to Overtype in the textbox's
gotfocus event, and back to its previous state (whatever that was) in
lostfocus. But for now I guess your Sendkeys may be the only option.

I can see why Access doesn't make the fixed-length Char option easily
available... it's a pain!

John W. Vinson[MVP]