Auto fill field upon New Record selected

[RuralGuy]

So far, I have had no luck with Paul's solution and perhaps I am using it incorrectly. I was assuming that Datefield is a field of date/time type where the default value is Date().

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.

[NOTLguy]

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

[RuralGuy]

You do *not* want the me. in that criteria argument!

Me.Client__ = Nz(DMax(Me.Client__, Clients, "me.DateField =" & Year(Date)), 0) + 1

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.
[DateField] & "-" & Format(Client__, "0000") or whatever you want.

[NOTLguy]

You do *not* want the me. in that criteria argument!

Me.Client__ = Nz(DMax(Me.Client__, Clients, "me.DateField =" & Year(Date)), 0) + 1

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.
[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:

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

[RuralGuy]

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".

[RuralGuy]

It may be time to post your zipped up db so we can play with it.

[NOTLguy]

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

[RuralGuy]

Why not put something in the Current Event of the Form that checks for Me.NewRecord?

[pbaldy]

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.

[NOTLguy]

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.

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

[NOTLguy]

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

[pbaldy]

DateField should be the name of your field. I take it you can't post the db?

[NOTLguy]

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