Open Form and Increment table value

[acarter1]

I have a database to record annual leave of all the doctors in the hospital i work in, when a doctor works a bank holiday they get an extra days worth of annual leave, at the beginning of the annual leave year we allocate each person a certain amount of days leave which is kept in the staff table next to the persons details etc, this is called "leavealloc" so on my leave totals form i want a button to open a form to enter the date of the bank holiday worked and i also want the id of the person "assignmentno" to go into a field on that form and when save is clicked i want the value of "leavealloc" to increase by 1 and the date of the bank holiday worked and the "assignmentno" to save in the bank holiday table.

I have tried to use an update query to change the persons value of "leavealloc" but i get a data type conversion error, so i changed the parameters under the query option in queries to suit the fields being used but then i am asked to input the assignment no again.

Please Help!!

 

[DCrake]

Your type conversion error indicates you arre trying to update number with a string or vice versa. Make sure the values are the same type.

Are you doing x = x + 1 or x = y?

David

 

[acarter1]

x = x + 1
i am using a query to find the assignmentno currently being used by the open form which works fine but when it tries to update i get that error, i am doing daysalloc = daysalloc + 1.

 

[namliam]

Very simular problem

And you too my friend, IMHO offcourse, have a design issue... You are storing a total field instead of recording the individual records... if you fix the design, this will be easy !

Oh And welcome to AWF

 

[acarter1]

so could i make a table called say tblAnnualleave and in this table store "assignmentno" and "daysalloc" or would this create the same problem?

 

[acarter1]

you see the problem is each doctor could have 32 or 27 days depending on how long they have been with us....

 

[namliam]

The problem is they have a different number each year
Adding new, taking a few/a lot, carry over to next year... etc...

Look at the other thread I linked, and see if you can make some sence out of that...
The idea is isntead of doing " 2 + 27 = 29 " to get the total allowed you have
+2
+27
Then use a query to make the total, in the first sample you lose the detail as 29 over-writes the 2 already there, while with 2 and 27 as detail records you can always view the historic changes...