I have a textbox on a form, txt_total_quest, which is supposed to display the number of questionnaires entered thus far in the database. Therefore, the control source needs only to be equal to the highest generated by the primary key in the respective table i.e. MAX(quest_no). However, this value is only updated when the user enters the database initially. If the user enters 5 questionnaires after entering the DB, this value is not updated accordingly. I it need to update and display the correct value each time a new questionnaire is generated.
I thought tried entering the the following in the "click" event of the button that generates a new questionnaire.... but it generates a syntax error....
txt_total_quest.ControlSource = max(Survey_no)
I also tried the entry below, but that doesn't work either.
The first, if you are using the autonumber as a primary key to get an accurate count of the records in the table, you have an area of potential error. The autonumber field increments on every insert whether it failed or succeeded. This will give you a higher count than actual record count. You should count the records instead.
To do this use =DCount("[Fieldname]","tblTablename") in the control source property of that textbox. Then issue a me.recalc on the form's afterinsert or oncurrent event, or any save button.