"Michael Gramelspacher" <gramelsp[ at ]psci.net> wrote in message
news:6oq0k45k1crvepr0lde724odm9bpjii47l[ at ]4ax.com...
[Quoted Text] > On Wed, 10 Dec 2008 21:30:45 -0000, "Steve" <steveB[ at ]nowhere.com> wrote:
>
>>I am trying to create an incrementing ID based on a date prefix like
>>"CC-10-Dec-2008-001" using an example by Helen Feddema from Access Archon
>>Column 114 from www.helenfeddema.com/access.htm
>
> I think most people would advise against doing what you want to do. Just
> have two fields, one for
> the date and another for the number. these two fields would have a unique
> index in the table.
>
> In the AfterUpdate event of the date field, lookup the max number already
> used for the date field
> value and add 1 to it. Put this value in the number field.
>
> You can easily combine the number and date on a report so as to appear as
> you wish.
>
> CREATE TABLE SomeTable (
> this_date DATETIME NOT NULL,
> sequence_num INTEGER NOT NULL,
> UNIQUE (this_date, sequence_num)
> );
>
> Form: txt_this_date, txt_sequence_num
>
> Private Sub txt_this_date_AfterUpdate()
>
> If Not IsNull(Me.txt_this_date) Then
> If IsDate(Me.txt_this_date) Then
> Me.txt_sequence_num = Nz(DMax("sequence_num", "SomeTable", _
> "this_date=#" & Me.txt_this_date & "#"), 0) + 1
> MsgBox "CC-" & Format(Me.txt_this_date, "dd-mmm-yyyy") _
> & "-" & Format(Me.sequence_num, "000")
> End If
> End If
>
> End Sub
>
Thanks Micheal, I'll give that a go. It does look a lot "cleaner" than the
method I was trying to use.
Regards
Steve
|