Solved

ms access update field when another field changed

Posted on 2011-03-03
14
473 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Outlook Free & Paid Tools
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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.

821 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