?
Solved

ms access update field when another field changed

Posted on 2011-03-03
14
Medium Priority
?
505 Views
Last Modified: 2012-05-11
I am trying to update a field when another field change.  However, after i change the field you won't see the change until you click on another field in the form. Is there any way to change the field without having to click another field. I guess when you move the mouse off the field. I tried other events like on change, on lost focus, but they more or less do the same.

Code:
Private Sub S_N_BeforeUpdate(Cancel As Integer)
If Me.p25.Value = True Then
      Me.pEsn.Value = Me.[S/N].Value
     Me.pEsn.Enabled = False
   End If
End Sub
0
Comment
Question by:Shen
  • 6
  • 5
  • 2
  • +1
14 Comments
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 35027545
Try setting the Text value of your controls  i.e.pESN.Text rather than pESN.Value

0
 
LVL 28

Expert Comment

by:omgang
ID: 35027618
Try

Private Sub S_N_BeforeUpdate(Cancel As Integer)
If Me.p25.Value = True Then
      Me.pEsn.Value = Me.[S/N].Value
     Me.pEsn.Enabled = False
      Me.pEsn.Requery
   End If
End Sub

OM Gang
0
 
LVL 6

Expert Comment

by:TinTombStone
ID: 35027659
I normaly use the Exit event for this type of thing

Private Sub S_N_Exit(Cancel As Integer)
    If p25.Value = True Then
        pEsn.Value = [S/N].Value
        pEsn.Enabled = False
    End If
End Sub


I take it that the value of p25 is True? try putting a breakpoint on the If p25 line and checking the values
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 28

Expert Comment

by:omgang
ID: 35027727
Rickgov, are you wanting to see the other field updated BEFORE focus has left current field?  Think about this:  each time the user enters a character in the field do you want the other field to update?  The BeforeUpdate, LostFocus, Exit events all fire when focus leaves the control --- that's how the applciation knows the user is done doing data entry.
OM Gang
0
 

Author Comment

by:Shen
ID: 35027854
.Requery was the same.

.text gave me an error.

I wanted to change when I type the last character.  Maybe this is not possible.
0
 
LVL 28

Expert Comment

by:omgang
ID: 35028005
<<I wanted to change when I type the last character.  Maybe this is not possible. >>

How will Access know when you've typed the last character?  That's why most of the events fire when the control loses focus.
OM Gang
0
 
LVL 6

Expert Comment

by:TinTombStone
ID: 35028059
if the field is limited to say, 6 characters, then you should be able to check the length of the entered text during the Change event.  

When the length hits 6 then...
0
 

Author Comment

by:Shen
ID: 35028482
on change trigger for every character change, but it won't change the other field.
0
 
LVL 28

Expert Comment

by:omgang
ID: 35028632
Please post your latest code.
OM Gang
0
 

Author Comment

by:Shen
ID: 35028688
Private Sub S_N_Change()
If Me.p25.Value = True Then
     Me.pEsn.Value = Me.[S/N].Value
     Me.pEsn.Requery
     Me.pEsn.Enabled = False
  End If
End Sub
0
 
LVL 28

Expert Comment

by:omgang
ID: 35028786
The sub you posted says

When the control named S_N is changed (each time)
    check the value of the control named p25 and if it is True then change the value of the control pEsn to equal the value of the control S/N

correct me if I am wrong but the value of control S/N isn't changing while you are entering text into the control S_N so we shouldn't expect pEsn to change

OM Gang
0
 

Author Comment

by:Shen
ID: 35028879
you are correct. After debugging, the on change event triggers when a character is entered/change but the value remains the same. So the on change does not capture the character changes.
0
 
LVL 28

Accepted Solution

by:
omgang earned 2000 total points
ID: 35028931
Please post a screen shot of your form showing the controls in question.  There are four cotrols involved, correct?
S_N
p25
pEsn
S/N

OM Gang
0
 

Author Comment

by:Shen
ID: 35028951
It is working now. I changed from .value to text.
 
If Me.p25.Value = True Then
           Me.pEsn.Value = Me.[S/N].Text
     Me.pEsn.Requery
     Me.pEsn.Enabled = False
     End If

Thank you all very much
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post, we will learn to set up the Group Naming policy and will see how it is going to impact the Display Name and the Email addresses of the Group.
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

593 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question