Default value of a record equal to previous record in date format

[isben22]

I had a resource for this in the past but lost it somewhere...

I have a need to enter daily information. Every day has to be accounted for, 24/7.

I would like to make the default value of the date record, sequential; i.e. each new record is +1 day of the record before it.

I know I cannot refference a record in the same table, but I cannot seem to find the reference on how to build the query.

Anyone have a reference for this?

[puppydogbuddy]

See this link:

http://forums.mysql.com/read.php?65,...026#msg-148026

replace the fields used in the example with your fields.

[isben22]

See this link:

http://forums.mysql.com/read.php?65,...026#msg-148026

replace the fields used in the example with your fields.

Sorry. New to the posting world. I should have specified that I am working with MS Access 2003.

This has led me to the idea of using a form to add a date to the default value but returned an error.

I used this line (from the help file) to try and return a default value:
=DMax([ID],[Date],+1)

But came up with the #Error.

Where should I go?

[MMcCarthy]

Sorry. New to the posting world. I should have specified that I am working with MS Access 2003.

This has led me to the idea of using a form to add a date to the default value but returned an error.

I used this line (from the help file) to try and return a default value:
=DMax([ID],[Date],+1)

But came up with the #Error.

Where should I go?

You want ...

=DMax("[Date]","TableName")+1

[puppydogbuddy]

The syntax provided in the link I gave you is correct even though it is in a MySql forum.

As instructed, you should put your date autonumber expression in the before insert event of the form as shown:.For purposes of illustration, I have named the textbox on your form txtAutoDate, and named the underlying field in your table as fldAutoDate. Me.RecordSource refers to the query that is the data source of your form.

The expression shown below should generate unique sequential numbers. Logically, however, what you are trying to do does not make sense because if you have two transactions on the same day, they will be assigned different date autonumbers. I don't understand why you don't want to use a date and time stamp as your autonumber.


Private Sub YourFormName_BeforeInsert()
Me!txtAutoDate = DMax("[fldAutoDate]", Me.RecordSource) + 1)
End Sub

[isben22]

You want ...

=DMax("[Date]","TableName")+1

Getting there! Ok got it to work without error. I had to add a comumn to the form (not an issue as there will naturally be other columns) But it has an interesting result.

it lists each date twice like:

ID Date
1 2/2/2007
2 3/2/2007
3 3/2/2007
4 4/2/2007 and so on...

what could be causing this doubling? I really only want one day per record. I will then have a sub table to make a list of people assigned to a day...

Thanks again. Could not have gone this far without you!

[JConsulting]

Getting there! Ok got it to work without error. I had to add a comumn to the form (not an issue as there will naturally be other columns) But it has an interesting result.

it lists each date twice like:

ID Date
1 2/2/2007
2 3/2/2007
3 3/2/2007
4 4/2/2007 and so on...

what could be causing this doubling? I really only want one day per record. I will then have a sub table to make a list of people assigned to a day...

Thanks again. Could not have gone this far without you!

I'm thinking a small function in a code module like so

Function GetLastDate() As Date
GetLastDate = DateAdd("d", 1, DMax("date1", "tblTest"))
End Function

then in the default value for that field in the table

=GetLastDate

When new records are added...this date is automatically entered.

J
PS. Hey PDog!

[isben22]

I'm thinking a small function in a code module like so

Function GetLastDate() As Date
GetLastDate = DateAdd("d", 1, DMax("date1", "tblTest"))
End Function

then in the default value for that field in the table

=GetLastDate

When new records are added...this date is automatically entered.

J
PS. Hey PDog!

PDog,

I understand your question and maybe I am not attacking this logically.

The large scale event is to create a personnel schedual with a running date.
1 APR 2 APR 3APR
John X / Q
Marry X Q /
Jean Q / Q
Larry X X X

It will look like this in the end. For the purposes of the people I work for, the program will need to work just like the form we use or the program will not be used. Silly, but a necessity.

So I thought the easyest way to accomplish this was to create a table that sequentially gives you the dates with a sub table on the duty each person will preform for that day.

We have 20 people all preforming different duties on each day, and there is never a time where a day is left unaccounted for.

Did I start down the wrong path? If you have an easier way to accomplish this I would be delighted to try it! I thought a master table with the dates and sub tables for the person and events would work best... I dunno... Thoughts?

M

[isben22]

PDog,

I understand your question and maybe I am not attacking this logically.

The large scale event is to create a personnel schedual with a running date.
1 APR 2 APR 3APR
John X / Q
Marry X Q /
Jean Q / Q
Larry X X X

It will look like this in the end. For the purposes of the people I work for, the program will need to work just like the form we use or the program will not be used. Silly, but a necessity.

So I thought the easyest way to accomplish this was to create a table that sequentially gives you the dates with a sub table on the duty each person will preform for that day.

We have 20 people all preforming different duties on each day, and there is never a time where a day is left unaccounted for.

Did I start down the wrong path? If you have an easier way to accomplish this I would be delighted to try it! I thought a master table with the dates and sub tables for the person and events would work best... I dunno... Thoughts?

M

All that and the "sample table did not come out as I typed it... should I be clearer?

[puppydogbuddy]

If you want track the activities of each person for each date using the date autonumber for the date, that won't work because all 20 people will be assigned different date autonumbers for the same activity date.

There are a number of different ways to design the structure of your tables, but I would not use the date a as an autonumber key for the table. Instead, I would use a meaningless Access generated autonumber for internal system use as the primary key for the table.

In addition, I would have the activity date as separate field that could be part of a combined [employee name] & [activity date] index that you would use in presenting the daily record to your users. That way all 20 users could have the same activity date. And if you don't want them to have to type in the activity date, you can set its default value property to the system date (Date()).