Solved

ms access update field when another field changed

Posted on 2011-03-03
14
472 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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 500 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now