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]

It's not working is not very helpful. Means what? Wrong value? Error message? Nothing seems to happen? Something else?
Last 9 characters will always be what you want (not sometimes 8 or 7 or ...)? You need to ensure those 9 are only numbers (seems to be the case)?

What doesn't look right is if you're updating PayerId thus the event occurs, you seem to have the equals backwards. You just made an manual entry there and now you're using code to set it to something else. Reverse the two parts?

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

 

[jrsarrat]

It's not working is not very helpful. Means what? Wrong value? Error message? Nothing seems to happen? Something else?
Last 9 characters will always be what you want (not sometimes 8 or 7 or ...)? You need to ensure those 9 are only numbers (seems to be the case)?

What doesn't look right is if you're updating PayerId thus the event occurs, you seem to have the equals backwards. You just made an manual entry there and now you're using code to set it to something else. Reverse the two parts?

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

I reversed the code. Here's the error message I'm getting. I was getting the same message before as well:

 

[Micron]

The message means the value (or lack of one) that you're trying to put in a field is not allowed for that field. Examples would be
- trying to put text in a number field
- trying to put text in a date field (which is basically a number field)
- trying to insert a null into a field that doesn't allow null

Put a break point at the start of the code and cause it to run. Step through (F8) and see what values PayerID and AIMCarrierBranchCode hold. One way to do that is to type into the immediate window and hit Enter, such as

?Me.PayerID

If you get an empty line instead of a value, then you have what's called an empty string (or zls - zero length string). IIRC, if the value is null, you get that word, but I can't recall for sure.

 

[jrsarrat]

The message means the value (or lack of one) that you're trying to put in a field is not allowed for that field. Examples would be
- trying to put text in a number field
- trying to put text in a date field (which is basically a number field)
- trying to insert a null into a field that doesn't allow null

Put a break point at the start of the code and cause it to run. Step through (F8) and see what values PayerID and AIMCarrierBranchCode hold. One way to do that is to type into the immediate window and hit Enter, such as

?Me.PayerID

If you get an empty line instead of a value, then you have what's called an empty string (or zls - zero length string). IIRC, if the value is null, you get that word, but I can't recall for sure.

Thanks, Micron! I am not too skilled with VBA in order to troubleshoot this error message. Do you have any ideas on how else I can auto-populate the 2nd field based on entry of the first?

Thanks!

 

[Micron]

Your problem isn't vba - it's data incompatibility as I've noted. Not much that I can do to help until you figure out which of the situations I posted applies here (or perhaps a different one). I've given some instruction as to how to troubleshoot that - did you try any of it?

Do you have any ideas on how else I can auto-populate the 2nd field based on entry of the first?

You're already on the right track there. The issue is the data - or lack of it.

 

[jrsarrat]

I looked at my data validations and none of your suggestions appear to be the problem. I have one record in my table, as my database is a newly created one.

 

[jrsarrat]

AIMCarrierBranchCode is the second primary key in my table. Could this be a problem? I triggered the code to run by pressing F8 and the same error message displays.

 

[jrsarrat]

One way to do that is to type into the immediate window and hit Enter, such as

?Me.PayerID

Can you elaborate on this please? I really appreciate your time today.

 

[Micron]

Need to see something. At least pics of table - both design view showing field data types and pic of table showing your record might help.
NP with trying to help you, but if you can't follow that simple instruction it must be because you don't know what the immediate window is? In that case, would be good for you to Google it as there's lots you can learn - too much for this thread I think. Could also try this in your code:

Private Sub PayerID_AfterUpdate()
msgbox Me.PayerID.Value.TypeName
msgbox Me.AIMCarrierBranchCode.Value.TypeName

End

and report what you get. When doing F8 you don't attempt to execute a line that you know is faulty. You stop before that and check variable and/or form field values as you go. However, a line has to execute in order to make any changes, so in this case you would stop when the problem line is highlighted.

Posting your db in some sort of drop box might be an option?