This field in Paul's example is just an Integer field (long if you want) in every record with the default value of Year(Date()). It is *not* a DateTime field.
Thanks for getting back to me. I have changed the DateField to a long integer and stored the default value as Year(Date()) which is now '2010' if I look at the datasheet view of the next record to be created.
I have placed the code in the On Click Event Procedure of my Add New Record Control as follows:
Private Sub Command44_Click()
On Error GoTo Err_Command44_Click
DoCmd.GoToRecord , , acNewRec
Me.Client__ = Nz(DMax(Me.Client__, Clients, "me.DateField =" & Year(Date)), 0) + 1
Exit_Command44_Click:
Exit Sub
Err_Command44_Click:
MsgBox Err.Description
Resume Exit_Command44_Click
End Sub
You will notice that the Year(date()) does not look right as Access will not let me add the extra )'s and returns an error "Invalid use of Null" when executed. Any thoughts?
Thanks,
Bill
You do *not* want the me. in that criteria argument!
You do ynderstand that the Me.Client__ control will end up with just a number in it, right? When you display it or search for it you will need to concatenate the two fields into the string you want.Code:Me.Client__ = Nz(DMax(Me.Client__, Clients, "me.DateField =" & Year(Date)), 0) + 1
[DateField] & "-" & Format(Client__, "0000") or whatever you want.
Thanks for your reply. I tried the code with the "me." removed from the DateField and it still comes up with an error "Invalid use of null". As well, I am still unable to add the extra ) required. My cope comes out looking like this:You do *not* want the me. in that criteria argument!
You do ynderstand that the Me.Client__ control will end up with just a number in it, right? When you display it or search for it you will need to concatenate the two fields into the string you want.Code:Me.Client__ = Nz(DMax(Me.Client__, Clients, "me.DateField =" & Year(Date)), 0) + 1
[DateField] & "-" & Format(Client__, "0000") or whatever you want.
Before leaving editing the code:
Me.Client__ = Nz(DMax(Me.Client__, Clients, "DateField =" & Year(Date())), 0) + 1
After leaving the line, Access removes the () after Date
Me.Client__ = Nz(DMax(Me.Client__, Clients, "DateField =" & Year(Date)), 0) + 1
This results in the "Invalid use of null" error.
???
Regards,
Bill
Any chance you have named some procedure DATE() in your ap somewhere? Do a find on "Date(' from any VBA module and select "Current Project".
It may be time to post your zipped up db so we can play with it.
Would something like this work in the On-Click event of the Add New Record button?
Private Sub Command44_Click()
On Error GoTo Err_Command44_Click
Dim ClienNum As Integer
Dim ClienYear As Integer
DoCmd.GoToRecord , acClients, acLast
ClienNum = Me.DateField
ClienYear = Left(Me.Client__, 4)
DoCmd.GoToRecord , , acNewRec
If ClienYear = Year(Date) Then
Me.Client__ = ClienYear & "-" & ClienNum + 1
Else
Me.DateField = 1
Me.Client__ = Year(Date) & "-" & Me.DateField
End If
Exit_Command44_Click:
Exit Sub
Err_Command44_Click:
MsgBox Err.Description
Resume Exit_Command44_Click
End Sub
Why not put something in the Current Event of the Form that checks for Me.NewRecord?
Pardon me for jumping in. A couple of thoughts on what appears to be your most recent effort:
Me.Client__ = Nz(DMax(Me.Client__, Clients, "DateField =" & Year(Date)), 0) + 1
Does this work?
Me.[Client__] = Nz(DMax("[Client__]", "Clients", "DateField =" & Year(Date)), 0) + 1
I'm not a fan of the trailing underscores; you may need to bracket that field name, but the quotes are necessary in the DMax, and as RG mentioned you don't want Me in the field argument. As RG also mentioned, a sample db would be a great help.
Thanks for your reply.Pardon me for jumping in. A couple of thoughts on what appears to be your most recent effort:
Me.Client__ = Nz(DMax(Me.Client__, Clients, "DateField =" & Year(Date)), 0) + 1
Does this work?
Me.[Client__] = Nz(DMax("[Client__]", "Clients", "DateField =" & Year(Date)), 0) + 1
I'm not a fan of the trailing underscores; you may need to bracket that field name, but the quotes are necessary in the DMax, and as RG mentioned you don't want Me in the field argument. As RG also mentioned, a sample db would be a great help.
I tried this line of code with the field name bracketed and it produced:
"1 - " in the [Client__] field.
Prior to going to the new record, Datefield value was 37 and the largest value in the [Client__] field with the current year was 2010-37.
Regards,
Bill
In using this code:
Me.[Client__] = Nz(DMax("[Client__]", "Clients", "DateField =" & Year(Date)), 0) + 1
I am confused as to what "DateField" actually is? Is this another field in the Current Table (Clients) that I have to create and store the value of the current year in?
Regards,
Bill
DateField should be the name of your field. I take it you can't post the db?
This Db is for a Not for Profit Palliative Care Group and the Clients records are confidential in nature. I cannot post the Db. I am a volunteer in the group with some limited Access background.
I have solved the problem, although using a different approach. The field that stores the data for Client Number is a text field and the data looks like this:
2010-36
2010-37
etc and increments by 1 as a new client is entered until the end of the year comes and we start over as:
2011-01
etc
The following is the code I used in the On-Click eventy of the Add New Record Control:
Private Sub Command44_Click()
On Error GoTo Err_Command44_Click
Dim ClienNum As Integer
Dim ClienYear As Integer
DoCmd.GoToRecord , , acLast
ClienNum = Me.DateField ‘Sets ClienNum = to value of Datefield in the last record
ClienYear = Left(Me.Client__, 4) ‘ Sets ClienYear – to first 4 characters in the Client__ Field
DoCmd.GoToRecord , , acNewRec
If ClienYear = Year(Date) Then ‘test for the year being the same as the last record
Me.DateField = (ClienNum + 1) ‘adds 1 to value of DateField
Me.Client__ = ClienYear & "-" & (ClienNum + 1)
Else ‘ year has changed on Jan 1st and a new series of numbers must begin
Me.DateField = 1
Me.Client__ = Year(Date) & "-" & Me.DateField
End If
Exit_Command44_Click:
Exit Sub
Err_Command44_Click:
MsgBox Err.Description
Resume Exit_Command44_Click
End Sub
Many thanks to all of you who have helped me with this,
Regards,
Bill