Problem with Form Calculations

[jhawkins]

I'm working on Access 2000 at the moment and i've got a small problem in regards to coding a calculation involving a form and a subform.

The form i have is called Player Overall Stats and the problem area is with two text boxes i have created to display win and loss values. The problem arises where i'm trying to find a way to take the value of a result field, which is either win or lose. I'm hoping to convert this into a number, so for example, in the subform there are 2 wins and 1 loss, i'm not entirely sure how to code it so that on the main form there would be 2 in the wins box and 1 in the loss box.

So what i'm trying to do in vba is go through the subform and count wins and losses basically and put these results onto the main form.

Any help you can give me would be appreciated.

[puppydogbuddy]

I'm working on Access 2000 at the moment and i've got a small problem in regards to coding a calculation involving a form and a subform.

The form i have is called Player Overall Stats and the problem area is with two text boxes i have created to display win and loss values. The problem arises where i'm trying to find a way to take the value of a result field, which is either win or lose. I'm hoping to convert this into a number, so for example, in the subform there are 2 wins and 1 loss, i'm not entirely sure how to code it so that on the main form there would be 2 in the wins box and 1 in the loss box.

So what i'm trying to do in vba is go through the subform and count wins and losses basically and put these results onto the main form.

Any help you can give me would be appreciated.

The following assumes that the detail control on your subform is called [Outcome] , the subform footers are totalWins and totalLosses, and the textboxes on the main form are txtWins and txtLosses

1.To total wins and losses, place two textboxes in the subform footer section:
[totalWins] = Sum(IIf ([Outcome] = “Win”, 1, 0)
[totalLosses] = Sum(IIf ([Outcome] = “Loss”, 1, 0)

2.Have each of your textboxes on the main form get the totals from the subform like this: txtWins.Value = Me!YourSubformContainer.Form![totalWins]
txtLosses.Value = Me!YourSubformContainer.Form![totalLosses]

3.This code goes in the change event for win and loss controls on the subform.
Me.Parent![txtWins].Recalc
Me.Parent![txtLosses].Recalc

[jhawkins]

3.This code goes in the change event for win and loss controls on the subform.
Me.Parent![txtWins].Recalc
Me.Parent![txtLosses].Recalc

You mean the event where the wins and losses are calculated?

[jhawkins]

You mean the event where the wins and losses are calculated?

Okay, i've put that coding on the on change event for results, so should the result be changed this code should run.

At the moment i've got

In the Wins text box on the main form,

Wins.Value = Me!Ball Total Subform For Stats.Form![totalWins]
 

And currently in the Total Wins text box i have

[TotalWins]=Sum(If([Result]=[“Win”],1,0))
 

However i get a runtime error 438 saying the object doesnt support this property or method when i try and edit the result.

[theaybaras]

Hi Jhawkins

If you don't specifically need VBA code you could use something similar to a db I have made before. I'm not using a vba code, just an expression as my control source.

I have eliminated fields that do not apply to your problem. And have changed my form name to match yours. If you are wanting vba specifically, please feel free to ignore this completely! :)

theAybaras

Tables
----------

<b>tblname = tblMain</b>
<i>Field Name; Data Type; Description
mainkey; autonumber; PK
playname; text; (name of player)</i>
 
<b>tblname = tblSub</b>
<i>Field Name; Data Type; Description
subkey; autonumber; PK
sub_mainkey; numeric; FK
winloss; text; (looks up a defined list of "Win", "Loss", "Draw")</i>

Forms
----------
Player Overall Stats
Includes [playname] from tblMain and three unbound fields labeled "Wins" "Losses" and "Draws" as well
as a subform datasheet where you can enter in as many wins losses or draws as you want.

frmSub
Includes [winloss] from tblSub which is a combobox that allows you to select "Win", "Loss" or "Draw".

on Player Overall Stats, my unbound "Wins" control source is:
<i>

=DCount( "[winloss]", "tblSub", "[winloss] = 'Win' And [sub_mainkey] =  [mainkey]" ),

</i>

on Player Overall Stats, my unbound "Losses" control source is:
<i>

=Dcount( "[winloss]", "tblSub", "[winloss] = 'Loss' And [sub_mainkey] = [mainkey]" ),

</i>

on Player Overall Stats, my unbound "Draw" control source is:
<i>

=Dcount( "[winloss]", "tblSub", "[winloss] = 'Draw' And [sub_mainkey] = [mainkey]" ),

</i>

Results.
tblMain.mainkey; tblMain.playname values:
1; Alan Fontaine,
2; Michael Villipando,
3; Jony Shavier

tblSub.subkey; tblSub.sub_mainkey; tblSub.winloss values
1; 1; Loss
2; 1; Loss
3; 1; Win
4; 1; Win
5; 2; Win
6; 2; Draw
7; 2; Loss
8; 2; Win
9; 3; Win
10; 3; Win
11; 3; Win
12; 3; Loss

On my form when I cycle through my records I see the appropriate data in the sub form, and in Player Overall Stats:
Record 1
Alan Fontaine
Subform
Wins; 2
Losses; 2
Draws; 0

Record 2
Michael Villipando
Wins; 2
Losses; 1
Draws; 1

Record 3
Jony Shavier
Wins; 3
Losses; 1
Draws; 0

[puppydogbuddy]

Okay, i've put that coding on the on change event for results, so should the result be changed this code should run. That is correct.

At the moment i've got in the Wins text box on the main form,
Wins.Value = Me!Ball Total Subform For Stats.Form![totalWins]
Change to this (put brackets around names that have spaces within)
= Me![Ball Total Subform For Stats].Form![totalWins]

And currently in the Total Wins text box i have
[TotalWins]=Sum(If([Result]=[“Win”],1,0))
Change to this (If to IIf and take out everything to the left of the equal sign):
=Sum(IIf([Result]=[“Win”],1,0))

[jhawkins]

Okay, i've put that coding on the on change event for results, so should the result be changed this code should run. That is correct.

At the moment i've got in the Wins text box on the main form,
Wins.Value = Me!Ball Total Subform For Stats.Form![totalWins]
Change to this (put brackets around names that have spaces within)
= Me![Ball Total Subform For Stats].Form![totalWins]

And currently in the Total Wins text box i have
[TotalWins]=Sum(If([Result]=[“Win”],1,0))
Change to this (If to IIf and take out everything to the left of the equal sign):
=Sum(IIf([Result]=[“Win”],1,0))

Okay i've put that code in, and the code below is set to be applied on a result change.

Private Sub Result_Change()
Me.Parent![Wins].Recalc
End Sub
 

However it brings up the same error as before. It also puts #name? into the wins box and #error into other boxes on that form that do calculations.

[puppydogbuddy]

Okay i've put that code in, and the code below is set to be applied on a result change.

Private Sub Result_Change()
Me.Parent![Wins].Recalc
End Sub
 

However it brings up the same error as before. It also puts #name? into the wins box and #error into other boxes on that form that do calculations.

First, try changing>>> Me.Parent![Wins].Recalc to Parent![Wins].Recalc

If that does not help, then I need you to provide or confirm the following info before going further.

Name of textboxes on Main Form: [Wins] ; [Losses]
Name of Main Form:
Name of SubformControl on Main Form: [Ball Total Subform For Stats]
Name of Subform (the Source obj in the Subform container) :
Name of textbox control in the S/f Detail: [Result]
Name of textbox controls in the S/f footer:[TotalWins]: [TotalLosses]

[jhawkins]

First, try changing>>> Me.Parent![Wins].Recalc to Parent![Wins].Recalc

If that does not help, then I need you to provide or confirm the following info before going further.

Name of textboxes on Main Form: [Wins] ; [Losses]
Name of Main Form:
Name of SubformControl on Main Form: [Ball Total Subform For Stats]
Name of Subform (the Source obj in the Subform container) :
Name of textbox control in the S/f Detail: [Result]
Name of textbox controls in the S/f footer:[TotalWins]: [TotalLosses]

Okay that didnt help

The names of the two text boxes are like you say Wins and Losses
The name of the main form is Player Overall Stats
Not sure what you mean by the two next options.
If i'm reading it correctly [Ball Total Subform for Stats] is the name of the subform.
As for the name of the subform control, i'm not entirely sure what you mean.

The rest looks okay.

[puppydogbuddy]

Okay that didnt help

The names of the two text boxes are like you say Wins and Losses
The name of the main form is Player Overall Stats
Not sure what you mean by the two next options.
If i'm reading it correctly [Ball Total Subform for Stats] is the name of the subform.
As for the name of the subform control, i'm not entirely sure what you mean.
The rest looks okay.

I noticed I made one syntax error.
Change this: =Sum(IIf([Result]=[“Win”],1,0)) to =Sum(IIf([Result]=“Win”,1,0))

You forgot to tell me whether the change from Me.Parent to Parent above had any effect on the calulations. Also, can the [Result] ever be any thing other than "Win" or "Loss" .....i,e. can result ever be null?

The subform control is the container object on the main form that holds the form that we refer to as the subform. If you put the main form in design view, invoke the property sheet, and then click the along the border between the main Form and what you think of as the subform, you will see the title on the property sheet change to subform/report....this is the subform container on the main form that holds the form that is referred to as the subform from a user perspective. On that container, the first 2 properties you will see are the name (which refers to the name of the SubformControl) and the source object (which refers to the name of the form within the subform container. Both may have the same name or they may have different names, but I need to know which is which.