ms access 2003 vba incrementing new record

[pademo57]

Hi There,
Yes, I am a newbie especially because I have been tasked with updating an old MS ACCESS 2003 database.

The school wants to add a record to a students file by clicking on a button which will add a new record but also increment the daily record number.

In other words, when adding a new record copy the students first and last name , class name but increment the number of days in the class. The issue is that I can get VBA to update the first and last name and class name but I get a 3164 error when I try to increment the num_of_days

The record looks like this:
txt_fname
txt_lname
txt_classname
num_of_days

my program says:
addnew
!txt_fname = me.txt_fname
!txt_lname = me.txt_lname
!txt_classname = me.txt_classname
!num_of_days = num_of_days + 1
update
However as soon as the program reaches the "!num_of_days ..." line it drops to the error code and displays error 3164.
What am I doing wrong?

 

[pademo57]

num_of_days is defined as a number and the initial value was someone who put in the number 1.
I hope that helps.

 

[gemma-the-husky]

requests like these are pretty typical of users who do not understand how to use a database properly

i struggle to see the possible benefit of an artificial construct, such as the number of days.

a daily register? just store the date.

 

[pademo57]

Yes, I have had "typical" answers before like the one above. No disrespect meant but I just need an answer to the question:
What is the syntax for writing in VBA to increment a number on to the next record? And secondly why am I getting a 3164 error?

To explain a little further, this is for a school for juvenile delinquency, also known as juvenile offending depending on what part of the country you are from, and can not afford to upgrade.

The "num_of_days" number is used to keep track of the person and how many days they spend in class or not.

I have spent years programming in "C" and was asked to help them out. I'm sure there is a very simple explanation for a simple guy like me but for the life of me I can't figure it out.

 

[gemma-the-husky]

OK - this type of generic sql statement

"update personrecord set daysfieldvalue = " & daysfieldvalue + 1 & " where personid = " & yourtarget

but in your case, with a recordset, it needs an extra ! (field reference)

!num_of_days = !num_of_days + 1
or more correctly
!num_of_days = nz(!num_of_days,0) + 1

as otherwise a brand new record will not initiallize to 1 correctly



the trouble with storing a calculated value is that it becomes very difficult to reset it, or establish the correct value should the recording system not work correctly.

it's far far better to store a table of the dates you need to manage, and count those dates each time you need this data.

 

[pademo57]

Thank you very much. That is exactly the help I needed. I tried to explain to them exactly what you are talking about with dates but they insisted this is the way they want to do things so they are the client ...

Thanks again.

 

[gemma-the-husky]

it's difficult. I think sometimes you have to do what's right, not what the client thinks. if they get the same answer, what's the difference

if all they have is a total, what happens if they decide later that they need an analysis of how the total is split over time?

funny think how clients want to design software - they wouldn't get a builder in, and then try and tell the builder the technical details of his job.