I need a running balance on a form linked to a check box

[bhoran]

I have a main form with 2 subforms, each subform has a option group box reconciled and unreconciled. I need a running total of all records which are flagged by the reconciled check box.

I was attempting to handle this by having the group option check box update the field QRRNCD to 1. Then I have a query that sums the value of all records on the table where QRRNCD = 1.

I was then looking at adding another subform (controlamount1 subform) into the main form with just the value field from that query being shown.

I.e. The subform with the option group box and the controlamount1 subform are both subforms of the main form but not of each other.

Problems:

1)I cannot get requery to work as it keeps coming back with the error; You can't use the applyfilter action on this window.

I have tried several other methods such as openform and openquery the do work but obviously do open a new window.

2)The summed value is always one record behind i.e. the update seem to be occuring after the query has been opened even though I attached the command to the after update action in the properties of the option group box.

I am already using the requery command in the main form but that was simple as the 2 subforms are its source so I have no need to give a control name.

I have seen other advice on this type of issue but it is a bit much for me to handle.

Any help would be greatly appreciated!

Cheers
BHoran

 

[jmeadows7]

One way I do running tallies or other things that need to be updated like this is to:
1. Write a query that brings back the 1 number that I need.
2. Drop a list box on the form with the source as the query written in step 1.
3. Either do a listbox.requery command when it should be udpated or give the user a button to click and put the listbox.requery in the onclick event if the update might take more than a second.

Hope this helps.

 

[DougP]

this is the syntax for requery of a subform

Me![subformName].Requery

If you have two or more tables in the recordsource of the subform it may cause problems too. DougP, MCP

 

[bhoran]

Thanks jmeadows, I like the idea of using the listbox much handier but I still can't get the syntax right.

I am currently writing a maco using requery as the command and then simply the name of the listbox ControlAmt1 I still get the same error "You can't use the applyfilter action on this window".

I also tried using the syntax from DougP and putting an after updated eventprocedure on the group options box with no luck. Any other ideas?

The important thing here is to have it auto recalculate after each selection as it will be used for validation upon exit.

Cheers
bhoran

 

[jmeadows7]

I'm not sure what your window looks like; however, in the query that you use behind the list box - you should be able to set the criterial fields equal to something like:

[Forms]![cmw035_EDI]![cbomw035_Customer]

where cmw035_EDI is your form name and cbomw035_Customer is a field on your window contaning the information from which to query the info. THe query should return only 1 field - the sum of the data on the window that you wish to see.

Then, use the wizard builder to drop a list box on the form, select your query, select the one field and it's done.

Use listbox.requery when want the list box to update.

You should then always be able to reference:
listbox.column(0,0) to find the value.

hope this helps.

 

[dduffy]

I have the query sorted fine, it is just the auto requery I am having difficulty with.

The way it is supposed to work is to auto requery after the user selects an option on the option group. (i.e. after the option group has updated the table)

The problem I am having is actually getting the requery to run on that action.

It is actually setting the listbox.requery function that I am having trouble with, I have not used this before and I am uncertain of the syntax especially as the action is on the subform and the listbox is on the form.

Where do I use the listbox.requery I am trying to attach it to the after update on the options group in the subform.

I will try it on a separate button and see if I can get the syntax right for that.

Thanks for the help, I am finding this very frustrating.

 

[jmeadows7]

Okay,
it may be a little frustrating - but that's where you learn and move forward.
The great thing about this method is that the list box isn't attached to anything - it's unabound. It's data source is the query which has as criteria something on the form. Here's a code example from one module I have written.
The list boxes update the amount of inventory "adjusted" in and out of the inventory. It recalcualtes the amount in and out and assigns it to text boxes on the window. I use the same list box to calcualte the in and out and then transfer the value to the text field. Since you are only calculating one field - you only need to have the list box display the one row it will return.


'*
'* If the user has a "dirty" (unwritten record) tell them
'*

If Me.Dirty Then
Beep
MsgBox "Unsaved records found!@Save records by clicking on another!@Try Your Command Again!", vbInformation + vbOKOnly, "Information"
Exit Sub
End If

'*
'* If the users wishes to know what has been done so far -
'* recalc and provide the information.
'*

'*
'* First re-set variables in case nothing is found.
'*

txtsw014_Weight_In = 0
txtsw014_Loads_In = 0
txtsw014_Weight_Out = 0
txtsw014_Loads_Out = 0


txtsw014_Query_Adj_Ind = "I"
lstsw014_Recalc.Requery

If lstsw014_Recalc.ListCount > 0 Then
txtsw014_Weight_In = lstsw014_Recalc.Column(0, 0)
txtsw014_Loads_In = lstsw014_Recalc.Column(1, 0)
End If

txtsw014_Query_Adj_Ind = "O"
lstsw014_Recalc.Requery

If lstsw014_Recalc.ListCount > 0 Then
txtsw014_Weight_Out = lstsw014_Recalc.Column(0, 0)
txtsw014_Loads_Out = lstsw014_Recalc.Column(1, 0)
End If

txtsw014_Net = txtsw014_Weight_In - txtsw014_Weight_Out

 

[dduffy]

Cool, thanks I have got it sorted. Thanks for all your help.

Cheers
Brad