Solved (.Value) doesn't show textbox Value on Change() event

[Nicha]

Hi,
I have two text boxes in a login form, and both have the same issue. I'm using the [Change()] event to check the number of characters typed manually to change the Text Box's background color.
The problem is that the (.Value) property doesn't reflect changes made while the focus is on the respective Text Box. Therefore, the color change only occurs when the focus moves to another control.
Similarly, whenever the text box is updated, the [AllControls_Change()] procedure is called to check if the fill is Ok and activate the [Cmd_Ok] button.

I found that if I change the property from (.Value to .Text) in the Change() event, it works, but it fails in the [AllControls_Change()] procedure, because of the Focus control.




The code below doesn't work in the [usertxt] control:

Private Sub usertxt_Change()
'Occurs evry key Pressed
'Stop
'   MsgBox Len(Trim$(Me.usertxt.Value))
   If Len(Trim$(Me.usertxt.Value) & vbNullString) > 0 Then
      Me.usertxt.BackColor = RGB(255, 255, 204)     '#FFFFFF            '&H80000005
   Else
      Me.usertxt.BackColor = RGB(255, 255, 255)     '&HC0FFFF
   End If


    AllControls_Change

End Sub

Public Sub AllControls_Change()
'Function to activate Ok Button.

Dim flg As Boolean
    
flg = (Len(Trim$(Me.usertxt.value)) = 7 And Len(Trim$(Nz(Me.passwtxt.value, ""))) > 0)


''Tenho de usar, por precaução, a propriedade text. (altera no evento Change), pois o Value só é alterado no
''AfterUpdate. Com isto o user pode deletar o dado e sem retirar o cursor podia ainda clicar no Botão Activo...
'Enabled_Event:
Me.Cmd_Ok.Enabled = flg
        

End Sub

P.s. I am attaching a file with the Form, for test purpouses.

Many thank's in advance.

 

[arnelgp]

there is no .Value on change event (or it's .Value is the OldValue).
use the textbox .Text property instead.

 

[arnelgp]

i added two "hidden" textbox to put the .Text of each textbox to the hidden textbox.

 

[Nicha]

Hi @arnelgp, thank you very much for your answer. There is no way to make it work without having to create supporting text boxes?

If, in the Change() event, you change (.value) to (.Text), this event works, but the [AllControls_Change()] procedure does not perform the correct validation. Your solution works perfectly, but I would like to know if there is another way to solve it without having to create a text box for each textbox.

Many thank's to you.

 

[theDBguy]

If you don't want to use a textbox, you could try using TempVars.

 

[Nicha]

If you don't want to use a textbox, you could try using TempVars.

I have build it as an excel userform and all works well. Can I use it inside access database? And if yes; how can I open it with VBa?

 

[theDBguy]

I have build it as an excel userform and all works well. Can I use it inside access database? And if yes; how can I open it with VBa?

Access has UserForms as well.

 

[Nicha]

Can you say if, in Access UserForms, the (.Value) will have the value updated in the Change() event?

 

[theDBguy]

Can you say if, in Access UserForms, the (.Value) will have the value updated in the Change() event?

Sorry, No. The Value property is usually updated only once the focus leaves the control.

PS. I don't use UserForms in Access. You might give it a try and maybe it behaves differently than regular forms.

 

[cheekybuddha]

There is no way to make it work without having to create supporting text boxes?

Yes, there is.

I guess Arnel was using hidden textboxes to demonstrate how it works. (I didn't look at his example)

During the Change event the value in the textbox is 'uncommited' and you must use the .Text property to get the contents. The .Text property is only available when the textbox has focus (which it will have during the Change event).

Adjust your code:

Private Sub usertxt_Change()
'Occurs evry key Pressed

   MsgBox Len(Trim$(Me.usertxt.Text))
   If Len(Trim$(Me.usertxt.Text) & vbNullString) > 0 Then
      Me.usertxt.BackColor = RGB(255, 255, 204)     '#FFFFFF            '&H80000005
   Else
      Me.usertxt.BackColor = RGB(255, 255, 255)     '&HC0FFFF
   End If


    AllControls_Change

End Sub

You may have to adjust AllControls_Change() as well, but I don't what the purpose of that code is.

 

[cheekybuddha]

Make the following changes in your code:

Private Sub passwtxt_Change()

  With Me
    If Len(Trim$(.passwtxt.Text & vbNullString)) > 0 Then
      .passwtxt.BackColor = RGB(255, 255, 204)    '&H80000005
    Else
      .passwtxt.BackColor = RGB(255, 255, 255)    '&HC0FFFF
    End If
    .Cmd_Ok.Enabled = EnableOK(.usertxt, .passwtxt.Text)
  End With

End Sub

Private Sub usertxt_Change()
  
  With Me
    If Len(Trim$(.usertxt.Text) & vbNullString) > 0 Then
      .usertxt.BackColor = RGB(255, 255, 204)     '#FFFFFF            '&H80000005
    Else
      .usertxt.BackColor = RGB(255, 255, 255)     '&HC0FFFF
    End If
    .Cmd_Ok.Enabled = EnableOK(.usertxt.Text, .passwtxt)
  End With

End Sub

Private Function EnableOK(user As Variant, pw As Variant) As Boolean

  EnableOK = Len(Trim$(user & vbNullString)) = 7 And Len(Trim$(pw & vbNullString)) > 0

End Function

Public Sub AllControls_Change()
'Function to activate Ok Button.

  With Me
    .Cmd_Ok.Enabled = EnableOK(.usertxt, .passwtxt)
  End With

End Sub

You can probably remove all the AfterUpdate events and probably the KeyPress events as not necessary.

 

[Nicha]

I chose to use a UserForm, and everything worked out fine.
The purpose of the [AllControls_Change()] procedure is to validate whether the conditions are met to activate the [Cmd_Ok] button.

I want to Thank you all (@arnelgp, @theDBguy and @cheekybuddha), it was with your help that I was able to make a decision.

Can you tell me please, how to mark the @cheekybuddha (#11) post as the solution?

 

[cheekybuddha]

Can you tell me please, how to mark the @cheekybuddha (#11) post as the solution?

I think you just mark the whole thread as Solved (Is there a button at the top of the thread?)

I know you have said you are going to use UserForms, but I am attaching the Access version so you can see how simple it is in Access too.

 

[Nicha]

I think you just mark the whole thread as Solved (Is there a button at the top of the thread?)

I know you have said you are going to use UserForms, but I am attaching the Access version so you can see how simple it is in Access too.

Excelent work dear @cheekybuddha. It WORK's....

Great Job.
Thank you very much.

 

[Pat Hartman]

I found that if I change the property from (.Value to .Text) in the Change() event, it works, but it fails in the [AllControls_Change()] procedure, because of the Focus control.

Each data bound control has three buffers.
.OldValue - the value of the control when the record is navigated to. For new records, the value is null.
.Text - the value of the control when you are in an event that runs when the control has the focus. This is the buffer that captures data entry. This property is ONLY AVAILABLE when the control has the focus. Once the control loses the focus, you MUST use the .Value property to see the contents of the control
.Value - current contents of any control which does not have the focus.

When a control loses the focus, the contents of .Text is moved to .Value
In the AfterUpdate event, whatever data is in the .Value property is moved to .OldValue so after a record has been changed and saved, you no longer see the same value in .OldValue that you saw originally. This is why your logging code needs to happen in an earlier event or you need to save the data you want to log.

The .Text property of VB forms is NOT THE SAME as the .Text property of Access forms. They are DIFFERNT.

 

[Nicha]

Each data bound control has three buffers.
.OldValue - the value of the control when the record is navigated to. For new records, the value is null.
.Text - the value of the control when you are in an event that runs when the control has the focus. This is the buffer that captures data entry. This property is ONLY AVAILABLE when the control has the focus. Once the control loses the focus, you MUST use the .Value property to see the contents of the control
.Value - current contents of any control which does not have the focus.

When a control loses the focus, the contents of .Text is moved to .Value
In the AfterUpdate event, whatever data is in the .Value property is moved to .OldValue so after a record has been changed and saved, you no longer see the same value in .OldValue that you saw originally. This is why your logging code needs to happen in an earlier event or you need to save the data you want to log.

The .Text property of VB forms is NOT THE SAME as the .Text property of Access forms. They are DIFFERNT.

Thank You @Pat Hartman. This is a very important information.

 

[arnelgp]

.OldValue - the value of the control when the record is navigated to. For new records, the value is null.

not entirely true, the OldValue for new record is it's Default value.
if the Default Value is 0, then that is the OldValue for new record.

 

[Pat Hartman]

not entirely true, the OldValue for new record is it's Default value.
if the Default Value is 0, then that is the OldValue for new record.

If you are using SQL Server, the default doesn't get applied until the new record is sent to the server to be saved. It doesn't make sense for .Oldvalue to be the default but I'll check.

 

[arnelgp]

just noticed this although i don't use salesforce.

 

[Pat Hartman]

I did check how the OldValue works and I disagree totally with the changes. There is code in the Current event and code in the BeforeUpdate event and several different default situations including a conflict between what is defined at the table level and what is defined on the form. The form default overrides the table and that is totally wrong. Also the OldValue is populated for a new record which is also totally wrong and it is populated even when the "new" record hasn't even been dirtied.

I made the sample in an .mdb so that if someone has old versions they can try to determine what the old situation was and in which version it changed. I'm guessing around 2010 because I had a problem with an app with an SQL Server BE where I had needed the default to populate before a new record got saved and so I ended up putting the default in the form so it would populate immediately. Perhaps if @isladogs is interested, he could tell us which version the change occurred in.

Thanks for pointing this out @arnelgp