Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ms access update field when another field changed

Posted on 2011-03-03
14
Medium Priority
?
486 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

721 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