Case Statement Help Needed

[topdesk123]

Good morning. The following code creates certain sentences in a mail merge document depending on the choices made by the user. Clients get 3 options for a new bid: Whole Deck, Horizontal Only, or a price for both. I am also posting the paragraph I am having trouble with (it's in the Two Option letter).

Here's the code:

Private Sub Command181_Click()
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenQuery "DELETETEMPLETTERTABLEPC"
Me![HorizontalSentence] = Null
DoCmd.RunCommand acCmdSaveRecord

Dim lCol_IncludeAreas As New Collection 'Deck
Dim lCol_IncludeAreas6 As New Collection 'OpeningLine
Dim lCol_IncludeAreas14 As New Collection 'Horizontal


Dim lStr_FullSent As String
Dim lStr_FullSent2 As String
Dim lStr_FullSent3 As String
Dim lStr_FullSent4 As String
Dim lStr_FullSent5 As String
Dim lStr_FullSent6 As String
Dim lStr_FullSent7 As String
Dim lStr_FullSent8 As String
Dim lStr_FullSent9 As String
Dim lStr_FullSent10 As String
Dim lStr_FullSent11 As String
Dim lStr_FullSent12 As String
Dim lStr_FullSent13 As String
Dim lStr_FullSent14 As String
Dim lInt_Idx As Integer
Dim lInt_Idx2 As Integer
Dim lInt_Idx3 As Integer
Dim lInt_Idx4 As Integer
Dim lInt_Idx5 As Integer
Dim lInt_Idx6 As Integer
Dim lInt_Idx7 As Integer
Dim lInt_Idx8 As Integer
Dim lInt_Idx9 As Integer
Dim lInt_Idx10 As Integer
Dim lInt_Idx11 As Integer
Dim lInt_Idx12 As Integer
Dim lInt_Idx13 As Integer
Dim lInt_Idx14 As Integer
Me![ActBidFinal] = (Nz(Me![SidingActual]) + (Nz(Me![AsphaltActual]) + (Nz(Me![CedarActual]) + (Nz(Me![ConcreteActual]) + (Nz(Me![ActGaz]) + (Nz(Me![ActPor]) + (Nz(Me![ActPer]) + (Nz(Me![FenceActual]) + (Nz(Me![ActDeck]) + (Nz(Me![DockAct]) + (Nz(Me![PlaySysAct]) + (Nz(Me![ActFurniture])))))))))))))
DoCmd.RunCommand acCmdSaveRecord

'Deck

If (Forms![needs letters]![Walking Surfaces] = True) And (Forms![needs letters]![#ofWS] = "1") Then
lCol_IncludeAreas.Add "walking surface of your deck"
End If

If (Forms![needs letters]![Walking Surfaces] = True) And (Forms![needs letters]![#ofWS] > "1") Then
lCol_IncludeAreas.Add "walking surfaces of your deck"
End If

If (Forms![needs letters]![Fascia] = True) Then
lCol_IncludeAreas.Add "fascia"
End If

If (Forms![needs letters]![Railings] = True) Then
lCol_IncludeAreas.Add "railings"
End If

If (Forms![needs letters]![Spindles] = True) Then
lCol_IncludeAreas.Add "spindles"
End If

If (Forms![needs letters]![Steps] = True) And (Forms![needs letters]![# of Steps] = "1") Then
lCol_IncludeAreas.Add "step"
End If

If (Forms![needs letters]![Steps] = True) And (Forms![needs letters]![# of Steps] > "1") Then
lCol_IncludeAreas.Add "steps"
End If

If (Forms![needs letters]![Support Posts] = True) Then
lCol_IncludeAreas.Add "support posts"
End If

If (Forms![needs letters]![Planter Boxes] = True) And (Forms![needs letters]![#ofPlanterBoxes] = "1") Then
lCol_IncludeAreas.Add "planter box"
End If

If (Forms![needs letters]![Planter Boxes] = True) And (Forms![needs letters]![#ofPlanterBoxes] > "1") Then
lCol_IncludeAreas.Add "planter boxes"
End If

If (Forms![needs letters]![Lattice] = True) Then
lCol_IncludeAreas.Add "lattice"
End If

If (Forms![needs letters]![Skirting] = True) Then
lCol_IncludeAreas.Add "skirting"
End If

If (Forms![needs letters]![Benches] = True) And (Forms![needs letters]![# of Benches] > "1") Then
lCol_IncludeAreas.Add "benches"
End If

If (Forms![needs letters]![Benches] = True) And (Forms![needs letters]![# of Benches] = "1") Then
lCol_IncludeAreas.Add "bench"
End If

If (Forms![needs letters]![Other1] = True) Then
lCol_IncludeAreas.Add Forms![needs letters]![Detail1]
End If

If (Forms![needs letters]![Other2] = True) Then
lCol_IncludeAreas.Add Forms![needs letters]![Detail2]
End If

'In the future, if other choices need to be added, just copy the above IF
'statement and change the criteria

Select Case lCol_IncludeAreas.Count
Case 0
lStr_FullSent = vbNullString
Case 1
lStr_FullSent = lCol_IncludeAreas.Item(1) & " "
Case 2
lStr_FullSent = lCol_IncludeAreas.Item(1) & " and " & lCol_IncludeAreas.Item(2)
Case 3
lStr_FullSent = lCol_IncludeAreas.Item(1) & " " & lCol_IncludeAreas.Item(3)

Case Else
lStr_FullSent = vbNullString
For lInt_Idx = 1 To lCol_IncludeAreas.Count - 1
lStr_FullSent = lStr_FullSent & lCol_IncludeAreas.Item(lInt_Idx) & ", "
Next lInt_Idx
lStr_FullSent = Mid(lStr_FullSent, 1) & "and " & lCol_IncludeAreas.Item(lCol_IncludeAreas.Count)

End Select

Forms![needs letters]![DeckSentence] = lStr_FullSent & " for " & (FormatCurrency(Me![ActDeck]) & ". ")
Forms![needs letters]![HorizontalSentence] = "just the " & lStr_FullSent & " for " & (FormatCurrency(Me![HorizontalOnlyPrice]) & ". ")
DoCmd.OpenQuery "PC Customer Bid Letters2", acViewNormal
DoCmd.TransferText acExportDelim, , "templettertablepc", "c:\rooftodeckdb\templettertablepc.txt", True


End Sub

And here is the paragraph it produces:

ABCCompany will clean, brighten and seal/stain just the walking surfaces of your deck, fascia, railings, spindles, steps, bench, and boxes for $410.00. Or we will clean, brighten and seal/stain the walking surfaces of your deck, fascia, railings, spindles, steps, bench, and boxes for $400.00. Please let us know which of the two options listed above you would prefer. The last time we restored your deck the price was $390.00.

It should read:

ABCCompany will clean, brighten and seal/stain just the walking surfaces of your deck and steps for $410.00. Or we will bla bla bla....

I can't figure out how to enumerate (?) correctly....or something! If anyone is stuck inside on this rainy day, I'd so much appreciate any help I can get!

Thanks!!!

 

[JoeAtWork]

Well, following your code it looks like all the following statements evaluate to True:

   If (Forms![needs letters]![Fascia] = True) Then
      lCol_IncludeAreas.Add "fascia"
   End If
   
  If (Forms![needs letters]![Railings] = True) Then
      lCol_IncludeAreas.Add "railings"
   End If
   
  If (Forms![needs letters]![Spindles] = True) Then
      lCol_IncludeAreas.Add "spindles"
   End If

   If (Forms![needs letters]![Benches] = True) And (Forms![needs letters]![# of Benches] = "1") Then
      lCol_IncludeAreas.Add "bench"
   End If

   '-- Couldn't find the code that added "boxes" to the collection

So, lCol_IncludeAreas.Count is bigger than 3, and you hit the Case Else statement, where it builds your string "deck, fascia, railings, spindles, steps, bench, and boxes".