Calculated field

[Reader1]

Hello

I'm trying to make a calculated field on a form based on the contents of
another field.

What I want is a field to display the number of characters typed in the
other field.

I've tried using a query to do this and the 'Len' function but this only
works if you click in another field to see this length field update.

Is it possible to display the calculated field in real time so that the
number changes as the length of the string changes as the record is being
typed in ?

Thank You

 

[Graham Mandeno]

Hi Reader1

When you say Len([MyTextBox]), you are referring to the textbox's Value
property. This does not change until the textbox is updated (which usually
happens when it loses focus).

The property that changes as you type is the Text property, but this is only
available when the textbox has the focus.

So the short answer is that you cannot use a calculated control for this,
but you must use an unbound control and fill it with the calculated value
using code.

The Change event is the one that is raised repeatedly as you type, so you
need some code like this:

Private Sub MyTextBox_Change()
txtNumChars = Len(MyTextBox.Text)
End Sub

You will also want the value to be correctly set as you navigate from one
record to another, so use the Current Event:

Private Sub Form_Current()
txtNumChars = Len(MyTextBox & "")
End Sub

The & "" bit handles the situation where the value in the textbox is Null.

 

[Guest]

On the OnChange event of the other field write the code

Me.TheField = Len(Me.[OtherFieldName].Text)

 

[Reader1]

The Change event is the one that is raised repeatedly as you type, so you

need some code like this:

Private Sub MyTextBox_Change()
txtNumChars = Len(MyTextBox.Text)
End Sub

You will also want the value to be correctly set as you navigate from one
record to another, so use the Current Event:

Private Sub Form_Current()
txtNumChars = Len(MyTextBox & "")
End Sub

The & "" bit handles the situation where the value in the textbox is Null.

Sorry but just how and where do I enter all this ?

I'm a newbie !

 

[Reader1]

OK - I've had a play around and I think I put everything in the right place
but .............

It doesn't work at all.

The unbound field doesn't display anything.

 

[Graham Mandeno]

How about you copy and paste the stuff you think you have in the right place
into a reply here and then someone might be able to start advising where you
might have gone wrong.

 

[Reader1]

How about you copy and paste the stuff you think you have in the right
place into a reply here and then someone might be able to start advising
where you might have gone wrong.

OK here goes ...........

My table is called 'tbl_Test'
It contains two fields 'Alpha' and 'Beta'
Both text fields

My form is called 'frm_Test3'
It has two fields - Alpha and Beta
(It's the Alpha field that I want to count the number of characters)
I've added a third unbound text box called 'MyTextBox' like you said.
In the properties for this field where it says 'On Change' next to this it
says [Event Procedure]
If I click on the [...] (three dots field) that's where I pasted the
following

Private Sub MyTextBox_Change()
txtNumChars = Len(MyTextBox.Text)
End Sub

Then on the form properties where it says 'On Current' next to this it says
[Event Procedure]
If I click on the [...] (three dots field) that's where I pasted the
following

Private Sub Form_Current()
txtNumChars = Len(MyTextBox & "")
End Sub

Thanks for replying

 

[Graham Mandeno]

"MyTextBox" and "txtNumChars" are just two made-up names that I used for the
textboxes because you didn't tell us what they were.

You are typing in a textbox named Alpha, and you want the number of
characters in Alpha to appear in a textbox named txtNumChars, OK?

So what you want is:

Private Sub Alpha_Change()
txtNumChars = Len(Alpha.Text)
End Sub

and

Private Sub Form_Current()
txtNumChars = Len(Alpha & "")
End Sub

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

How about you copy and paste the stuff you think you have in the right
place into a reply here and then someone might be able to start advising
where you might have gone wrong.

OK here goes ...........

My table is called 'tbl_Test'
It contains two fields 'Alpha' and 'Beta'
Both text fields

My form is called 'frm_Test3'
It has two fields - Alpha and Beta
(It's the Alpha field that I want to count the number of characters)
I've added a third unbound text box called 'MyTextBox' like you said.
In the properties for this field where it says 'On Change' next to this it
says [Event Procedure]
If I click on the [...] (three dots field) that's where I pasted the
following

Private Sub MyTextBox_Change()
txtNumChars = Len(MyTextBox.Text)
End Sub

Then on the form properties where it says 'On Current' next to this it
says [Event Procedure]
If I click on the [...] (three dots field) that's where I pasted the
following

Private Sub Form_Current()
txtNumChars = Len(MyTextBox & "")
End Sub

Thanks for replying

 

[Reader1]

"MyTextBox" and "txtNumChars" are just two made-up names that I used for
the textboxes because you didn't tell us what they were.

You are typing in a textbox named Alpha, and you want the number of
characters in Alpha to appear in a textbox named txtNumChars, OK?

So what you want is:

Private Sub Alpha_Change()
txtNumChars = Len(Alpha.Text)
End Sub

and

Private Sub Form_Current()
txtNumChars = Len(Alpha & "")
End Sub

Have followed what you said..........

When I open the form I get an error that says 'Module not found'

The first bit of code goes in the unbound text box called txtNumChars in the
'Change Event' - Yes ?

And the next bit of code goes in the form properties 'On Current' - Yes ?

If that's right then thats what I've done.

Sorry to be a pain in the ass !

 

[Graham Mandeno]

Have followed what you said..........

Not quite

When I open the form I get an error that says 'Module not found'

Not sure what could be causing that. Let's get the rest of it right and
we'll see if it goes away.

The first bit of code goes in the unbound text box called txtNumChars in
the 'Change Event' - Yes ?

No, that code is to be executed when the contents change in the textbox
named "Alpha", so it should be attached to Alpha's Change event. (That's
why the procedure is named "Alpha_Change".)

And the next bit of code goes in the form properties 'On Current' - Yes ?

Yes, precisely!

If that's right then thats what I've done.

Well, almost

You must also make sure that the textbox where you want the number to appear
is named "txtNumChars". It should be unbound (ControlSource is blank) and
you should also set Enabled to no and Locked to Yes so that it can never get
the focus.

Sorry to be a pain in the ass !

Nah - just on a learning curve

 

[Reader1]

Brilliant !!!!!!!!

Thank you so much !!

Initially I still got the Module not found error - don't know why but when I
started a new database and rebuilt the table and form, the error disappeared
and the form now works correctly.

Thanks for your help and patience.

 

[Graham Mandeno]

Great!!! Glad you got it working