form setup

  • Thread starter Thread starter guidop12
  • Start date Start date
G

guidop12

My table is set up like this

ID(sequential field) Read_Date Black_Click Black_Total_Month
1 05/05/08 505957 505957
2 06/02/08 762429 256472 (this number is
#2 black click - #1 black click , which was originally an Excel formula)

Question 1 Do I need some other field to do the math?

I also have a form

Read date (user will input)
Black Click (user will input)
Black Total (formula: #2 Black Click - #1 Black Click)

I want the Black total field to populate when the field in clicked

Is this possible??
 
My table is set up like this

ID(sequential field) Read_Date Black_Click Black_Total_Month
1 05/05/08 505957 505957
2 06/02/08 762429 256472 (this number is
#2 black click - #1 black click , which was originally an Excel formula)

Question 1 Do I need some other field to do the math?

I also have a form

Read date (user will input)
Black Click (user will input)
Black Total (formula: #2 Black Click - #1 Black Click)

I want the Black total field to populate when the field in clicked

Is this possible??
Click to expand...

It's possible... but it's also A Very Bad Idea.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.
 
Thank you, but I guess my question is how do I setup the form to do the
math(#2 black_click - #1 black_click) and then have it automatically put in
the Black Total
 
Thank you, but I guess my question is how do I setup the form to do the
math(#2 black_click - #1 black_click) and then have it automatically put in
the Black Total
Click to expand...

<shrug>

Ok. So you're saying that the integrity and accuracy of your data is
unimportant and that you are happy to store incorrect values. Not the way I'd
do business, but...

Use the AfterUpdate event of the black_click control to "push" the calculated
result into Black Total:

Private Sub Black_click_AfterUpdate()
If IsNull(Me!Read_Date) Then
' can't find previous date if you don't know this date!
MsgBox "Please fill in Read_Date"
Else
Me!Black_Total = Me!Black_Click - NZ(DLookUp("[Black_Click]", _
"[your-table-name-here]", _
"[Read_Date] = #" & DMax("[Read_Date]", "[your-table-name-here]") & "#")
End If
End Sub
 
this worked perfectly, thank you very much.

John W. Vinson said:
Thank you, but I guess my question is how do I setup the form to do the
math(#2 black_click - #1 black_click) and then have it automatically put in
the Black Total
Click to expand...

<shrug>

Ok. So you're saying that the integrity and accuracy of your data is
unimportant and that you are happy to store incorrect values. Not the way I'd
do business, but...

Use the AfterUpdate event of the black_click control to "push" the calculated
result into Black Total:

Private Sub Black_click_AfterUpdate()
If IsNull(Me!Read_Date) Then
' can't find previous date if you don't know this date!
MsgBox "Please fill in Read_Date"
Else
Me!Black_Total = Me!Black_Click - NZ(DLookUp("[Black_Click]", _
"[your-table-name-here]", _
"[Read_Date] = #" & DMax("[Read_Date]", "[your-table-name-here]") & "#")
End If
End Sub
Click to expand...
 
Back
Top