User Form not working as needed

[sinder81]

Here is my requirements:

1. User Form with auto-update field that sequentially puts the next number in after the last number.
2. Have the number begin at 1 again as the year changes

The following fields are identified in the Table

CC# (long integer)
CCYear (formatted yyyy)
DocumentNumber
DocumentTitle
ChangeLead

I entered the following code in the database itself

Public Function GetNextCC()
GetNextCC = Nz(DMax("CC#", "CCEntry", "CCYear=" & Year(Date)), 0) + 1
End Function

I entered the following code in the form properties
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me![CC#]) Then
Me![CC#] = GetNextCC()
End If
End Sub

This isn't working. I open the form in add mode and the field is empty. When I enter the first field Document Number the field stays blank. When I try to save the record (field is still empty), it give me an error that the form has an error. I've only changed the form by adding the code above. When the form was autonumber it worked fine; however, i had to create another database for the next year.

Can someone please tell me what I'm doing wrong.

Thanks in advance.

 

[DoubleD]

What error are you getting when you try to save the record?

Your text box for CC# should be called txtCCNum.
The Control Source should be CC#.
Then your code should read:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.CC#) Then
Me.CC# = GetNextCC()
End If
End Sub

Is CCYear a DateField formatted as YYYY? If so, your DMax needs to changes as follows:

GetNextCC = Nz(DMax("CC#", "CCEntry", "Year(CCYear)=" & Year(Date)), 0) + 1


I am what I am based on the decisions I have made.

DoubleD