The database that I am working on requires that every day the numbering of 
the wire agreements start with the number one ("1") and increment by one 
throughout the day.  For example, on Monday the agreements would start 
at "1" and go up to "85."  On Tuesday the numbering would start over 
at "1".  Can anyone give me some assistance on the best way to set this 
up?  Thank you!

Lynn,

<< The database that I am working on requires that every day the numbering 
of the wire agreements start with the number one ("1") and increment by 
one throughout the day.  For example, on Monday the agreements would start 
at "1" and go up to "85."  On Tuesday the numbering would start over 
at "1".  Can anyone give me some assistance on the best way to set this 
up? >> 

First, do NOT set the wire agreements field equal to an autonumber and the 
primary field.  There's never any reason to renumber primary key 
autonumbers because they're used for reference only, not for "meaningful" 
data.  Make sure the wire agreement field is on its own.

Create a table that holds only one field:  the date the last time the 
database was opened, say dtmLastOpen.

Now for the counting.  Hmmm... try this:  When you open the form, the 
first thing to do is compare today's date with dtmLastOpen.  Using code on 
the form's "On Current" event, if dtmLastOpen < Date() then set 
Me.txtWireAgreement.DefaultValue = 1, else set 
Me.txtWireAgreement.DefaultValue = Me.RecordCount + 1.  Why default 
value?  Because in case someone changes his/her mind and closes the form, 
you don't want the wire agreement value to store anything.  If they add 
data elsewhere in the form, the wire agreement will be automatically be 
the default.  Remember, if data is entered anywhere in the form, you MUST 
set dtmLastOpen = Date().

Now, if someone deletes something and you must renumber everything for 
that day, you'll have to handle that!   :)

Let me know if that does the trick.

Greg

Do you have a multi-user system, are you using Access tables to store the
data or a SQL back-end, is your app written using ADO or DAO, what data
access methods are you using (locking, recordset types etc), does the
cutover happen at midnight or at some other time... these are a few things
that need to be known before the right answer can be found.

-----Original Message-----
From: Lynn Morgenson [mailto:lmorgenson@j...]
Sent: Tuesday, November 26, 2002 3:21 PM
To: Access
Subject: [access] Resetting incremental numbers


The database that I am working on requires that every day the numbering of 
the wire agreements start with the number one ("1") and increment by one 
throughout the day.  For example, on Monday the agreements would start 
at "1" and go up to "85."  On Tuesday the numbering would start over 
at "1".  Can anyone give me some assistance on the best way to set this 
up?  Thank you!

Answering your questions:  This is an Access database and I am storing the 
data in Access tables.  There will only be one person processing these 
wire agreements at a time.  Not quite sure what you mean by ADO or DAO 
(I'm rather new to this...).  Cut-off time is 5:00 p.m.


> Do you have a multi-user system, are you using Access tables to store the
data or a SQL back-end, is your app written using ADO or DAO, what data
access methods are you using (locking, recordset types etc), does the
cutover happen at midnight or at some other time... these are a few things
that need to be known before the right answer can be found.

-----Original Message-----
From: Lynn Morgenson [mailto:lmorgenson@j...]
Sent: Tuesday, November 26, 2002 3:21 PM
To: Access
Subject: [access] Resetting incremental numbers


The database that I am working on requires that every day the numbering of 
the wire agreements start with the number one ("1") and increment by one 
throughout the day.  For example, on Monday the agreements would start 
at "1" and go up to "85."  On Tuesday the numbering would start over 
at "1".  Can anyone give me some assistance on the best way to set this 
up?  Thank you!

Hi Lynn,

Assuming that you have a table tblTestAutoNumber for these records with 
the
following fields:

RecordDate	Date/Time 	Format =3D ShortDate, Default =3D Date()
Sequence	Autonumber
Other Fields as desired.

You can use the following query to reset the autonumber field.  Use the 
SQL
window to create it.

ALTER TABLE tblTestAutoNumber ALTER COLUMN Sequence COUNTER (1,1);

If you compose the Primary Key as both of the first two fields 
(RecordDate
and Sequence), you will need to run this query daily, prior to ANY data
entry.

Give it a try, and let me know how it works.

Good Luck!

Rand E Gerald
Database Specialist
Information Services / Operations
Bah=E1'=ED National Office
1233 Central St.
Evanston IL 60201
(xxx) xxx-xxxx

-----Original Message-----
From: Lynn Morgenson [mailto:lmorgenson@j...]
Sent: Tuesday, November 26, 2002 15:21
To: Access
Subject: [access] Resetting incremental numbers

The database that I am working on requires that every day the numbering 
of
the wire agreements start with the number one ("1") and increment by 
one
throughout the day.  For example, on Monday the agreements would start
at "1" and go up to "85."  On Tuesday the numbering would start over
at "1".  Can anyone give me some assistance on the best way to set this
up?  Thank you!

If it is single user then you don't have to worry about locking and
concurrency - for example if two users tried to create a record at the same
time, which one gets which number?

Since it is single-user in Access ADO/DAO is irrelevant I think ;)


I would have a table ("tbl_MaxID") that had two fields : IDdate and maxid.  

When you create a wire agreement record, use the DLookup function

private sub cmdSave_Click()

dim newID as variant
dim rs as recordset
newID = DLookup("maxid", "tbl_MaxID", "IDdate='" & date() & "'")
if not isnull(newID) then   'current date found, increment counter
  newID = newID + 1
  ' update newID back to table
  currentdb.execute "update tbl_MAxID set maxid = " & cstr(newID)
else 'current date not found, so put it there
  newID = 1
  currentdb.execute "update tbl_MAxID set iddate = '" & date() & "', maxid 
" & cstr(newID)  
endif
...

you might have to control the precision of the date field to strip times
off...

  

-----Original Message-----
From: Lynn Morgenson [mailto:lmorgenson@j...]
Sent: Tuesday, November 26, 2002 4:43 PM
To: Access
Subject: [access] Re: Resetting incremental numbers


Answering your questions:  This is an Access database and I am storing the 
data in Access tables.  There will only be one person processing these 
wire agreements at a time.  Not quite sure what you mean by ADO or DAO 
(I'm rather new to this...).  Cut-off time is 5:00 p.m.


> Do you have a multi-user system, are you using Access tables to store the
data or a SQL back-end, is your app written using ADO or DAO, what data
access methods are you using (locking, recordset types etc), does the
cutover happen at midnight or at some other time... these are a few things
that need to be known before the right answer can be found.

-----Original Message-----
From: Lynn Morgenson [mailto:lmorgenson@j...]
Sent: Tuesday, November 26, 2002 3:21 PM
To: Access
Subject: [access] Resetting incremental numbers


The database that I am working on requires that every day the numbering of 
the wire agreements start with the number one ("1") and increment by one 
throughout the day.  For example, on Monday the agreements would start 
at "1" and go up to "85."  On Tuesday the numbering would start over 
at "1".  Can anyone give me some assistance on the best way to set this 
up?  Thank you!