To vbProper or not to vbProper

[Curtis]

Hello,
This is my first post on this forum, so bare with me.
I am trying to code a form., but having problems getting the results in the
way I want.......
My problem is with a vbProper setting... But this could actually be used for
any function that executes on the AfterUpdate. But I'll use the vbProper for
my example. So I have the function to capitalize each word when you tab off
of a field by calling the function on the AfterUpdate. BUT, there are times
when you want the user to be able to move to the next field without executing
the called function therefor leaving the text just as it was typed.
So what I am looking for is a way to code another key, perhaps a function key,
to tab from field to field, on the tab stops you set, without calling the
vbproper function.
Hope I'm making sense
A short example.........Tab or "Other Key"
With Tab
Your on field one.....
You type "ABC Company" then tab...
Moves to field two, field one changes to "Abc Company"
With "other Key" ......say "F10"
Your on field one...
You type "ABC Company" then "F10"
Moves to field two, field one remains "ABC Company"

The more I think about this putting the code on the Afterupdate event might
not be best... But I'm open to any suggestions.

Any thoughts on how to code this?
Thanks

 

[Steve Schapel]

Curtis,

The After Update event of the control will fire regardless of how you
move the focus off it.

The purpose here is to "manually" control whether the data is modified
by the code. Am I right? So your thought was to manually control this
via the key used to tab from the control, right? How about this idea
instead... Place a checkbox (probably unbound) on the form, next to the
control, maybe labelled something like 'Retain as entered'. If you know
you are about to enter data which you do not want converted to proper
case, such as "ABC Company", tick the checkbox before entering the data.
Then, your After Update code could be something like this...

If Me.NameOfCheckbox Then
Me.NameOfCheckbox = 0
Else
Me.NameOfTextbox = StrConv(Me.NameOfTextbox, 3)
End If

Here would be another approach... Set a form-level variable to store the
value of the control as entered, before the string conversion takes
place. So your control's After Update code is like this...

YourStringVariable = Me.NameOfTextbox
Me.NameOfTextbox = StrConv(Me.NameOfTextbox, 3)

And then, have a little Command Button next to the control, which you
can click to revert back to the text as entered, via code something like
this...
Me.NameOfTextbox = YourStringVariable

 

[Curtis via AccessMonster.com]

I'm sure this would work..... But.......... Not as simple as I want.
I currently work with some software that does this, using the Tab key and F10
keys. I know it was built in Access, so........ I know there is a way.
I think I might see if one of thier programers will tell me how they did it.

I have been playing with some code but it still has one error...... Maybe you
know why........

Private Sub Form_Load()
Me.KeyPreview = True
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyTab Then
Screen.ActiveControl = StrConv(Screen.ActiveControl, 3)
Else
End If
End Sub

This works when text is already in the field. For example, I created a simple
DB with one form two text fields.
Type into field one, press enter, moves to field two leaves field one as
typed. BUT if you type into field one and Tab to field two, it deltes text
from field one.
Once text is in records, you can tab or enter from field to field and it
changes accordingly.
SO..... Why is it deleting the text if you Tab on the first time??

Curtis,

The After Update event of the control will fire regardless of how you
move the focus off it.

The purpose here is to "manually" control whether the data is modified
by the code. Am I right? So your thought was to manually control this
via the key used to tab from the control, right? How about this idea
instead... Place a checkbox (probably unbound) on the form, next to the
control, maybe labelled something like 'Retain as entered'. If you know
you are about to enter data which you do not want converted to proper
case, such as "ABC Company", tick the checkbox before entering the data.
Then, your After Update code could be something like this...

If Me.NameOfCheckbox Then
Me.NameOfCheckbox = 0
Else
Me.NameOfTextbox = StrConv(Me.NameOfTextbox, 3)
End If

Here would be another approach... Set a form-level variable to store the
value of the control as entered, before the string conversion takes
place. So your control's After Update code is like this...

YourStringVariable = Me.NameOfTextbox
Me.NameOfTextbox = StrConv(Me.NameOfTextbox, 3)

And then, have a little Command Button next to the control, which you
can click to revert back to the text as entered, via code something like
this...
Me.NameOfTextbox = YourStringVariable

Hello,
This is my first post on this forum, so bare with me.

[quoted text clipped - 25 lines]

Any thoughts on how to code this?
Thanks

 

[Steve Schapel]

No problem, Curtis. Apparently you and I have different idea of what
"simple" means .

The problem here is that you are trying to change the value of a control
before the value has been updated into the control. So in the case of
your new record, at the point where you press the Tab key, the value of
the control is Null, and converting Null to proper case still leaves you
with Null. I have no idea whether this will work or not, but should be
easy to test... try it like this:
If KeyCode = vbKeyTab Then
Me.Dirty = False
Screen.ActiveControl = StrConv(Screen.ActiveControl, 3)
End If

Another idea...
If KeyCode = vbKeyTab Then
Screen.ActiveControl.Text = StrConv(Screen.ActiveControl.Text, 3)
End If

Otherwise, we'll try and think of something else!

 

[Curtis via AccessMonster.com]

Bingo!
The Me.Dirty = False
Does the trick. Now you click "Tab" to change to proper case "Enter" to leave
as typed.
Now THATS what I call simple
Thank you very much! This one has been frustrating!

No problem, Curtis. Apparently you and I have different idea of what
"simple" means .

The problem here is that you are trying to change the value of a control
before the value has been updated into the control. So in the case of
your new record, at the point where you press the Tab key, the value of
the control is Null, and converting Null to proper case still leaves you
with Null. I have no idea whether this will work or not, but should be
easy to test... try it like this:
If KeyCode = vbKeyTab Then
Me.Dirty = False
Screen.ActiveControl = StrConv(Screen.ActiveControl, 3)
End If

Another idea...
If KeyCode = vbKeyTab Then
Screen.ActiveControl.Text = StrConv(Screen.ActiveControl.Text, 3)
End If

Otherwise, we'll try and think of something else!

I'm sure this would work..... But.......... Not as simple as I want.
I currently work with some software that does this, using the Tab key and F10

[quoted text clipped - 23 lines]

changes accordingly.
SO..... Why is it deleting the text if you Tab on the first time??

 

[Curtis via AccessMonster.com]

One more question on this so I understand it correctly........
The Me.Dirty = False
This is telling the record has not been changed? So does it force access to
save it before running the next line of code?

Thanks again
Curtis

Bingo!
The Me.Dirty = False
Does the trick. Now you click "Tab" to change to proper case "Enter" to leave
as typed.
Now THATS what I call simple
Thank you very much! This one has been frustrating!

No problem, Curtis. Apparently you and I have different idea of what
"simple" means .

[quoted text clipped - 22 lines]

 

[Steve Schapel]

Curtis,

... So does it force access to
save it before running the next line of code?

Exactly!

 

[Steve Schapel]

Lovely! Poetry in motion, eh?

I would be interested in knowing if my idea using the .Text property
would also solve the problem.

 

[Curtis via AccessMonster.com]

Yes, this works also.
But I'm not sure why
You specify the control to be text? So why would that work and not without it?


And now.......... Which is better to use?

 

[Steve Schapel]

Curtis,

Yes, this works also.

Ok, thanks for confirmation... saves me having to set up a test case .

But I'm not sure why
You specify the control to be text? So why would that work and not without it?

The default property of a control is the Value property. So...
Screen.ActiveControl = Screen.ActiveControl.Value
The Value of a control is the data after it has been updated into the
control.
On the other hand, the Text property relates to the data currently
displayed in the control, so
Screen.ActiveControl.Text
should give you the data you have typed in, even though this has not
been saved.

And now.......... Which is better to use?

The .Text approach will only work if the control has the focus at the
time. The .Dirty approach would be awkward if you didn't want the
record saved for some reason, for example data "required" in a field
that you hadn't got to yet, or some such. Other than that, I can't
think of much to choose between them.

 

[Curtis via AccessMonster.com]

I have used the ".text" version. I have other required fields on the form in
this app, and your right, it doesn't like that
I have also found you can use a Select statement to give other conversion
options. Gets interesting.
But a follow up question while using Select, and getting back to the
beginning of this thread.
My first example used the F10 key to avoid the conversion. As I have it set
now, the return key is doing that task only because it moves focus to the
next tab without coding.
So the follow up is this........ Say I wanted to use a function key to set
another conversion, which works fine, BUT, I also want it to move to the next
field.
No I know I could simply add another line of code, possibly on the "Key up"
Setfocus.NextFieldName. BUT this would require ALOT of setfocus lines.
So final question for this one is....... Is there a way to set focus to the
next tab stop without having to name that tab stop in the code?

 

[Steve Schapel]

Curtis,

Warning: untested "air code"...

Dim ctl As Control
For Each ctl In Form.Controls
If ctl.ControlType = acTextbox Then
If ctl.TabIndex = Screen.ActiveControl.TabIndex + 1 Then
ctl.SetFocus
Exit For
End If
End if
Next ctl

 

[Curtis via AccessMonster.com]

Problem has occured ........
Several fields on the form I am using the code......
If KeyCode = vbKeyTab Then
Screen.ActiveControl.Text = StrConv(Screen.ActiveControl.Text, 3)
End If
on................. Well, two of them are dates, with input mask. error 2279
comes up....
Hit debug and it highlights code ..... Screen.ActiveControl.Text = StrConv
(Screen.ActiveControl.Text, 3)
So... Is it causing this error beacuse there is a different value then text?
Do we just add to the code? Like........
If Keycode = vbKeyTab and Screen.ActiveControl = text ..... ???????
I don't know the syntax here.....

 

[Steve Schapel]

Curtis,

Best way I can think of to handle this would be to enter something in
the Tag property of all the controls you want this functionality to
apply to. Then the code like this...

If Keycode = vbKeyTab And Screen.ActiveControl.Tag = "fred" Then

 

[Curtis via AccessMonster.com]

I was thinking of that
so I did this..........

Private Sub Form_Load()
Me.KeyPreview = True
End Sub

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
Dim ctl As Control
For Each ctl In Controls
If ctl.Tag = "*" And KeyCode = vbKeyTab Then
Screen.ActiveControl.Text = StrConv(Screen.ActiveControl.Text, 3)
Else
End If
Next ctl

End Sub
That seems to work on my formating.......


Now, problem is still on those date fields.... Gives me the 2279 error and
highlights
Screen.ActiveControl.Text = StrConv(Screen.ActiveControl.Text, 3)

I guess this could be an unrelated problem..... But I don't see how. I have
not changed anything in these date fields.

???????

Curtis,

Best way I can think of to handle this would be to enter something in
the Tag property of all the controls you want this functionality to
apply to. Then the code like this...

If Keycode = vbKeyTab And Screen.ActiveControl.Tag = "fred" Then

Problem has occured ........
Several fields on the form I am using the code......

[quoted text clipped - 9 lines]

If Keycode = vbKeyTab and Screen.ActiveControl = text ..... ???????
I don't know the syntax here.....

 

[Steve Schapel]

Curtis,

What does ctl.Tag ="*" mean? You need to enter a specific something in
the tag property of the controls you want the code to apply to. Any
anyway, your code is not what I suggested. Your code will try to
StrConv the active control if *any* control had the tag you mention...
doesn't make sense.

 

[Curtis via AccessMonster.com]

OK, I switched away from the asterisk.......
Code is now
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
Dim ctl As Control
For Each ctl In Controls
If ctl.Tag = "Cap" And KeyCode = vbKeyTab Then
Screen.ActiveControl.Text = StrConv(Screen.ActiveControl.Text, 3)
Else
End If
Next ctl

End Sub

Is the asterisk not a good character to use in the "for each"???
This code now seems to work perfectly.


oh...... The other problem does seem to be a input mask problem.... I like to
use mm/dd/yy
Sometimes I find that causes problems. Doesnt seem to work the same in every
case.....

Curtis,

What does ctl.Tag ="*" mean? You need to enter a specific something in
the tag property of the controls you want the code to apply to. Any
anyway, your code is not what I suggested. Your code will try to
StrConv the active control if *any* control had the tag you mention...
doesn't make sense.

I was thinking of that
so I did this..........

[quoted text clipped - 23 lines]

???????

 

[Steve Schapel]

Curtis,

I am pleased to hear that it seems to be working correctly. However, I
can't see why you are using this complicated "For Each ctl" construct,
and I can't see how it would work. Without testing, it looks to me like
it will "Properize" the text in the active control if *any* control on
the form has "Cap" in the Tag property. Why not do it like I suggested
before?...

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If Keycode = vbKeyTab And Screen.ActiveControl.Tag = "Cap" Then
Screen.ActiveControl.Text = StrConv(Screen.ActiveControl.Text, 3)
End If
End Sub

I have never been able to understand the purpose of an input mask for a
date field. What are you trying to achieve by using an input mask?
Anyway, if you can explain in a bit more detail what you mean by "causes
problems", I may have an idea.

 

[Curtis via AccessMonster.com]

Steve,
Thanks very much for your reply. I do like your method better
I have to admit I have only used the tag property on one other db......
I'm fairly new to Access, about a year and I'm still very much learning.
I have also seen on other post (Other forums) alot of bloated code!
Now I must say I'm guitly of it! Opps
On the date input subject I guess it is just what you prefer. I prefer to
be able to just input 6 numbers. No slashes and not a 4 digit year.
I have used before the built in input mask in Access and backed out 2
characters on the year. But can't seem to find one that works consistantly.
Maybe it comes from my background. I've spent about 20 years in the
accounting field. (We like ten keys)
That may all change soon. I'm thinking of diving into the IT field. We have
an outfit here in KC called "Foss training center" IT training. They have
several "fast track" programs. So I am debating which to go into....
Maybe "Database Administrator" or "Joint Network/Database Administrator"

You might give me your opinion of the best IT career choice these days.

Curtis,

I am pleased to hear that it seems to be working correctly. However, I
can't see why you are using this complicated "For Each ctl" construct,
and I can't see how it would work. Without testing, it looks to me like
it will "Properize" the text in the active control if *any* control on
the form has "Cap" in the Tag property. Why not do it like I suggested
before?...

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If Keycode = vbKeyTab And Screen.ActiveControl.Tag = "Cap" Then
Screen.ActiveControl.Text = StrConv(Screen.ActiveControl.Text, 3)
End If
End Sub

I have never been able to understand the purpose of an input mask for a
date field. What are you trying to achieve by using an input mask?
Anyway, if you can explain in a bit more detail what you mean by "causes
problems", I may have an idea.

OK, I switched away from the asterisk.......
Code is now

[quoted text clipped - 16 lines]

Sometimes I find that causes problems. Doesnt seem to work the same in every
case.....

 

[Steve Schapel]

Curtis,

Thanks very much for your reply. I do like your method better
I have to admit I have only used the tag property on one other db......
I'm fairly new to Access, about a year and I'm still very much learning.
I have also seen on other post (Other forums) alot of bloated code!
Now I must say I'm guitly of it! Opps

Well, it's not so much the bloat that's the problem, it's that it
wouldn't have the effect you wanted!

On the date input subject I guess it is just what you prefer. I prefer to
be able to just input 6 numbers. No slashes and not a 4 digit year.
I have used before the built in input mask in Access and backed out 2
characters on the year. But can't seem to find one that works consistantly.

Can't comment without seeing details of what you are using, and what
unexpected behaviour your are experiencing.

That may all change soon. I'm thinking of diving into the IT field. We have
an outfit here in KC called "Foss training center" IT training. They have
several "fast track" programs. So I am debating which to go into....
Maybe "Database Administrator" or "Joint Network/Database Administrator"

You might give me your opinion of the best IT career choice these days.

Hmmm. Sorry, not really. There are so many factors to take into
account. Obviously you need to do something which you have an aptitude
for, and that you enjoy. I think there are huge opportunities in IT,
but it's certain to be hard work. There are a lot of blogs these days
written by people in all sorts of aspects of IT, so that might be a good
place to get an impression of what's going on. All my experience in IT
is as a self-employed software developer, specialising in Access. I
believe there will continue to be huge scope and demand for this type of
work for the forseeable future. Best wishes.