Find duplicate value total and add 1

[sdchris]

I have a year field that automatically enters the current year when creating a new record. From there, I want my form to check the project number field to see if it is null, and if it is, count the duplicate amount of years and then add 1.

For example, I create a new record. The code checks and sees that there are ten 2010 entries in the year field. It then adds 1 to the 10 and displays it in the project field.

I have a query that checks for duplicate entries in the year field. Is there a way to call that field in vba and add 1?

I'm stuck at this right now:

If IsNull(ProjectNumber) Then 'checks if field is null

ProjectNumber = QryYear 'want it to count dups then add 1

Else
'do nothing
End If

End Sub

 

[Galaxiom]

It can be done in a query but it needs more code to do it.
It is easier to use a domain function which does it all in one line.

Me.ProjectNumber = DCount("*","[tablename]","[yearfield]=" & Me.yearcontrol ) + 1

 

[sdchris]

I just used this code in the On Current event:

Private Sub Form_Current()

If IsNull(ProjectNumber) Then

ProjectNumber = DCount("ProjectYear", "ProjectInfo", "Year(Now())") + 1

End If

End Sub

Do you think this is acceptable?

 

[Galaxiom]

Good idea. You can put the DCount in the Default property of the ProjectNumber control where it won't even need an event. It will just appear automatically in each new record.

However the syntax is not quite right

= DCount("*", "ProjectInfo", "ProjectYear =" & Year(Date()) ) + 1

Leave the field argument as "*" otherwise the function will test the designated field in every record for Nulls (which it doesn't count). This adds significantly to the time taken and you know there are no Nulls anyway.

I used Date() instead of Now() because it returns just the Date rather than Date and Time saving a few nanoseconds and RAM bytes.

 

[Galaxiom]

One weakness with using a DCount like this is with multiple users. Until the record is saved the next user creating a new record will get the same default value. Consequently you should immediately save the new record before continuing to edit it.

In large systems with many users the next value should be stored in a table and accessed via a recordset which is locked until the value has been incremented to prevent duplicates.