Sequential number on subform

[jpkeller55]

Hi,

I have a form and subform that links two tables tblPatients and tblBleedDetails respectively. PK-FK is PatientID. There is a one to many relationship between the tblPatients and tblBleedDetails. I am logging patient bleed events for each patient sequentially so that the first event is 1, second event 2 and so on. How do I get the sequence number for each bleed event to start at 1 and increment by one each time a new event is entered on the subform?



For example PatientID 12345 may have records in the tblBleedDetails with a sequence 1,2,3 for his three events and PatientID 23456 may have three events also sequenced 1,2,3.

Thanks for any help or pointing me in the right direction.

[jpkeller55]

As I think about this some more, maybe I can use the DCOUNT function to see how many records exist for a particular PatientID, if any, and then add 1 to whatever value DCOUNT returns and use that as my sequence number. Does this make sense?

[RuralGuy]

Have you already reviewed this thread? https://www.accessforums.net/forms/n...orm-12001.html

[jpkeller55]

OK, I looked at the example from the thread you suggested and it indeed numbers rows accordingly on a form if you add a text box. I would like to be able to save this information in a table so that the sequence number becomes a record and not just a row number on a form. Does this make sense? So as I add a new bleed event on the subform, a new bleed event number is assigned and stored in the tblBleedDetails table with the sequence starting at 1 and incrementing by 1 for each individual PatientID.

[RuralGuy]

Maybe you will find this link helpful: http://www.baldyweb.com/CustomAutonumber.htm

[jpkeller55]

OK, I solved what I need to do using the following code in the subform before update event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim intHolder As Integer
 
    intHolder = DCount("BleedID", "qryBleedDetails", "PatientID =" & Me.PatientID)
    Me.PatientBleedSequence = intHolder + 1
End Sub

Thanks RG for taking a look...I appreciate your help.

[RuralGuy]

Excellent solution! Thanks for post back.

[jpkeller55]

Just as a follow-up, I moved this code to the Before Insert event of the form so that it would not update the sequence number if I modified any data on the form.

[RuralGuy]

Good point. You could also have simply checked to make sure it did not have one already in the BeforeUpdate event. Lots of ways to skin this cat here.