Next Date Sequence

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thought sure I'd find this one already asked but ...
I have a form that requires an entry for every day. I want to automatically
sequence through each day in the month (have the date field populated
automatically). I know you can create a sequence object in SQL, but where do
you create that in Access (or VBA)? Thanks
 
Okschlaps,

There is sure to be a way to achieve what you want. But can you please
explain a bit more detail. Do you mean each new record added should
have the date field entered the next day from the previous record? Or
do you mean you want the current date entered by default for a new
record? Or do you mean you want to mass create a whole bunch of records
with sequential dates? If so, what span of dates? Or do you mean
something else?
 
Each new record added should have the date field entered the next day from
the previous OR since I have about 25 clients who each need an entry for each
day of the month, would it be possible to mass create records for each of
those?
 
Okschlaps,

On a suitable event on your form, perhaps the Before Update event of the
form itself, or the After Update event of the client, you could put code
the equivalent of this...
Me.YourDateField = DMax("[YourDateField]","YourTable","[ClientID]=" &
Me.ClientID) + 1

As for mass creation of records, I would need to know more details of
what you want... can you give an example?
 
Thanks, Steve! As for the mass create. there are 25 employees and each has to
have a record for each day. If I could, say at the beginning of the month,
create a record with each employee id populated and each day of the month
populated - so for April employee 0005 would have 30 records with just his id
and the date fields populated, my client would be thrilled.


Steve Schapel said:
Okschlaps,

On a suitable event on your form, perhaps the Before Update event of the
form itself, or the After Update event of the client, you could put code
the equivalent of this...
Me.YourDateField = DMax("[YourDateField]","YourTable","[ClientID]=" &
Me.ClientID) + 1

As for mass creation of records, I would need to know more details of
what you want... can you give an example?

--
Steve Schapel, Microsoft Access MVP

Each new record added should have the date field entered the next day from
the previous OR since I have about 25 clients who each need an entry for each
day of the month, would it be possible to mass create records for each of
those?
Click to expand...
Click to expand...
 
Okschlaps,

1. Make a table with one field (lets call it DaySeq) and enter numbers
from 0 to 30.
2. Make a query which includes this table, and also the employee table
or wherever you have got the employee id of all the employees, not joined.
3. Make a calculated field in this query, like this...
DateToAdd: DateSerial(Year(Date()),Month(Date()),1)+[DaySeq]
4. Make this query into an Append Query, and run it to add the records
as required to the table in question.

You will have to find a way to keep this to the maximum number of days
for the month. I think it would work to put this in the criteria of the
DaySeq field in the query...
<Day(DateSerial(Year(Date()),Month(Date())+1),0))
 
Okschlaps,

I can't understand what the problem is here. It should include all IDs.
Please post the SQL view of the query, and I might be able to spot
what is happening.
 
Back
Top