I have came up with an idea to help me in my database. So, I have a form for follow up my patients, that have a lot of fields, and I have no problems with them, except for one field that I named it VisitCounter which basically will add +1 to the previous value, i.e with every visit the patient made , it will count +1 to the previous visit number.
and I created a backstage VBA for it like this:
Private Sub Form_AfterUpdate()
Dim count As Integer
count = VisitCounter.Value
Me.VisitCounter.Value = count + 1
End Sub
But I ran into two problems
First: with updating any record in the form , the visitcounter field will update accordingly, which access will consider it as another update and the counter keeps counting and I find my self stuck in an infinite loop 😁.
second: to solve the previous problem, i had to jump to the next patient record by the navigation buttons, but i find that the counter value continued from the previous record 😠.
So i think because of my deficient VBA experience, i made this silly mistake, but i have no idea how to solve it...
EDITED QUISTION PART:
I think I'll add some details, to make it clear.
The follow Up form connected to the follow up table which set with a primary key patientID.
So, whenever the patient visited the clinic, I don't want to create a new record for that, instead I just updated his old record , by editing the other fields, and it's work fine for me. that's means there is no duplicate records for a single patient. I think by this way I'll have no redundant records for the same patient.
in summary, I need my follow up form when I open it, to tell me what is the visit number through visitcounter field. but it's have the two issues as I mention it before.
If you are not saving history of visits and just updating patient record with current visit info, saving a visit number not calculated from visit records carries risk of the number getting corrupted. There is no data to substantiate the saved count. However, if you really do prefer this approach, updating the count field can be done simply with:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.VisitCounter = Nz(Me.VisitCounter, 0) + 1
End Sub
If you visit the record again to do a minor edit and this was not due to an actual visit, the count will still increment and therefore the visit count will not be valid. Perhaps this visit number increment should be tied to update of another field such as VisitDate. Only cause increment if visit date is modified. Use AfterUpdate event of VisitDate textbox.
With Me
If Nz(.VisitDate.OldValue,0) <> Nz(.VisitDate,0) Then .VisitCounter = Nz(.VisitCounter, 0) + 1
End With
If you decide to switch to schema saving visit records, options for the visit counter are:
don't save this value to table, use RunningSum property of textbox on report to dynamically generate this sequence number in conjunction with report Sorting & Grouping features - no VBA needed
lookup last generated sequence number for that patient and increment by 1, using BeforeUpdate event - this works fine as long as visit records are not deleted
If IsNull(Me.VisitCounter) Then Me.VisitCounter = Nz(DMax("VisitCounter", "Visits", "PatientID=" & Me.PatientID), 0) + 1