Full Version: Code Help - Is Null Not Working
UtterAccess Forums > Microsoft® Access > Access Forms
sweetwater
Hello Again Utters:
opefully I am posting this in the correct forum. I have the following code in my salutation field.
Private Sub Salutation_AfterUpdate()
If ([Salesperson] = "Nelson Maughan") Then
[Salutation] = [FirstName] + " and " + [Spouse]
Else
If IsNull([Spouse]) Then
[Salutation] = [FirstName]
Else
[Salutation] = [Title] + " " + [LastName]
End If
End If
End Sub
However, I input a record where the last name is Jacob, first name is Sue and the spouse field is blank. The salutation came up with Ms Jacob instead of just Sue (Salesperson is Nelson Maughan). I do believe this was working at one time. What am I missing?
Thanks!
Maria
sweetwater
As an addendum, I also have the following code for the following fields to make the first letter upper case.
Private Sub First_Name_AfterUpdate()
Me![FirstName] = StrConv(Me![FirstName], 3)
End Sub
Private Sub Last_Name_AfterUpdate()
Me![LastName] = StrConv(Me![LastName], 3)
End Sub
Private Sub Spouse_AfterUpdate()
Me![Spouse] = StrConv(Me![Spouse], 3)
End Sub
Wasn't sure if this has anything to do with my dilemma.
Thanks!
vtd
The Field [Spouse] may be an empty String on non-printable characters and not Null.
You can check for all 3 cases above by replacing
If IsNull([Spouse]) Then
with
If Trim(Nz(Me.Spouse, "")) = "" Then
BTW, you should use the qualifier Me if the first set of code posted.
sweetwater
Thank you! I am new to this and self teaching. Can you please explain when you say qualifier Me in the first code posted. But first let me see if I have my own question answered. I am assuming that I should put Me. in front of each field name. In doing so, that would be for the current record? Am I right?
My the way, tried your code and it did not work. I compiled and re-entered my record. It still comes up with Ms Jacob. Any other suggestions?
Thanks!
vtd
>>In doing so, that would be for the current record? Am I right?<<
That would be the CurrentRecord of the current Form.
Could you explain what you are trying to do, espcially the fact that your code is trying to change the Value of the Control [Salutation] in the AfterUpdate Event of the same Control? Usually, the AfterUpdate Event only fires when the user enters something in the Control ([Salutation]) and your code seems to want to change its Value to something completely different from what the user has just entered???
sweetwater
Hi VTD,
I have 13 different salespeople here. The fields are the normal N,A,C,S & Z. Then comes the salutation field. There is one salesperson who would like his salutation to be the first name "and" spouse (if one) vs the title and last name. There is always one in every crowd, isn't there! So, maybe the after update is the wrong way to go. So are you saying that this should not be an event, it should just be code?
Thanks so much for your help! This is bugging me and I would love to get it working correctly.
Maria
vtd
>>The fields are the normal N,A,C,S & Z.<<
orry. The Fields are normal to you but they don't mean anything to me.
Ostill don't understand why you use the Salutation_AfterUpdate to change the value of [Salutation] completely???
Basically, the user can type in anything in this Control, says "xyz", and as soon as the user tabs out of this Control, you code will change it to something completely different regardless of the user's input.
Is that what you want your code to do? If not, please describe the relevant Table(s), the RecordSource of your Form, general details of your Form and the process as you see and what you want the code to do in a particular step of the process.
>>So, maybe the after update is the wrong way to go. So are you saying that this should not be an event, it should just be code?<<
Code is usually activated by some Event on the Form in this context, i.e. Form process. I think you may be using the wrong Event but I don't know what you are trying to do.
sweetwater
Hi VTD and thank you for trying to help me.
>>The fields are the normal N,A,C,S & Z.<<
>>Sorry. The Fields are normal to you but they don't mean anything to me.<<
These fields are Name, Address, City, State & Zip. Sorry for the non-clarification.
>>I still don't understand why you use the Salutation_AfterUpdate to change the value of [Salutation] completely???<<
Odon't need to use the Salutation_AfterUpdate, but I really don't know what event to use when I just want the field to update.
>>Basically, the user can type in anything in this Control, says "xyz", and as soon as the user tabs out of this Control, you code will change it to something completely different regardless of the user's input.
Is that what you want your code to do? If not, please describe the relevant Table(s), the RecordSource of your Form, general details of your Form and the process as you see and what you want the code to do in a particular step of the process. <<
Yes, this is exactly what I want the code to do. All I am trying to accomplish is the Salutation being automatically updated based on the Salespersons name. If it is Nelson Maughan then the salutation should read [FirstName] + [Spouse] or just [FirstName] if the spouse field is blank. If the Salesperson is anyone but Nelson Maughan, then the Salutation should automatically be [Title] + [LastName]. Hopefully that made this clearer in what I am trying to accomplish.
Thanks again for your help!
vtd
In this case, you need to to use the AfterUpdate EVent of each of the Controls use in evaluating the Value of the Control [Salutation], namely:
[Salesperson]
* [FirstName]
* [Spouse]
* [Title]
You may also need to use the Form_Current Event to display the appropriate value in the Control [Salutation] on viewing the Record also.
All the Subs should be similar to what we discussed earlier...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.