Unique calculation for a field in a table

[PcEatsAnApple]

After searching a long time.... I found no answer to my question. So here it is.

I have a table with:
[InvoicesID](Autonumber);[ContactID](Number);[Date](date);[InvoicesNumber](Number);[InvoicesCal](Calculated)


At the moment I have a calculation:[ContactID]+100&"-"&Year([Date])&"-"&[InvoicesNumber]=[InvoicesCal]
[ContactID] is also my customer number. [Date] the year. [InvoicesNumber] is a manual input.

[InvoicesCall has to be a unique calculation.
As an example:106-2013-001 This has to be a unique calculation.
Another example:108-2013-001
Another example:106-2013-002

My problem is the manual input of the field [InvoicesNumber]. Chance of human error with results in a not unique calculation.
I need [InvoicesNumber] to be like an autonumber but individual for every [ContactID].

What is the best approach here?

[zmbd]

Normally, calculated fields are not stored within the table except for historical/auditing reasons which an Invoice number may very well be such a need; however, at first glance it appears that the entire field value could be built in a query calculated field depending on how the value for [InvoicesNumber] is determined.


Therefore, we need just a little more information:

1) What version of Access are you using?
2) What are you currently using to create the invoice, is this all manual or some other method?
3) How is the value of [InvoicesNumber] determined?

[ADezii]

Just subscribing for now, will Post a possible solution later.

[ADezii]

I am literally running out the door, but the following Function will return the next Sequential Invoice Number based on the Contact ID passed to it. If the ID does not exist it will create it in the Format of XXX-YYYY-001. It is based on several assumptions - any questions feel free to ask.

Public Function fGenerateInvoiceNum(intContactID As Integer) As String
Dim strSQL As String
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
 
strSQL = "SELECT * FROM Table1 WHERE Val(Left$([InvoicesNumber],3)) = " & intContactID
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
 
With rst
  If .BOF And .EOF Then     'No Records for the ContactID (intContactID), create New
    fGenerateInvoiceNum = CStr(intContactID) & "-" & CStr(Year(Date)) & "-001"
  Else
    'Record(s) with Contact ID exist, move to the Last
    'Record then Increment
    .MoveLast
    fGenerateInvoiceNum = Left$(![InvoicesNumber], 9) & Format$(Val(Right$(![InvoicesNumber], 3)) + 1, "000")
  End If
End With
 
rst.Close
Set rst = Nothing
End Function