Empty crosstab colums killing Access 2007

[armoredcars]

I created a fixed header cross tab query that totals up how many photos of each size there is in an order. I wrote some visual basic code to total up the price (kind of complex with the different packages) but any orders without at least one of every size photo completely locks up Access. The following is the code:


Private Sub Text66_DblClick(Cancel As Integer)
Dim Units
Dim UnitsCache
Dim PackagesCache
Dim F4x5Cache
Dim F5x7Cache
Dim E8x10Cache
Dim E11x14Cache
Dim S16x21Cache
Dim T21x24Cache
Dim WalletsCache
Dim T4x5
Dim T5x7
Dim T8x10
Dim T11x14
Dim T16x21
Dim T21x24
Dim TWallets

If [4x5] = Null Then
T4x5 = 1
Else
T4x5 = [4x5]
End If

If [5x7] = Null Then
T5x7 = 1
Else
T5x7 = [5x7]
End If

If [8x10] = Null Then
T8x10 = 1
Else
T8x10 = [8x10]
End If

If [11x14] = Null Then
T11x14 = 1
Else
T11x14 = [11x14]
End If

If [16x21] = Null Then
T16x21 = 1
Else
T16x21 = [16x21]
End If

If [21x24] = Null Then
T21x24 = 1
Else
T12x24 = [21x24]
End If

If [Wallets] = Null Then
TWallets = 1
Else
TWallets = [Wallets]
End If



F4x5Cache = T4x5
F5x7Cache = T5x7
E8x10Cache = T8x10
E11x14Cache = T11x14
S16x21Cache = T16x21
T21x24Cache = T21x24

WalletsCache = TWallets \ 8
UnitsCache = T8x10 + T5x7 \ 2 + T4x5 \ 4

Text58 = 0
Text56 = " "
Text54 = " "
Text52 = " "

RerunPackages:
PackagesCache = "x"
If [T21x24Cache] > 0 And [WalletsCache] >= 2 And [UnitsCache] >= 4 Then
PackagesCache = "F"
[WalletsCache] = [WalletsCache] - 2
[UnitsCache] = [UnitsCache] - 4
[T21x24Cache] = [T21x24Cache] - 1
Text58 = Text58 + 439
GoTo FoundOne
End If

If [S16x21Cache] > 0 And [WalletsCache] >= 2 And [UnitsCache] >= 4 Then
PackagesCache = "E"
[WalletsCache] = [WalletsCache] - 2
[UnitsCache] = [UnitsCache] - 4
[S16x21Cache] = [S16x21Cache] - 1
Text58 = Text58 + 305
GoTo FoundOne
End If

If [E11x14Cache] > 0 And [WalletsCache] >= 1 And [UnitsCache] >= 3 Then
PackagesCache = "D"
[WalletsCache] = [WalletsCache] - 1
[UnitsCache] = [UnitsCache] - 3
[E11x14Cache] = [E11x14Cache] - 1
Text58 = Text58 + 236
GoTo FoundOne
End If

If [WalletsCache] >= 2 And [UnitsCache] >= 4 Then
PackagesCache = "C"
[WalletsCache] = [WalletsCache] - 2
[UnitsCache] = [UnitsCache] - 4
Text58 = Text58 + 169
GoTo FoundOne
End If

If [WalletsCache] >= 1 And [UnitsCache] >= 3 Then
PackagesCache = "B"
[WalletsCache] = [WalletsCache] - 1
[UnitsCache] = [UnitsCache] - 3
Text58 = Text58 + 127
GoTo FoundOne
End If

If [WalletsCache] >= 1 And [UnitsCache] >= 2 Then
PackagesCache = "A"
[WalletsCache] = [WalletsCache] - 1
[UnitsCache] = [UnitsCache] - 2
Text58 = Text58 + 89
GoTo FoundOne
End If


FoundOne:
If PackagesCache <> "x" Then
If Text56 = " " Then
Text56 = PackagesCache
GoTo RerunPackages
ElseIf Text54 = " " Then
Text54 = PackagesCache
GoTo RerunPackages
ElseIf Text52 = " " Then
Text52 = PackagesCache
GoTo RerunPackages
End If
End If

UnitsCache = (T8x10 + T5x7 \ 2 + T4x5 \ 4) - UnitsCache
Do
If F4x5Cache < 4 Or UnitsCache = 0 Then
GoTo Skip4x5
End If
F4x5Cache = F4x5Cache - 4
UnitsCache = UnitsCache - 1
Loop
Skip4x5:
Do
If F5x7Cache < 2 Or UnitsCache = 0 Then
GoTo Skip5x7
End If
F5x7Cache = F5x7Cache - 2
UnitsCache = UnitsCache - 1
Loop
Skip5x7:
Do
If E8x10Cache < 1 Or UnitsCache = 0 Then
GoTo Skip8x10
End If
E8x10Cache = E8x10Cache - 1
UnitsCache = UnitsCache - 1
Loop
Skip8x10:

Text42 = 0
Redo:
If WalletsCache >= 5 Then
Text42 = [Text42] + 40
WalletsCache = [WalletsCache] - 5
GoTo Redo
End If
If WalletsCache = 4 Then
Text42 = [Text42] + 37
ElseIf WalletsCache = 3 Then
Text42 = [Text42] + 33
ElseIf WalletsCache = 2 Then
Text42 = [Text42] + 28
ElseIf WalletsCache = 1 Then
Text42 = [Text42] + 18
End If

Text28 = [F4x5Cache] * 12
Text30 = [F5x7Cache] * 25
Text32 = [E8x10Cache] * 40
Text34 = [E11x14Cache] * 110
Text36 = [S16x21Cache] * 155
Text38 = [T21x24Cache] * 249
Text40 = [25x31] * 362
Text44 = [16x20 Collage] * 210
Text46 = [Hard Cover w/Poetry] * 212
Text48 = [Hard Cover Book] * 98
Text50 = [Soft Cover Book] * 68

Text66 = Text28 + Text30 + Text32 + Text34 + Text36 + Text38 + Text40 + Text42 + Text44 + Text46 + Text48 + Text50 + Text58 - Text60

End Sub

 

[Banana]

Few pointers:

1) You aren't strong-typing your variables. Instead of:

Dim Something

You should be specifying the data type

Dim SomeString As String
Dim SomeInteger As Integer
Dim SomeBoolean As Boolean
....

2) I hope you have "Option Explicit" at top of your module, especially if you're using that many variables. Without that, VB will initalize a new variable for anything that's not same as what you Dim'ed.

3) If you have multile If/End, I'd look into using this instead:

Select Case Variable
   Case 0
      'Do something
   Case 1
      'Do something
   Case 2 To 10
      'Do something
    Case 11, 13, 15
      'Do something
    Case Else
      'Do something
End Select

4) I'd really use MSDN's help on how to make a crosstab report. Here's a link. The report not only allows for dynamic numbers of column but also is simpler to implement because you're not making any assumptions which could fail if you ever needed to modify how you report it (e.g. suppose you had a new size?)

HTH.