Mimicing an Autonumber Field

  • Thread starter Thread starter Ken
  • Start date Start date
K

Ken

Hello all:

I have an application where I want to keep a series of notes using a
text field in a table just for these notes. My primary key across the
application is ID_Number, I need this table to have a structure like
this:

ID_Number
Sequence_Number (for making the notes unique within ID_Number)
Notes_Text

What would be the best technique for automatically assigning the next
sequence_number within ID_Number?

TIA

Ken Halpern
Margate FL
 
Ken,
Since you already have the ID relationship with your other records, just
a DateTime field with a default value of NOW would be all you need to
sequence your notes properly.
If you must use a number... a default value for SeqNo could be...
= NZ(DMax("[SeqNo]", "tblNotes") + 1
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."
 
Slight typo: there's a missing closing parentheses.

= NZ(DMax("[SeqNo]", "tblNotes")) + 1

However, Ken said he wanted Sequence_Number to be unique within ID_Number,
so you'd probably want:

NZ(DMax("[Sequence_Number]", "tblNotes", "[ID_Number] = " &
Me.txtID_Number), 0) + 1


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Al Campagna said:
Ken,
Since you already have the ID relationship with your other records,
just a DateTime field with a default value of NOW would be all you need to
sequence your notes properly.
If you must use a number... a default value for SeqNo could be...
= NZ(DMax("[SeqNo]", "tblNotes") + 1
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."


Ken said:
Hello all:

I have an application where I want to keep a series of notes using a
text field in a table just for these notes. My primary key across the
application is ID_Number, I need this table to have a structure like
this:

ID_Number
Sequence_Number (for making the notes unique within ID_Number)
Notes_Text

What would be the best technique for automatically assigning the next
sequence_number within ID_Number?

TIA

Ken Halpern
Margate FL
Click to expand...
Click to expand...
 
Both good ways. I didn't really think about using the datetime field,
that just may be easiest of all.

Thanks to all



Slight typo: there's a missing closing parentheses.

= NZ(DMax("[SeqNo]", "tblNotes")) + 1

However, Ken said he wanted Sequence_Number to be unique within ID_Number,
so you'd probably want:

NZ(DMax("[Sequence_Number]", "tblNotes", "[ID_Number] = " &
Me.txtID_Number), 0) + 1

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




Ken,
Since you already have the ID relationship with your other records,
just a DateTime field with a default value of NOW would be all you need to
sequence your notes properly.
If you must use a number... a default value for SeqNo could be...
= NZ(DMax("[SeqNo]", "tblNotes") + 1
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html
"Find a job that you love... and you'll never work a day in your life."
Click to expand...

- Show quoted text -
Click to expand...
 
Back
Top