Results 1 to 2 of 2
  1. #1
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156

    Optional Variables in sequence still taking Format, even when not used.


    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

  2. #2
    IncidentalProgrammer is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2014
    Location
    Texas
    Posts
    156
    So of course, once I posted it, I remembered how to fix it. Replace "IsMissing" with "IsNull". Derp!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 10-07-2013, 09:37 AM
  2. Replies: 0
    Last Post: 08-13-2013, 09:35 PM
  3. Replies: 2
    Last Post: 10-23-2012, 12:18 PM
  4. Replies: 3
    Last Post: 07-26-2012, 03:09 PM
  5. Replies: 3
    Last Post: 06-04-2012, 01:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums