RecordCount Doesn't Work

[GMSRE]

Hello,
Access 2007
In my input table I have a field called RecordIDNo. I have another field called communityID. I am trying to generate a number in the RecordIDNo field in relation to the communityID. For example there are communityID 0001 and communityID 0002 I want the database to keep track of how many records that are associated with each communityID number. In the RecordIDNo field I want the record number populated in that field when I am adding a record. Instead I get the number zero in the RecordIDNo field.

Any help would be greatly appreciated. Thank you.

This is the code I used.

Private Sub Form_BeforeInsert(Cancel As Integer)
'Generate the record id number for a the community

On Error Resume Next
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngID As Long

strSQL = "SELECT RecordIDNo FROM DataForm " & _
"WHERE CommunityID = '" & Forms!DataForm!Community & "'"
Set rstOriginal = db.OpenRecordset(strSQL, dbOpenSnapshot)

If (rs.RecordCount > 0) Then
rs.MoveLast
lngID = rs.RecordCount + 1
Else
lngID = 1
End If
rs.Close
Me.RecordIDNo = lngID

End Sub

Public Function NextID() As Long
On Error Resume Next
'Generate the record id number for a the community
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngID As Long

lngID = 1
strSQL = "SELECT RecordIDNo FROM DataForm WHERE CommunityID = '" & Forms!DataForm!Community & "'"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If (rs.RecordCount > 0) Then
rs.MoveLast
lngID = rs.RecordCount + 1
End If
rs.Close
NextID = lngID
End Function


Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error Resume Next
If (IsNull(Me.RecordIDNo)) Then
Me.RecordIDNo = NextID()
End If
End Sub

Private Sub Form_Load()
On Error Resume Next
Set db = CurrentDb
boCopy = False
End Sub

 

[pbaldy]

This jumps out:

Set rstOriginal = db.OpenRecordset(strSQL, dbOpenSnapshot)

If (rs.RecordCount > 0) Then

This type of test:

If (rs.RecordCount > 0) Then

I would replace with

If Not rs.EOF Then

Further, this is an instance where I'd probably skip the recordset and use

Nz(DMax("RecordIDNo", "DataForm", CommunityID = '" & Forms!DataForm!Community & "'"), 0) + 1

 

[GMSRE]

I pasted Nz(DMax("RecordIDNo", "DataForm", CommunityID = '" & Forms!DataForm!Community & "'"), 0) + 1 and it turned red.

Where would Nz(DMax("RecordIDNo", "DataForm", CommunityID = '" & Forms!DataForm!Community & "'"), 0) + 1 be used?

Thanks.

 

[pbaldy]

NextID = Nz(...)

or

Me.RecordIDNo = Nz(...)

 

[GMSRE]

Hi Paul,

The NZ(… code was very helpful to get the record id number in the RecordIDNo field to populate and increment. The remaining challenge is to get it to keep track of the number of records for each individual community ID. For example if I inputted 3 records associated with community ID 0002 and 6 records associated with community ID 0004 then the next record I input for community ID 0002 the RecordIDNo number should be 4 not 10. How can I get that to happen?

Thanks again for your help.

 

[pbaldy]

With the criteria on community, I thought it was doing that already. What's your exact code, and what's the data type of the community ID field?

 

[GMSRE]

Hi Paul,

The communityID field is in text format. The RecordIDNo field is in number format.
This is the code I am using. Thanks again for your help.

Private Sub Form_BeforeInsert(Cancel As Integer)
On Error Resume Next
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngID As Long

strSQL = "SELECT RecordIDNo FROM DataForm " & _
"WHERE CommunityID = '" & Forms!DataForm!Community & "'"

Me.RecordIDNo = Nz(DMax("RecordIDNo", "InputTable", CommunityID = "'" & Forms!DataForm!Community & "'"), 0) + 1

End Sub

Public Function NextID() As Long
On Error Resume Next
'Generate the next record id for a the community
Dim rs As DAO.Recordset
Dim strSQL As String
Dim lngID As Long

lngID = 1
strSQL = "SELECT RecordIDNo FROM DataForm WHERE CommunityID = '" & _
Forms!DataForm!Community & "'"
NextID = Nz(DMax("RecordIDNo", "InputTable", CommunityID = "'" & Forms!DataForm!Community & "'"), 0) + 1

End Function

 

[pbaldy]

Sorry, I had a copy/paste typo in there (quotes around the criteria). Try

Nz(DMax("RecordIDNo", "InputTable", "CommunityID = '" & Forms!DataForm!Community & "'"), 0) + 1

 

[GMSRE]

Hi Paul,

It works. This is just what I was looking for. Thanks so much.

GMSRE

 

[pbaldy]

No problem, sorry about the goof.