How can I reset a field every day to 1?

[Michael]

I have a form that counts the number of deliveries each day. How can I reset
the field to number one each day?
thank you
Michael

 

[Guest]

Do you mean that you reuse the same record every day? There are probably
ways to accomplish what you need, but what that may be is not clear. Some
details of your database design would help.

 

[Michael]

Sorry... not the same record, just reset the count for the amount of
deliveries. Each delivery has another ID that counts the deliveries, for
instance

deliveryID, DailyDeliveryID,Shipper,Date


the deliveryID would be the key number that would stay with the order, the
dailydeliveryID is just a way to track how many deliveries were made each
day. now they manually enter 1,2,3 and so on.

thank you
michael

 

[Guest]

In general you would use something like this as the default value of a text
box in order to increment the number by one with each new record:
=Nz(DMax("DailyDeliveryID","tblDailyDelivery")+1)
However, in my tests I have not been able to find out how to use this method
to increment the number in a subform record source (i.e. a table related to
the main table). I don't know anything about your database structure
(tables, relationships, etc.) so I don't know for sure if this is what you
are trying to do. If you are I cannot help, I am afraid. I would suggest in
that case that you post a new thread entitled something like "Incrementing
number in subform".

 

[Craig Alexander Morrison]

You have to include the Date field when you do the DMax (or use a query that
only includes the date in question) but there is a better way, using just
the date field.

That is in another answer to this same question - see 2nd post ---

 

[Michael]

Thank you Bruce,
It is in a main form, but my problem is not incrementing, the number, but it
resetting it to 1 each day
thank you for trying
Michael

 

[Rick Brandt]

Thank you Bruce,
It is in a main form, but my problem is not incrementing, the number,
but it resetting it to 1 each day
thank you for trying

Assuming a table with a CreatedDate field...

=Nz(DMax("DailyDeliveryID","tblDailyDelivery", "Day(CreatedDate) =
Day(Date)"), 0) + 1

However; I would not recommend using the DefaultValue for this unless you
are only going to have one user entering records and only on a single view
form.

The DefaultValue will not work at all on a continuous form and with multiple
users doing data entry you really have to assign the value in the
BeforeUpdate event to minimize the odds of two users grabbing the same
value.

 

[Guest]

Good point about multiple users. I would just point out to Michael that the
code you provided will assure that the number is 1 each day, even if there is
no need for a number 2, 3, etc.

 

[Rick Brandt]

Good point about multiple users. I would just point out to Michael
that the code you provided will assure that the number is 1 each day,
even if there is no need for a number 2, 3, etc.

Isn't that what he requested, a number sequence that starts over each day?

 

[Ronald Roberts]

Create a table with a date field and the counter as long.
In the start up of Access or your first form to be opened,
check to see if the date in the record matches todays date.
If it does not, reset the counter to 1. The first user that
starts the application will reset the counter. The rest will
just increment the counter.

Something like this. Air Code
Sub CheckDate()
dim recDate as date
dim db as database


RecDate = Dlookup("tblDate,"DateTableName")
If RecDate = Date then 'May need formating
exit Sub
endif
Set db=CurrentDB
db.Execute "UPDATE DateTableName SET DateTableName.tblCounter = 1;"

end Sub


In the Access Startup, or the Open event of the first form, call
the subroutine

CheckDate

You will need to make sure all users have the correct date and time
at all times.

HTH

Ron

 

[Guest]

At first I thought that was what he wanted, but then he wrote wrote: "my
problem is not incrementing, the number, but it resetting it to 1 each day."
This left me uncertain what he wanted other than starting with "1" each day.
I was just telling him that if all he needs is a "1" every day your method
would do that.

 

[Michael]

Hey thanks guys, sorry I did not get back sooner. I need the field to reset
to 1 each day and then increment for each record. Sorry if I was not clear.
I have tried many combinations and have not been able to get it to work.
When I have tried to enter the code just changing my fields names, get
error messages. I will try to send the messages and my code later.
Take care
Michael