T
Tara
I've posted this question before and want to thank those that have provided
me with help so far. The issue I'm having is this: I have a table called
"TblHV" in which I have home visiting records for multiple families. What I
need to be able to do is automatically number records by family number. For
example, Family #1 has 5 visits. I have a text box on a form called SbfHV
that I need to update to reflect 6 visits when a worker adds a new visit.
I've been provided the following code to add into the BeforeUpdate event of
the form but it doesn't seem to do what I need.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim lngVisitNum As Long
If Not IsNull(Me.[FamilyID]) Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.HVNumber = Nz(DMax("HVNumber", "TblHV", strWhere), 0) + 1
Else
Me.FamilyID = 1
End If
End Sub
This code should be exactly what I need but for some reason, it isn't
working! Using this code, if a family has not yet had their 1st visit, the
text box starts off at 10 instead of 1. If I make the default value in the
table '1', (in order to overcome the 'default' 10) then every record comes up
numbered as '1', even if it's the 15th record for that family! What am I
doing wrong??? This doesn't seem like it should be this complicated!
me with help so far. The issue I'm having is this: I have a table called
"TblHV" in which I have home visiting records for multiple families. What I
need to be able to do is automatically number records by family number. For
example, Family #1 has 5 visits. I have a text box on a form called SbfHV
that I need to update to reflect 6 visits when a worker adds a new visit.
I've been provided the following code to add into the BeforeUpdate event of
the form but it doesn't seem to do what I need.
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim lngVisitNum As Long
If Not IsNull(Me.[FamilyID]) Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.HVNumber = Nz(DMax("HVNumber", "TblHV", strWhere), 0) + 1
Else
Me.FamilyID = 1
End If
End Sub
This code should be exactly what I need but for some reason, it isn't
working! Using this code, if a family has not yet had their 1st visit, the
text box starts off at 10 instead of 1. If I make the default value in the
table '1', (in order to overcome the 'default' 10) then every record comes up
numbered as '1', even if it's the 15th record for that family! What am I
doing wrong??? This doesn't seem like it should be this complicated!