Recordset Problem (1 Viewer)

[Ali Edwards]

Hi - can anyone help with this one? It should be simple.......!

I have a mainform/ subform routine where I am trying to add the values of a control in all the subform's records and enter it in a field in the main form.

'subform name = Compilation_Form.
'memory = the field in the main form I wish to put the result in.
'TD = the field on each record (in the subform) whose value I am trying to take and add to the 'memory' field (in the main form), before moving to the next record and adding that value etc.

At the moment the 'memory' field will only show the value of the last record in the subform's recordset

'-------------------------
Dim memory As String
Dim rst As Recordset

'zero out the memory field to start
Me.memory = "0"

Set rst = Me.Compilation_Form.Form.Recordset

rst.MoveFirst
Do Until rst.EOF

Me.memory = Me.[Compilation_Form].Form![TD] + Me.memory.OldValue

rst.MoveNext
If Not rst.EOF Then
Me.Compilation_Form.Form.Bookmark = rst.Bookmark
End If
Loop

'--------------------------

 

[jgc31]

Ali

if memory is a string then

Me.memory = Me.[Compilation_Form].Form![TD] + Me.memory.OldValue

should be

Me.memory = Me.[Compilation_Form].Form![TD] & Me.memory.OldValue

if on the other hand it is a numerical value memory should be dimensioned as a integer / long etc

 

[tomj]

Add a total to the subform

I'm assuming your subform is a datasheet - if so, modify the subform and add a formfooter. Add a unbound field that sums the field, i.e., TD is the field you want the total of; the unbound field might be TotalTD. Set the control source for TotalTD to:

=NZ(Sum([TD])) (The 'nz' eliminates problems with nulls...)

Save the subform and go to your main form and create another field (bound or unbound) and set it's control source to:

=[Forms]![Mainform]![Mainform_subform]![TotalTD]

Insert your form names for MainForm and MainForm_Subform in the above.

Each time you enter or change a value in the subform, the value in the will update on the main form field.

 

[Ali Edwards]

That worked a treat!

Many thanks

 

[Ali Edwards]

Except that.......

I'd like to apply NZ(Sum([TD])) to the after update event of a tick box. I'm getting the error 'Sub or Function not defined'.

I have:

dim newval as String
newval = NZ(Sum([TD])) - Which is where the error occurs

..and thereafter I'm procesing the value of newval to change it's format etc..

Any ideas?

Many thanks!
Ali

 

[Rich]

Sum is not a vba function, you'd have to use DSum

 

[Ali Edwards]

Thanks Rich, you've obviously spotted my limited talent!

I tried Dsum before posting but unfortunately I am in Access 2K.

 

[Ali Edwards]

Sorry I should be more specific.

TomJ's solution was perfect for getting the sum of all TD values into a field in the Main form header [SumTD]. I now want to process the [SumTD] value and put the result in another field [CompileTime]. I need to do this with the After Update event of a tick box in the sub form:

This is the code I'm using to format the SumTD value. The problem is that [CompileTime] is showing the old value of [SumTD] (ie. [SumTD] is not updating quickly enough before the code runs and updates [CompileTime]).

Checkbox After_Update:

if me.checkbox=true then me.td=me.duration2 - me.duration1
if me.checkbox=false then me.td = "0"
form.refresh

'(Toms nifty code updates the [SumTD] because [SumTD] control source=NZ(Sum([TD]))

NewVal = SumTD
Hours = Nz(Int(NewVal/90000), 0)
NewVal = NewVal - (Hours*90000)
Minutes = Nz(Int(NewVal/1500), 0)
NewVal = NewVal - (Minutes*1500)
Seconds = Nz(Int(NewVal/25), 0)
NewVal = NewVal - (Seconds*25)
Frames = Nz(NewVal, 0)
TheAnswer = Format(Hours, "0#") & ":" & _
Format(Minutes, "0#") & ":" & _
Format(Seconds, "0#") & ":" & _
Format(Frames, "0#")

[forms]![MainForm]![CompileTime] = TheAnswer


I'd be very grateful for any help!!
Thanks

 

[Rich]

Why are you storing this in another field, you already have it displayed?

 

[Ali Edwards]

Hi - this'll take a while, forgive me!

The purpose of the database is to store video clip information along with timecode in and out points. The 'timecode in' fields consist of four fields; Hours, Minutes, Seconds and Frames. The 'timecodeout' fields are another four - Hours, Minutes, seconds and Frames.

The user enters the TC in which automatically tabs across the four fields, say:

01 - 02 - 03 - 04 (TC in is at 1 hour, 2 minutes, 3 seconds and 4 frames).

And TC out is, say:
05 - 06 - 07 - 08 (TC out is at 5 hours, 6 minutes, 7 seconds and 8 frames).

The [Duration1] field calculates the 'TC in' value in FRAMES and the [Duration2] field calculates the 'TC out' value in FRAMES (there are 25 frames per second). So [Duration1] = 93079 and [Duration2] = 459183

The [TD] field calculates the total amount of frames [Duration2] - [Duration1] (= 366104)

Then [SumTD] adds the values of all the [TD] fields (there's one in each record) BUT ONLY if the check box for that record is checked. That way clips can be selected or deselected and a total running time is displayed, helping to select the clips for a program.

Now, because the [SumTD] field shows the value in frames, I need it to be converted nd shown in HH:MM:SS:FF, hence the code shown previously. This would display the [SumTD] in another field [CompileTime] as 04:04:04:04 - my total running time for this program.

The [Duration1], [Duration2], [TD] and [SumTD] fields are all invisible.

Problem is that (at the moment) the [CompileTime] field shows the correct value when the checkbox is unticked and the old value when it is checked!
When the checkbox is ticked the [TD] value is instantly updated for the record, but the [CompileTime] value is calculated too slowly and is displaying the old [SumTD] value...

It's all pretty shoddy I guess to you guys but I'm still learning and it all works up to this point!

Ali