Problem with piece of code (updating field)

[ombadboy]

Ok.. so ive writted this code to put a value in a text box in a form, but it only seems to work only if i use the On Click declaration, and it updates ALL fields with the same value.

Dim amount
Dim difference
difference = Date - [txtDateLoaned]

If difference > 2 Then
amount = difference * 2
[PenApplied] = Format(amount, "£##,##0.00")
Else
[PenApplied] = "Not applicable"
End If

I've attached an image because my english isnt very good.

 

[MStef]

It is normal in continuous form for unbound fields.
I can suggest put this field in form footer (subform),
in that case it'll be valid for current record.
Make your subform continuous , not datasheet.

 

[ombadboy]

It is normal in continuous form for unbound fields.
I can suggest put this field in form footer (subform),
in that case it'll be valid for current record.
Make your subform continuous , not datasheet.

is there a way it can be displayed in the same way as a datasheet tho?

because now its like showing 1 record, then scroll down see the 2nd, etc. But i would like to have the same view as with the datasheet

 

[ombadboy]

bump?

need help plz

 

[WayneRyan]

OM,

If you base your form on a query (instead of the table), you can
just add a new column. Indented for readability:

PenApplied = IIf("DateDiff("d", Date(), [DateLoaned]) > 2, 
                 Format(DateDiff("d", Date(), [DateLoaned]) * 2, "£##,##0.00"),
                 "Not applicable")

Then just add the new field to your form.

Wayne

 

[ombadboy]

OM,

If you base your form on a query (instead of the table), you can
just add a new column. Indented for readability:

PenApplied = IIf("DateDiff("d", Date(), [DateLoaned]) > 2, 
                 Format(DateDiff("d", Date(), [DateLoaned]) * 2, "£##,##0.00"),
                 "Not applicable")

Then just add the new field to your form.

Wayne

So if i understood well, i just use that piece of coding in the query field

I tried using that (after loosing the " before the datediff because it seems it was causing a problem) i get an #error

 

[MStef]

Hello boy!

Look at "DemoUpdatingFieldA2000.mdb" (attachment).
Look at Table1, Query1, Form1, Form2.
Form1 is a Wayne's method (a good one).
Form2 is my method.
I think it is what you need.

 

[ombadboy]

Look at "DemoUpdatingFieldA2000.mdb" (attachment).
Look at Table1, Query1, Form1, Form2.
Form1 is a Wayne's method (a good one).
Form2 is my method.
I think it is what you need.

Had a look at it, and implemented it into the program, but now am facing another problem. Since i have the date field using an input mask, the "diff" field now appears like: [thevalue]__/__/__ (am not sure if its the input mask causing the problem though)

edit: ok, seems to eb working now, just removed the input mask

thnx alot.. i think its working now