[B]Button counter...[/B]

[aronw]

Hi, I'm relatively new to Access.

I have a form with customer details applenty. I want to be able to have a button on this form which, when clicked increments itself by 1. This represents the number of phone calls a particular record has recieved. (The value held by this counter then needs to be displayed in a report.) How do I create this?

Another question. I need to sum a few fields together in this same form. e.g
Total = [score1]+[score2]+[score3]

My question is, is this the correct syntax and where do I put it?

TIA

Aron

 

[Howlsta]

bang it in the 'control source' property of the text box - correct syntax is:

=sum([score1]+[score2]+[score3])

Rich

oh and for the counter something like this will do:


Static counter

Me.Textboxnamehere = Me.Textboxnamehere + 1
counter = Me.Textboxnamehere

End Sub

as you said you might use a button, put the code in the click event of the button and change Me.Textboxnamehere to Me.cmdbuttonnamehere.caption

HTH

 

[aronw]

Thanks Howlsta,

It now calculates the fields, however when I scroll through the records the total field stays the same. I have a command button on the form which updates the data, still nothing changes.

This total field needs to be record specific and write its calc. to a table / query column

The Total field is blank by default, as are the score fields. Everynow and again the fields will fill with 0. This is fine but does not do it regularly.

Any help is very appreciated

Aron

 

[Howlsta]

are you viewing the records as continuous forms? Is the total box in the form footer? If so putting this line in the form's current event may help:

me.requery

you would then see the total for the current record

you could alternatively add the line in the click event of the command button.

 

[aronw]

The Total field is in the detail section. This calculation has to be record specific. When viewing a record in the form and entering scores the result will get applied to the total column for every record in the table

(I just discovered how to update thread)

thanks once again for your help

Aron

 

[Howlsta]

In that case it may be best to base your form on a query (if you are not already doing so). Add all the fields that you need to the query and then add a another field in the field box add:

Total(or some other meaningful name): ([score1] + [score2] + [score3])

make your query a totals query by clicking the sum button on the toolbar and in the total box select sum.

have a look at the query results and it should be the sum of the 3 for each record.

HTH

Rich

 

[aronw]

My query has fields; score1, etc. and myTotal.

If I create a totals query I can sum a column (correct me if i'm wrong). How do I sum a row?

The other route which was to create a standard query with same fields and "bang in expression" into control source worked. But but basically adds up all of the records.

Changing to single form instead of datasheet has no impact.

TIA

Aron

 

[Howlsta]

Add this field to your query:

Total: ([score1] + [score2] + [score3]) put sum in the total box in the query builder for this field. Look at the screenshot i've added if unsure.

in the datasheet view your query should look something like this:

ID_ score1_ score2_ score3_ Total
1____5_____5______4______14
2____3_____3______7______13
3____1_____2______6_______9

base your form on this query include scores1-3, total and any other fields that you want.

hope this explains it better.

Rich

 

[aronw]

Counter not functioning as planned

I now have a counter which is incremented by a click of a commmand button. However the text box and the field it refers to is blank ATM.
The code I used was:

Private Sub cmdCallCounter_Click()

Static counter

'Me.txtCallCounter = 0
Me.txtCallCounter = Me.txtCallCounter + 1
counter = Me.txtCallCounter

End Sub

Due to the fact the text box is blank to start with the counter will not work unless the value is manually typed in (1), then the counter counts. If I set the box to default at 0 or 1 then the counter does not function. I need to be able to lock the text box but can't with the situation as it stands.

Is there a way of making this work?

TIA

Aron

 

[ColinEssex]

If(IsNull(Me.txtCallCounter) Then

Me.txtCallCounter = 0

Else

Me.txtCallCounter = Me.txtCallCounter

End If

Make sure the textBox is formatted to number not text


Col

 

[aronw]

Thanks ColinEssex,

For anyone viewing post above, it works! But needs another closing bracket at the end of first line.

Thanks again everyone,



Aron

 

[ColinEssex]

Oops sorry! Forgot that one

Col