Form Help

[Rory898]

I am looking for a piece of code or macro of some kind to decrease the value in a field each time the form is opened. i.e:

The amount in stock is 400
each time the form opens i want it to go down by 20

therefore it will be 400 then 380 then 360 then 340 etc

Thanks in advance for your help

[RuralGuy]

Is the field in the RecordSource of the form you are opening?

[Rory898]

Sorry what do you mean record source?

It is linked to a stock table. Its not a calculated field or anything

[RuralGuy]

On the Data tab of the property sheet for the form is the RecordSource property. Does this point to the table you want to modify?

[Rory898]

yea it is pointing to the table

[RuralGuy]

You said each time the form opens. Is there only one record in the table?

[Rory898]

Theres 3 separate values in the table but i only want one of them to decrease by 20

[RuralGuy]

You can use the OnLoad event to change the field but it will be pointing to the first record in the table. Is there only one record in the table?

[Rory898]

There are 6 records in the table

Text Paintball gun Supplier Name
Number Guns In Stock
Text Overalls Supplier
Number Overalls In Stock
Text CO2 Gas supplier
Number Amount in Stock <-- this is the value that i want to change

[RuralGuy]

That appears to be a strangely designed table. Have you studied up on normalization yet? Are those 6 Rows in the Table or 6 Columns in one Row? Here's a starting link on normalization: http://support.microsoft.com/kb/283878

[Rory898]

yeaaa but the table isnt related to anything else. I just made it for this purpose of a form. I could remove the Text rows.

so it would just be :

Guns in Stock:
Overalls in Stock:
CO2 in stock:

Would this make it simpler?

[RuralGuy]

Rory,
The problem I'm having is you seem to have a table with one field (Column) that changes back and forth between a text field and a numerical field. You can not do this in Access unless the "Numerical" field is really a text field which is a waste of resources of Access. It sounds like you are running a PaintBall park.

[Rory898]

Yea it is a paintball system. Here is the table and form. I would be extremely grateful if you cud take a look at it.

[RuralGuy]

That explains a lot Rory, thanks. Why do you have the Text fields in your table? I doubt you will forget what the next field will contain and besides it has a name as well that tells you what it contains.

[Rory898]

yeaaaa it was pretty unneccesary. So do you think i should remove all the text fields? and then how would i get the co2 field to decrease by 20 when it is opened?