G
Guest
This is essentially a repost of an earlier question I had. The person who
helped was great and definately got me headed in the right direction, but in
re-reading my post, I realized I left out some important information that
probably affected the outcome of the code I was provided, so I'm hoping a
repost might help. I have a table called TblHV that contains Home Visiting
records by family number. I need to have them numbered such that when
family #1, who currently has 13 visits, has another visit entered, it is
automatically numbered as #14. Family # 7, who has 10 visits, would have
their next record automatically numbered as #11, and so on. The records are
entered on a subform called SbfHV. This subform is invisible until the user
hits the Home Visit button on the parent form, which then makes all other
forms invisible except this subform. The code I was provided is:
Dim strWhere As String
Dim lngHVNumber As Long
If Not IsNull(Me.[FamilyID]) Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.[HVNumber] = Nz(DMax("HVNumber", "TblHV", srtWhere), 0) + 1
End If
In most instances, this works great. The problem is that if a family has
never had a home visit, their first record starts out at 10, instead of 1.
The code seems to work perfectly for families that had already had a home
visit before this was implemented (I manually entered the HV numbers for them
up to that point) so I'm not sure what the problem is. Also, on a much more
minor note, I had originally set the subform up to automatically go to a new
record, but now it goes to the last record and the user must click a command
button to add a new record. That's not too big of a deal, but I would like
it to still go to a new record if possible. I've tried moving the code that
I had in place for that action around to different events (OnLoad, OnOpen,
etc.) but no luck so far! Any help is appreciated!
Tara
helped was great and definately got me headed in the right direction, but in
re-reading my post, I realized I left out some important information that
probably affected the outcome of the code I was provided, so I'm hoping a
repost might help. I have a table called TblHV that contains Home Visiting
records by family number. I need to have them numbered such that when
family #1, who currently has 13 visits, has another visit entered, it is
automatically numbered as #14. Family # 7, who has 10 visits, would have
their next record automatically numbered as #11, and so on. The records are
entered on a subform called SbfHV. This subform is invisible until the user
hits the Home Visit button on the parent form, which then makes all other
forms invisible except this subform. The code I was provided is:
Dim strWhere As String
Dim lngHVNumber As Long
If Not IsNull(Me.[FamilyID]) Then
strWhere = "[FamilyID] = " & Me.[FamilyID]
Me.[HVNumber] = Nz(DMax("HVNumber", "TblHV", srtWhere), 0) + 1
End If
In most instances, this works great. The problem is that if a family has
never had a home visit, their first record starts out at 10, instead of 1.
The code seems to work perfectly for families that had already had a home
visit before this was implemented (I manually entered the HV numbers for them
up to that point) so I'm not sure what the problem is. Also, on a much more
minor note, I had originally set the subform up to automatically go to a new
record, but now it goes to the last record and the user must click a command
button to add a new record. That's not too big of a deal, but I would like
it to still go to a new record if possible. I've tried moving the code that
I had in place for that action around to different events (OnLoad, OnOpen,
etc.) but no luck so far! Any help is appreciated!
Tara