Concatentation with military rules...extremely tricky :O( 2

[BullHalseyUSN]

Greetings!

I have a form which enters information on members into our database.

I am attempting to generate a datum called "SALUTATION" which is a member's Rank or Rate/Rating and name. The rules for this become extremely complicated. Let's start simple. For example, "BM2 Smith"

BM is a RATE, which is a person's job.
2 is a RATING, which is a person's seniority.
SMITH, well, you know that Smith is the guy's name!

I currently have the following:

Private Sub SocialSecurityNumber_AfterUpdate()
[SALUTATION] = Me![CmbRate].Column(1) & " " & Me![CmbRankOrRating].Column(1) & " " & Me![Name]
Forms![frmtblSailors].[DeptId].SetFocus
Me.Refresh
End Sub

Which gets me:

BME5 Smith

Why? Because CmbRate drops down all the various RATES. CmbRankorRating drops down the various RANKS (officers) and RATINGS (seniority of enlisted persons).

Where does "5" come from? RATINGS run E1 to E9.

I need to stuff the following logic into this concatenation in order to get this important result:

An E1 needs to be displayed as SR (for “Seaman Recruit”). So, we’d have BMSR Smith
E2 = SA (“BMSA”)
E3 = SN (“BMSN”)
E4 = 3 (for “Third Class”, so “BM3”)
E5 = 2 (“BM2”)
E6 = 1 (“BM1”)
E7 = C (for “Chief”, so “BMC”)
E8 = CS (for “Senior Chief”, so “BMCS”)
E9 = CM (for “Master Chief”, so “BMMC”)

Lastly, and perhaps for another day, if the user chooses one of the officer designations from the CmbRankOrRating (Ensign, LTJG, LT, LCDR, CDR, CAPT) then he shouldn’t be allowed to choose a RATE because officers don’t have ‘em!

Can anyone be so kind as to provide guidance on how I can attack this sticky mess?

Many thanks! BH

 

[hneal98]

You can try this:

Private Sub SocialSecurityNumber_AfterUpdate()
Dim sRatings as sting

select case Me![CmbRate].Column(1)
case "E2"
sRatings = "SA" '(“BMSA”)
case "E3"
sRatings = "SN" '(“BMSN”)
case "E4"
sRatings = "3" '(for “Third Class”, so “BM3”)
case "E5"
sRatings = "2" '(“BM2”)
case "E6"
sRatings = "1" '(“BM1”)
case "E7"
sRatings = "C" '(for “Chief”, so “BMC”)
case "E8"
sRatings = "CS" '(for “Senior Chief”, so “BMCS”)
case "E9"
sRatings = "CM" '(for “Master Chief”, so “BMMC”)
else
'Put in Error Message here.
end select

[SALUTATION] = sRatings & " " & Me![CmbRankOrRating].Column(1) & " " & Me![Name]
Forms![frmtblSailors].[DeptId].SetFocus
Me.Refresh
End Sub

 

[hneal98]

Actually, you could do this:

select case Me![CmbRate].Column(1)
case "E2"
sRatings = "SA" '(“BMSA”)
case "E3"
sRatings = "SN" '(“BMSN”)
case "E4"
sRatings = "3" '(for “Third Class”, so “BM3”)
case "E5"
sRatings = "2" '(“BM2”)
case "E6"
sRatings = "1" '(“BM1”)
case "E7"
sRatings = "C" '(for “Chief”, so “BMC”)
case "E8"
sRatings = "CS" '(for “Senior Chief”, so “BMCS”)
case "E9"
sRatings = "CM" '(for “Master Chief”, so “BMMC”)
else
'Put in Error Message here.
end select

if Me![CmbRankOrRating].Column(1) = "Ensign" or _
Me![CmbRankOrRating].Column(1) = "LTJG", or _
Me![CmbRankOrRating].Column(1) = "LT", or _
Me![CmbRankOrRating].Column(1) = "LCDR", or _
Me![CmbRankOrRating].Column(1) = "CDR", or _
Me![CmbRankOrRating].Column(1) = "CAPT&quot then
[SALUTATION] = Me![CmbRankOrRating].Column(1) & " " & Me![Name] Forms![frmtblSailors].[DeptId].SetFocus
else
[SALUTATION] = sRatings & " " & Me![CmbRankOrRating].Column(1) & " " & Me![Name] Forms![frmtblSailors].[DeptId].SetFocus
end if

Me.Refresh
End Sub

 

[BullHalseyUSN]

Thanks, pal!

Here is what I wound up with:

Private Sub SocialSecurityNumber_AfterUpdate()
Dim sRatings As String

Select Case Me![CmbRankOrRating].Column(1)
Case "E2"
sRatings = "SA" '(“BMSA”)
Case "E3"
sRatings = "SN" '(“BMSN”)
Case "E4"
sRatings = "3" '(for “Third Class”, so “BM3”)
Case "E5"
sRatings = "2" '(“BM2”)
Case "E6"
sRatings = "1" '(“BM1”)
Case "E7"
sRatings = "C" '(for “Chief”, so “BMC”)
Case "E8"
sRatings = "CS" '(for “Senior Chief”, so “BMCS”)
Case "E9"
sRatings = "CM" '(for “Master Chief”, so “BMMC”)
Case Else
sRatings = Me![CmbRankOrRating].Column(1)

End Select

[SALUTATION] = Me![CmbRate].Column(1) & sRatings & " " & Me![Name]
Forms![frmtblSailors].[DeptId].SetFocus
Me.Refresh
End Sub

I still have the one wrinkle of trying to figure out what to do if an officer is selected. On clicking an officer rank CmbRankOrRating, I should have Me![CmbRate].Column(1)="n/a".

Thanks again!

 

[BullHalseyUSN]

I didn't see your second post.

Thanks!

 

[hneal98]

No problem.

 

[JonFer]

Another alternative that may be easier to maintain is to create a table with that maps the ranks to the codes. Then change the combo box record source to a query that combines the ranks with the codes. You can reference the code with .Column(2) instead instead of .Column(1) and avoid the case statement.