Creating Salutation Rules (1 Viewer)

access17401

Registered User.
Local time
Today, 05:12
Joined
Aug 24, 2017
Messages
33
So I have a complicated question (it’s probable not that hard but I am struggling), I have the following fields.

A_Title – Title, data input
A_FirstName – First name, data input
A_MiddleName – Middle name, data input
A_LastName – Last name, data input
Salutation – changes according to above

I am trying to make the salutation field change according to the other fields. So, I have an example.

The fields might contain Mr J J Smith and the salutation would be Mr Smith, but I now have updated information, his first name is John, so now I have Mr John J Smith and the salutation changes to John.

My salutation rules are:

Name Salutation
Mr Smith = Mr Smith – If no first name but title and last name
Mr J Smith = Mr Smith – First name only initial
J Smith = Default (Friend) – No title and first name initial only
Smith = Default (Friend) – No title or first name
John Smith = John – If first name always use
Mr John Smith = John – If first name always use

BUT I also need to be able to override it with a save button so it’s not accidentally done.

For example, John has asked us to send our reply to his wife Anne, so when I bring Johns profile up his fields are still Mr John J Smith but I need to make the salutation = Anne.

I have worked hard on learning code the last few weeks, worked out some cool things but after spending all last night trying to work this out, using ElseIf ect, I’m stumped.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:12
Joined
Aug 30, 2003
Messages
36,118
It would help if you posted your code. ElseIf would be a way to do it, with the tests in order of priority.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:12
Joined
Sep 21, 2011
Messages
14,038
I have seen databases where they have a KnownAs field.
You could set this with your rules, but be able to override it.

Eg. When in school we had a child called Paul Smith, his initials were D.W.S.P Smith

I don't use my first name, my given name is Paul. (my second name)

So have another field for the salutation?
 

access17401

Registered User.
Local time
Today, 05:12
Joined
Aug 24, 2017
Messages
33
Ok so this is what i have so far, please be kind only been writing code for two weeks, I am a very basic and early learning but trying really hard.

Private Sub Form_AfterUpdate()
' Telling it that is firstname is more then 2 characters use first name
If (Me.A_FirstName) >= "***" Then
[text_Salutation] = [A_FirstName]
' if the title and last name is not null use that combo
Elseif Not IsNUll [Me.A_Title] & Not IsNUll [Me.A_LastName] then
[text_Salutation] = [A_Title] & "" & [A_LastName]
' otherwise use the word - friend
Else: [txt_Salutation] = "Friend"
End If
End Sub

Once this is actually right i then want to add something like if override is selected use whatever i type in.

Thanks for the help everyone
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:12
Joined
Aug 30, 2003
Messages
36,118
I don't think the first test is valid, try

If Len(Me.A_FirstName & vbNullString) > 2 Then

The test for Null could be the same, testing for >0, or if you use IsNull it requires parentheses:

IsNull(Whatever)
 

access17401

Registered User.
Local time
Today, 05:12
Joined
Aug 24, 2017
Messages
33
Cool well i nearly have it (changed field names in my form)

Private Sub ADP_Title_AfterUpdate()
' Telling it that is firstname is more then 2 characters use first name
If Len(Me.ADP_FirstName & vbNullString) > 2 Then
[txt_Salutation] = [ADP_FirstName]
' if the title and last name is not null use that combo
ElseIf Not IsNull(ADP_Title) Or _
Not IsNull(ADP_LastName) Then
[txt_Salutation] = [ADP_Title] & " " & [ADP_LastName]
' otherwise use the word - friend
Else: [txt_Salutation] = "Friend"
End If
End Sub

The only thing that doesn't happen is if i delete either the title or the last name it does not change to friend when i want it too. I nee to delete both for it to happen
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:12
Joined
Aug 30, 2003
Messages
36,118
I think that test needs to be And rather than Or.
 

access17401

Registered User.
Local time
Today, 05:12
Joined
Aug 24, 2017
Messages
33
For anyone who may need the help in the future this is the code that worked:

Private Sub ADP_Firstname_AfterUpdate()
' Telling it that is firstname is more then 2 characters use first name
If Len(Me.ADP_FirstName & vbNullString) > 2 Then
[txt_Salutation] = [ADP_FirstName]
' if the title and last name is not null use that combo
ElseIf Not IsNull(ADP_Title) And _
Not IsNull(ADP_LastName) Then
[txt_Salutation] = [ADP_Title] & " " & [ADP_LastName]
' otherwise use the word - friend
Else: [txt_Salutation] = "Friend"
End If
End Sub
 

Users who are viewing this thread

Top Bottom