Update total field from unbound text box?? (1 Viewer)

M

Mtc

New member

Local time

Today, 06:57

Joined

Jan 25, 2007

Messages

3

• Jan 25, 2007

• #1

Hi,

Could someone please help me with this problem.

At work we have a simple point scoring system. I have created a database that includes a total points field. I have then created a form that shows employees records, name employee ID total points etc.

I have added two unbound text boxes to the form, "add points" and "deduct points". I'm trying to get it so that if we put a value in one of the unbound text boxes on change it will update the total field by adding or deducting points from the total field in the database and then refresh the form showing the new total.

Does this have to be written in VB? I have no idea how to code this and can't seem to find help on it anywhere.

Thanks,

Matt

 

M

MStef

Registered User.

Local time

Today, 06:57

Joined

Oct 28, 2004

Messages

2,251

• Jan 25, 2007

• #2

Hello Matt!

Try this;
In Event "add points After_Update" put this code.
Me.[total points] = Me.[total points] + Me.[add points]
In Event "deduct points After_Update" put this code.
Me.[total points] = Me.[total points] - Me.[deduct points]

 

M

Mtc

New member

Local time

Today, 06:57

Joined

Jan 25, 2007

Messages

3

• Jan 25, 2007

• #3

Hi,

Thanks but that didn't work. Someone I work with put something very similar to the following code in last night.

Private Sub Addpts_Change()
Dim pts, total As Variant

total = Me.total_points
pts = Me.add_points

total = total + pts

Me.total_points.Value = total

Me.Refresh

End Sub

We nearly got it working but it was adding the previously entered amount to the total.

i.e. default value is 0 type 1 it would enter a 0 then type 2 it would add 1, then what ever you put in next it would add the 2. So there was a delay in adding the value to the total.

At the moment the code doesn't work at all.

 

R

rborob

Registered User.

Local time

Yesterday, 22:57

Joined

Jun 6, 2006

Messages

116

• Jan 25, 2007

• #4

use .Text instead of value

 

Mile-O

Back once again...

Local time

Today, 06:57

Joined

Dec 10, 2002

Messages

11,316

• Jan 25, 2007

• #5

You should not be storing such a value in your database. It goes against the rules of database normalisation. You should calculate the total as and when you need it either in a form, query, or report.

 

neileg

AWF VIP

Local time

Today, 06:57

Joined

Dec 4, 2002

Messages

5,975

• Jan 25, 2007

• #6

SJ McAbney said:

You should not be storing such a value in your database. It goes against the rules of database normalisation. You should calculate the total as and when you need it either in a form, query, or report.

Click to expand...

Quite agree. Store the adjustments up or down and total them. You also get an audit trail so you can see how the current total has built up.

 

M

Mtc

New member

Local time

Today, 06:57

Joined

Jan 25, 2007

Messages

3

• Jan 25, 2007

• #7

I have created an add & deduct point columns to the database. How do I stop it from overwriting the value that is already in there when I put a new value in on the form?

Thanks for your help

 

neileg

AWF VIP

Local time

Today, 06:57

Joined

Dec 4, 2002

Messages

5,975

• Jan 25, 2007

• #8

You need another table and each adjustment becomes another record in that table. This record is linked to the employee via the employee ID. You then sum the adjustments by employee to get the total.