Solved

ms access update field when another field changed

Posted on 2011-03-03
14
471 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
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 …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

11 Experts available now in Live!

Get 1:1 Help Now