Help With format- Total By Listcount (1 Viewer)

[Shaunk23]

I have a main table.. the main table has fields for TotalGross / TotalCube / TotalNet etc..

Another table linked ( 1-many) hold the actual cargo.. (crates etc..) A listbox on the main shipment page holds the values in columns (seal number / Gross / Net / Tare / Cube.

This all works fine.. The numbers are setup in table as double & on the forms as fixed w/ 2 as decimal place.. this seems to work fine as well.. list displays correct - it calculates the cubic feet etc fine.

On the main page i need to get the totals in some seperate text boxes... Here is the code i had written but im getting a DATA MISMATCH and i cant seem to figure it out.


Option Compare Database
Option Explicit

Public Function UpdateMilBoxTotals()

Dim TotalGross As Double
Dim TotalTare As Double
Dim totalPc As Double
Dim TotalNet As Double
Dim totalcube As Double
Dim Listcount As Integer
Dim I As Integer


On Error GoTo ErrorHandler


If Form_AeclMilitaryMainScreen.MilBoxList.Listcount = 0 Then
Exit Function
End If

TotalGross = 0
Listcount = Form_AeclMilitaryMainScreen.MilBoxList.Listcount
I = 1
Do
TotalGross = TotalGross + Form_AeclMilitaryMainScreen.MilBoxList.Column(1, I)
I = I + 1
Loop Until I = Listcount

Form_AeclMilitaryMainScreen.ManualGross = TotalGross


ExitRoutine:
Exit Function

ErrorHandler:
MsgBox Err.Description
Resume ExitRoutine


End Function

 

[WayneRyan]

Shaunk,

You need to have I loop from --> 0 To Listcount - 1
The ListBox entries are "0" based.

Also, the ListBox items are not numeric. They are stored as text.

I don't readily see where your mismatch error is if it isn't that.
What line throws the error?

I'd think it'd be easier to use:

Form_AeclMilitaryMainScreen.ManualGross = DSum("[Field]", "YourTable", "Criteria")

Just substitute the correct field and table names in the above and use the
SQL for your Listbox to give you the criteria.

hth,
Wayne