VBA code-Use the value of an existing field to update another field on a form

[jrsarrat]

Hi, there!!!

I am attempting to use VBA code to auto update a field on a form after a previous field's manual entry. Here's the code:
Private Sub PayerID_AfterUpdate()

Me.PayerID.Value = Me.AIMCarrierBranchCode(IsNumeric(Right(PayerID, 9)))
End


End Sub

It is not working. I'm pretty sure the code is incorrect and the placement of the code is probably incorrect too. Under the Property Sheet menu, I am doing an "After Update" event on the PayerID field (the manual entry). I want to auto update the AIMCarrierBranchCode field using 9 digits (from the right) of the PayerID's value.

Your help is much appreciated! Also, is there a good VBA book I can buy that can school me on VBA coding?


Thanks!

 

[Micron]

Re last post - to be clear, just that code in your event, not saying that you add those 2 lines to what you already have.

 

[jrsarrat]

Thanks, Micron. It looks like I have homework to do? I’ll post my results later.

 

[jrsarrat]

Hello, Micron and fellow colleagues!

In the same database, I am trying to code my database to not auto-update the AIMCarrierBranchCode and AIMCarrierParentCode fields if the PayerID field does not start with 'PY' AND does not have 11 characters. I have already done the coding to auto-populate if the previous two parameters are met.

Here's what's happening-
1. If PayerID meets both parameters, both fields auto-populate. That's what I want!
2. If the PY parameter is not met (11 characters not met either), both fields do not auto-populate. That's what I want!
3. If the PY parameter is not met and the 11 characters parameter is met, the AIMCarrierParentCode does not populate (good), but the AIMCarrierBranchCode does populate (bad).
4. If PY parameter is met, and the 11 characters parameter is not met, both fields populate (BAD).

I have tried a series of coding that are not working. If you can point me in the right direction, that would be GREAT. Below, is my VBA code:

Option Compare Database


Private Sub BranchState_AfterUpdate()
Dim BranchName As String
Dim BranchCity As String
Dim PayerName As String
BranchState = Me.BranchState.Value
Me!BranchName.Value = Me.PayerName.Value & " " & "-" & " " & Me.BranchCity & "," & " " & Me.BranchState

End Sub

Private Sub PayerID_AfterUpdate()
Dim PayerID As String

If Not Me.PayerID.Value Like "PY*" Then
MsgBox "Your payer ID must begin with 'PY'"
Cancel = True

End If


If Not Len(Me.PayerID.Value) = 11 Then
MsgBox "Your payer ID must be 11 characters in length"
Cancel = True

End If


Dim AIMCarrierBranchCode As String
If Not Len(Me.PayerID.Value) = 11 Then
Cancel = True
Else
PayerID = Me.PayerID.Value
Me!AIMCarrierBranchCode.Value = Right(PayerID, 9)

End If

If Not Me.PayerID.Value Like "PY*" Then
Cancel = True
Else
PayerID = Me.PayerID.Value
Me!AIMCarrierBranchCode.Value = Right(PayerID, 9)

End If

Dim AIMCarrierParentCode As String
If Not Me.PayerID.Value Like "PY*" Then
Cancel = True
Else
PayerID = Me.PayerID.Value
Me!AIMCarrierParentCode.Value = Mid(PayerID, 3, Len(PayerID) - 6)
Me!AIMCarrierParentCode.Value = Me.AIMCarrierParentCode & "0000"

End If


Dim LoadDate As String
LoadDate = DateTime.Now
Me!LoadDate.Value = Format(LoadDate, "mm/dd/yyyy")

Dim EffectiveDate As String
EffectiveDate = DateTime.Now
Me!EffectiveDate.Value = Format(EffectiveDate, "mm/dd/yyyy")


End Sub

Private Sub PayerID_BeforeUpdate(Cancel As Integer)

End Sub

 

[Micron]

You have a different issue now, so maybe this would be better off as a new thread. At first it was error 13, now it's about updating fields?
Sometimes people who think they can't help with an issue don't follow the thread. Or they think it's being handled so they don't follow the thread. If you post a thread when it's an unrelated issue, you might do better. However, when posting more than a snippet of code please enclose it in code tags (use vba button on posting toolbar) and use proper indentation in that code.

Whether or not you start anew is entirely up to you of course, but regardless, you have to explain something about the form fields you're referring to. If they are bound to table or query fields (the control has a control source property that is the name of a field - see Data tab on property sheet in design view) then if you leave a record on a bound form, you cannot stop it from creating the new record in the AfterUpdate events - too late, it is done. You must use BeforeUpdate event and there are usually two to choose from - the control event and the form event.

Unlike this example
Private Sub cmbOrderID_BeforeUpdate(Cancel As Integer)
this event
Private Sub PayerID_AfterUpdate()
has no Cancel option so including Cancel = True will have no effect. To reiterate, you cannot cancel what is already done. Also if those controls have an expression as a control source (e.g. =myOtherControl) then they cannot be bound and moving off the record won't save that data.