Numbering records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that contains home visiting records by family. I need to find
a way to number these records to indicate how many home visits each family
has had. For example, Family #1 has had 3 home visits. When the workers
adds the next home visit, I need it to be home visit number 4. Family #5 has
17 home visits. When the workers adds the next home visit, I need it to be
numbered #18, and so on. I'm sure there's a simple way to do this, but I
can't seem to figure it out. Thanks in advance for any help!
 
Use DMax() in the BeforeUpdate event of the form to assign the next number
to the field.

This kind of thing:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim lngVisitNum as Long

If Not IsNull(Me.[Family#] Then
strWhere = "[Family#] = " & Me.[Family#]
Me.[VisitNum] = Nz(DMax("VisitNum", "Table1", strWhere),0) + 1
End If
End Sub

Note that if Family# is a Text type field, you need extra quotes:
strWhere = "[Family#] = """ & Me.[Family#] & """"
 
Thanks for the help Allen. I think this is the right direction. The HV
number now updates like I had hoped. However I now have 2 other issues.

1. The form was set to open to a new record using code in the OnLoad event.
That no longer works. I've tried moving the code around, but no luck. It
now opens to the last record and the user must manually move to a new record.

2. If a family has never had a Home visit, the number will not
automatically go to 1, it stays at zero.

Thanks for everything and I hope you can help me solve these two issues!

Allen Browne said:
Use DMax() in the BeforeUpdate event of the form to assign the next number
to the field.

This kind of thing:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim lngVisitNum as Long

If Not IsNull(Me.[Family#] Then
strWhere = "[Family#] = " & Me.[Family#]
Me.[VisitNum] = Nz(DMax("VisitNum", "Table1", strWhere),0) + 1
End If
End Sub

Note that if Family# is a Text type field, you need extra quotes:
strWhere = "[Family#] = """ & Me.[Family#] & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tara said:
I have a table that contains home visiting records by family. I need to
find
a way to number these records to indicate how many home visits each family
has had. For example, Family #1 has had 3 home visits. When the workers
adds the next home visit, I need it to be home visit number 4. Family #5
has
17 home visits. When the workers adds the next home visit, I need it to
be
numbered #18, and so on. I'm sure there's a simple way to do this, but I
can't seem to figure it out. Thanks in advance for any help!
Click to expand...
Click to expand...
 
If you wanted to open to the new record, you would use:
RunCommand acCmdRecordsGotoNew
in the Load event.

If you want to sometimes go to the new record, sometimes to the last,
sometimes to a particular record, and sometimes to the first, you will need
some more code. For example, test the FilterOn property of the form: it is
was opened filtered, then don't move record. Or perhaps just opening the
form in Add mode would be enough:
DoCmd.OpenForm "Form1",,,,acFormAdd

If the person has never had a visit, DMax() should return Null. Nz() should
then convert that to zero, and you are then adding 1. Not sure why you're
getting a zero for the first visit ever.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Tara said:
Thanks for the help Allen. I think this is the right direction. The HV
number now updates like I had hoped. However I now have 2 other issues.

1. The form was set to open to a new record using code in the OnLoad
event.
That no longer works. I've tried moving the code around, but no luck. It
now opens to the last record and the user must manually move to a new
record.

2. If a family has never had a Home visit, the number will not
automatically go to 1, it stays at zero.

Thanks for everything and I hope you can help me solve these two issues!

Allen Browne said:
Use DMax() in the BeforeUpdate event of the form to assign the next
number
to the field.

This kind of thing:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim lngVisitNum as Long

If Not IsNull(Me.[Family#] Then
strWhere = "[Family#] = " & Me.[Family#]
Me.[VisitNum] = Nz(DMax("VisitNum", "Table1", strWhere),0) + 1
End If
End Sub

Note that if Family# is a Text type field, you need extra quotes:
strWhere = "[Family#] = """ & Me.[Family#] & """"


Tara said:
I have a table that contains home visiting records by family. I need to
find
a way to number these records to indicate how many home visits each
family
has had. For example, Family #1 has had 3 home visits. When the
workers
adds the next home visit, I need it to be home visit number 4. Family
#5
has
17 home visits. When the workers adds the next home visit, I need it
to
be
numbered #18, and so on. I'm sure there's a simple way to do this, but
I
can't seem to figure it out. Thanks in advance for any help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top