Create my own AutoNumber

A bit of a messy answer, but you could:

Have your new record entered into an "input table" which holds the single record until it is "transferred" via VBA code. Before the record is transferred it would not require the unique field. Upon transferring the record, pick up the department code from an existing field, and the date from an existing field or using Date().

The incremental part of the number could come from yet another single record, single field table.

In your code for the transfer, you would:

1) append the record, building the unique key as described above.
2) clear the input table.
3) run sql to increment the value in the single record/single field table.

As I said, this is very crude, but it works and I have used this method.

There is also an article on this, wich describes the way Micrsoft recommend the why it should be. This works grat in a multiuser enviorment, and I use this method for all my applications.

http://support.microsoft.com/support/kb/articles/q140/9/08.asp

perove

perove, you are fond of MS' web site, eh? :o) That's the third comment I've seen like that in my digging! Good to see you back around here . . . it's been a while.

Yes, i've discover that my pedagoic skills are well...limtided (to say at least). So why try to complicate things... ( besides I've just got the whole MSKB on DVD)

My suggestion would be to do the following.

Before inserting the record.  Select Max primary key by using Max date.

Get this key and break that key apart using sub strings.  Get the 4 digit number add 1 then put the string together to make the new primary key to insert into the table.

This way no other table will be needed.

It will take a little trial and error at first to get it to work, but then should be fine.

Rog

One other solution is to make  the Department Code and date created one field and the Invoice number another.  Then make both the primary key to the table.  

Then you would only have to select the max Invoice number add one to that to get the next primary key to assign.

This way it would make it easy to search too.

Rog:

Hi there! I haven't seen you around here before, but I see from your profile that you've been on EE for three years. You may be unfamiliar with the Access forum, but we try to submit almost everything as a Comment rather than an Answer so questions aren't locked and out of the loop.

Your suggestion has merit, and StormMajik may even choose to use it, but the other comments also have merit. Since the various approaches are all fairly equal, would you consider changing your Answer to a Comment so StormMajik feels at liberty to try any of the approaches without the pressure of "rejecting" an answer?

brewdog

Not rejecting the answer per say Rog. Just like to leave the question open until I evaluate all the comments.

Storm ;-)

Your comment looks interesting brewdog, along my own (but more vague) line of attack.

Option 1 looks good - but can I index/search it given the full invoice number?

With your option 2, I attempted this first in my quick hack mode. What gave me a problem is when using =Date() as the default value and trying to concatenates the results with the other fields I got an error. Type the date instead and all seemed fine - but not what I needed.
Have I missed something, should I have converted somewhere?

Storm ;-)

Good info too perove!

I shall do some playing in morning (mind you that is only 10 mins away as it is 23.50 here!)

Storm ;-)

Hi Storm

ok your problem is interesting..so I shall have a stab.

My own viewpoint is you should have 1 number in tables and this is used to make up new numbers etc eg F111200-0001... your code looks at this and makes F111200-002 (Dept may vary of course!!!)

So what I would do is use WEE FUCTION CALL
MaxCoyNo = DomainMax_TSB("", "COY_NO", "COMPANY", "")


Ideally you want your recordset to only show your field ie F111200-0001 but only your right 4 characters 0001..this way you get last number used and the you can add Dept and 111200- to number.

follow???


Function DomainMax_TSB (strDatabase As String, strField As String, strDomain As String, strCriteria As String) As Variant
'Comments  : Returns the Maximum value of a field in a specified set of records
'Parameters: strDatabase - path and name of database to look in or "" (blank string) for the current database
'            strField - name of the field to return the Maximum value for
'            strDomain - name of the table or query to search in
'            strCriteria - string expression specifying the WHERE clause of the query or blank for no constraints (all records)
'Returns   : Maximum value of the specified field as a variant, or NULL if no records matching strCriteria are found
'
  Dim dbsTemp As Database
  Dim rstDomain As Recordset
  Dim strSQL As String

  ' Assume no records found
  DomainMax_TSB = Null

  If strDatabase = "" Then
    Set dbsTemp = CurrentDB()
  Else
    Set dbsTemp = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
  End If
 
  strSQL = "SELECT Max([" & strField & "]) AS DataReturned FROM [" & strDomain & "] "
  If strCriteria <> "" Then
    strSQL = strSQL & "WHERE " & strCriteria
  End If
  strSQL = strSQL & ";"

  Set rstDomain = dbsTemp.OpenRecordset(strSQL)
  If rstDomain.RecordCount > 0 Then
    DomainMax_TSB = rstDomain![DataReturned]
  End If

  rstDomain.Close
  dbsTemp.Close

End Function

With approach number 1 from me, yes, you would be able to index the whole field in your main table.

That's strange about not being able to use the Date as a default value. I'll have to think about that one . . .

brewdog: I think both of your solutions would have been valid. As it goes I created my final solution with a bit of both of them - and it works!!!

Good comments from many of you - but brewdog methods were clear and simple.

Many thanks,

Storm ;-)

Great! Glad to be of some help . . .