So this is probably a horribly sloppy code, but I'm still new to this, so please bear with me. I had this working yesterday, but then lost half a day's progress due to file corruption, and now I can't remember for the life of me how I did it, and now I'm so frustrated I can barely see straight, let alone program right.
This code runs a small form that generates names based on policy data that can then be pasted onto the policy document PDFs that we keep on file. I got some help with it here before, so it may be familiar. Yesterday, I made some tweaks to make some fields optional and attach formatting to them, and the problem I'm having now is that even when the fields aren't used on the form, the code still pulls the extra spaces, commas, wording, etc that's part of the formatting for if there IS a value, and throws it into the name. I had it working perfectly yesterday, so I know it can be done. What am I missing?
Code:
Private Sub btnFileName_Click()
'Generates file name based on data in the generator.
'Declare variables.
Dim PolNum As String
Dim EndNum As Variant
Dim effDate As Date
Dim desc1 As String
Dim desc2 As Variant
Dim desc3 As Variant
Dim desc4 As Variant
Dim desc5 As Variant
Dim expiry As Variant
Dim ExpYear As Variant
Dim sFileName As String
PolNum = Me.ctlPolNum
ExpYear = Me.ctlExp
effDate = Me.ctlPolEffDt
'Endorsement number is optional.
If IsMissing(ctlEndNum.Value) = True Then
EndNum = Null
Else
If IsMissing(ctlEndNum.Value) = False Then
EndNum = Format("End#" & Me.ctlEndNum)
End If
'Expiration extension is optional.
If IsMissing(ctlExp.Value) = False Then
expiry = Format(" Extend Expiry thru " & ExpYear & ",")
ElseIf IsMissing(ctlExp.Value) = True Then
expiry = Null
End If
'Description 1 must be provided.
desc1 = Format(" " & Me.ctlDesc1)
'Description 2 optional.
If IsMissing(ctlDesc2.Value) = False Then
desc2 = Format(", " & Me.ctlDesc2)
ElseIf IsMissing(ctlDesc2.Value) = True Then
desc2 = Null
End If
'Description 3 optional.
If IsMissing(ctlDesc3.Value) = False Then
desc3 = Format(", " & Me.ctlDesc3)
ElseIf IsMissing(ctlDesc3.Value) = True Then
desc3 = Null
End If
'Description 4 optional.
If IsMissing(ctlDesc4.Value) = False Then
desc4 = Format(", " & Me.ctlDesc4)
ElseIf IsMissing(ctlDesc4.Value) = True Then
desc4 = Null
End If
'Description 5 is optional.
If IsMissing(ctlDesc5.Value) = False Then
desc5 = Format(", " & Me.ctlDesc5)
ElseIf IsMissing(ctlDesc5.Value) = True Then
desc5 = Null
End If
'Format into name (policynumber(space)(space)end###(space)(space)effectivedate(year.mo.day) _
(space)(space)descriptions.
sFileName = PolNum & " " & EndNum & " " & Format(effDate, "YYYY.MM.DD") & " " _
& expiry & desc1 & desc2 & desc3 & desc4 & desc5
Me.ctlFileName = sFileName
End If
End Sub