Date as Primary Key with Auto increment

[Sharon M via AccessMonster.com]

I am trying to auto increment my date field in a table (which is my primary
key) automatically once i enter a new record.

for example:

My table (Weather_Delays) has only date and desc fields, so when the user
enters a new record, the date automatically increases from previous recordset
date +1 day.

thanx in advance

 

[Guest]

Dim dtmNextDate As Date
Dim dtmLastDate as Date

dtmLastDate = Nz(DMax("[DateFieldName]","Weather_Delays"), _
DateAdd("d",-1,Date))
dtmNextDate=DateAdd("d",1,dtmLastDate)

In the first line, we use the DMax to return the highest date in the date
field for the table. Because it is possible to get a Null value returned
(for example, the table is empty), we use the Nz function to return
yesterday's date. We return yesterday, because in the next line, we will add
1 day to it.

In the second line, we add 1 day to the highest date in the table.

 

[Sharon M via AccessMonster.com]

Hi Klatuu, many thanks for that,

however just wondering where i should write this code, eg. within what event
procedure within my form?

Dim dtmNextDate As Date
Dim dtmLastDate as Date

dtmLastDate = Nz(DMax("[DateFieldName]","Weather_Delays"), _
DateAdd("d",-1,Date))
dtmNextDate=DateAdd("d",1,dtmLastDate)

In the first line, we use the DMax to return the highest date in the date
field for the table. Because it is possible to get a Null value returned
(for example, the table is empty), we use the Nz function to return
yesterday's date. We return yesterday, because in the next line, we will add
1 day to it.

In the second line, we add 1 day to the highest date in the table.

I am trying to auto increment my date field in a table (which is my primary
key) automatically once i enter a new record.

[quoted text clipped - 6 lines]

thanx in advance

 

[Guest]

I would try the Current event of the form, check to see if it is a new
record, and if it is, do the code.

Hi Klatuu, many thanks for that,

however just wondering where i should write this code, eg. within what event
procedure within my form?

Dim dtmNextDate As Date
Dim dtmLastDate as Date

dtmLastDate = Nz(DMax("[DateFieldName]","Weather_Delays"), _
DateAdd("d",-1,Date))
dtmNextDate=DateAdd("d",1,dtmLastDate)

In the first line, we use the DMax to return the highest date in the date
field for the table. Because it is possible to get a Null value returned
(for example, the table is empty), we use the Nz function to return
yesterday's date. We return yesterday, because in the next line, we will add
1 day to it.

In the second line, we add 1 day to the highest date in the table.

I am trying to auto increment my date field in a table (which is my primary
key) automatically once i enter a new record.

[quoted text clipped - 6 lines]

thanx in advance

 

[Sharon M via AccessMonster.com]

Ok, still no luck i'm afraid with the following code:

Private Sub Form_Current()
If Me.NewRecord Then
MsgBox "Test New Record"
Dim dtmNextDate As Date
Dim dtmLastDate As Date

dtmLastDate = Nz(DMax("[W_Date]", "Weather_Delays"), _
DateAdd("d", -1, Date))
dtmNextDate = DateAdd("d", 1, dtmLastDate)
End If
End Sub

Have no idea where i'm going wrong, but will crack on and keep post updated
if i suceed!

I would try the Current event of the form, check to see if it is a new
record, and if it is, do the code.

Hi Klatuu, many thanks for that,

[quoted text clipped - 21 lines]

 

[Guest]

Are you getting an error? What results are you getting?

Ok, still no luck i'm afraid with the following code:

Private Sub Form_Current()
If Me.NewRecord Then
MsgBox "Test New Record"
Dim dtmNextDate As Date
Dim dtmLastDate As Date

dtmLastDate = Nz(DMax("[W_Date]", "Weather_Delays"), _
DateAdd("d", -1, Date))
dtmNextDate = DateAdd("d", 1, dtmLastDate)
End If
End Sub

Have no idea where i'm going wrong, but will crack on and keep post updated
if i suceed!

I would try the Current event of the form, check to see if it is a new
record, and if it is, do the code.

Hi Klatuu, many thanks for that,

[quoted text clipped - 21 lines]

thanx in advance

 

[Sharon M via AccessMonster.com]

When i click on "new" record on the navigation bar, i get the test msg, but
the date does not update and the W_Date field remains blank. Its probably
something really simple that i'm missing here, umm

Are you getting an error? What results are you getting?

Ok, still no luck i'm afraid with the following code:

[quoted text clipped - 22 lines]

 

[Guest]

I don't see in the code that you are assigning the value in dtmNextDate to a
control on your form. Let's assume you have a control on your form called
txtWDate and the control source is W_Date. Then the last line of the sub
should be:
Me.txtWDate = dtmNextDate

When i click on "new" record on the navigation bar, i get the test msg, but
the date does not update and the W_Date field remains blank. Its probably
something really simple that i'm missing here, umm

Are you getting an error? What results are you getting?

Ok, still no luck i'm afraid with the following code:

[quoted text clipped - 22 lines]

thanx in advance

 

[Sharon M via AccessMonster.com]

Hi Klatuu, thank you for all your help this week. Yes you're right, i have
assigned to txt box on form and now works perfectly,
thanx again

I don't see in the code that you are assigning the value in dtmNextDate to a
control on your form. Let's assume you have a control on your form called
txtWDate and the control source is W_Date. Then the last line of the sub
should be:
Me.txtWDate = dtmNextDate

When i click on "new" record on the navigation bar, i get the test msg, but
the date does not update and the W_Date field remains blank. Its probably

[quoted text clipped - 7 lines]