Automatic Date Entry (Date=LastDate+1)?? 2

[owizard]

Hi,

This problem probably has a very simple solution. Unfortunately, I couldn't find it.

I have a form connected to a Table with Dates and associated info. The Structure is:

Date Info1 Info2 Info3

The table already has some records with dates and other info. I want the date to be autofilled when I move to a new record. This new date should be "Date in the Last Record + 1" .

For example, if the date in the last record is 20/02/2002,
I want the date 21/02/2002 to automatically appear in the date field when I move to a new record.

I am also looking for a function that would automatically fill dates for the next thirty days by taking the date in the last record into account. In other words, this function should create 30 new records with just dates by calculating them from the date in the last record.

I badly need a solution for this problem. Thanks for your kind attention. Please respond soon.

 

[jdsf]

Try changing your date to a long integer. Microsoft increments their dates by days (add 1 to any number and you add a day). Convert the number for display or storage.

cdate(37307) = 20 February 2002
cdate(37308) = 21 February 2002
etc.

Hope this helps.

 

[AKMonkeyboy]

Make an unbound field and call it next date. Set the control source for this field to:

=DLast("[Date]","[Table1]&quot+1

(Where [Date] is your date field, and [Table1] is your source table)

Next, make the On Enter event for the date field this:

Private Sub date_Enter()

If Me.date = DLast("[Date]", "[Table1]&quot Then

GoTo Exit_Date_Enter

ElseIf Me.date - Me.nextdate <> 1 Then

GoTo Exit_Date_Enter

Else

Me.date = Me.nextdate

End If

Exit_Date_Enter:

Exit Sub

End Sub


I tested this several times and didn't see any bugs. Hope it works for you too.

 

[bradmaunsell]

Try looking at help for the DateAdd function. I have successfully used this function (a lot)


x = DateAdd(&quot;d&quot;, 30, [OrderDate])

 

[owizard]

Hi,

Thanks to all of you for your replies. I have tried AK's solution. It works most of the time. However, there is a problem. After entering a certain number of records, the code mysteriously repeats the same date 3-4 times. For instance, if I start from 20/02/02, the function works normally for may be 200-300 records before attempting to enter the same date several times. The same date can't be entered again, as the date field is indexed with no duplicates.

This happens in a random fashion, i.e., there is no specific date interval for this problem. I have even tried it in a new database with just a single table. Still, I am facing the same problem.

I would really appreciate if AK or anyone else can proide a solution for this problem. Thanks a lot.

 

[Craig0201]

Hmmm.....it's probably because you are assigning the DLast to a form control.....

So if I do it at time A and then you do it before i've had my record saved, we'll get the same date......

What you need to do is have the date in a table and have that increment on selection....

But what is this for....strikes me as VERY odd....

Craig

 

[AKMonkeyboy]

Perhaps your mysterious problem has to do with the next date field not updating properly. I couldn't get the problem replicated. I did update the &quot;code&quot; somewhat, allowing you to get rid of the [nextdate] field. You can do the whole thing in VB, using the On Enter event. Here's what I have:

Private Sub date_Enter()

If Me.date = DLast(&quot;[Date]&quot;, &quot;[Table1]&quot Then

GoTo Exit_Date_Enter

ElseIf Me.date - (DLast(&quot;[Date]&quot;, &quot;[Table1]&quot + 1) <> 1 Then

GoTo Exit_Date_Enter

Else

Me.date = DLast(&quot;[Date]&quot;, &quot;[Table1]&quot + 1

End If

Exit_Date_Enter:

Exit Sub

End Sub


Trial and error. That's how I learned. As for adding 30 records automatically, how about using an unbound form with a target date. For example. Your last date entered is 31/12/2001. You would want the program to generate append the next 30 days automatically, right? I made an append query called qryadddate. Here's the SQL:

INSERT INTO Table1 ( [date] )
SELECT Forms!FrmAddMonth!NextDate AS [Next];

I made an unbound form called FrmAddMonth. I have three fields.

[Date], control source set to =DLast(&quot;[Date]&quot;,&quot;[Table1]&quot

[LastDate], control source set to =DLast(&quot;[Date]&quot;,&quot;[Table1]&quot+1[

[TargetDate], default value set to =DLast(&quot;[Date]&quot;,&quot;[Table1]&quot+30

Next I made a button on the form called &quot;BtnAddMonth&quot; in the On Click event I put this:

Private Sub BtnAddMonth_Click()
On Error GoTo Err_BtnAddMonth_Click

Dim stDocName As String

stDocName = &quot;Qryadddate&quot;

Do Until DLast(&quot;[Date]&quot;, &quot;[Table1]&quot = Me.LastDate

Me.Refresh
DoCmd.SetWarnings 0
DoCmd.OpenQuery stDocName, acNormal, acEdit


Loop

DoCmd.SetWarnings -1


Exit_BtnAddMonth_Click:
Exit Sub

Err_BtnAddMonth_Click:
MsgBox Err.Description
Resume Exit_BtnAddMonth_Click

End Sub


I would probably add some sort of msg box to let you know that it ran successfully, and maybe a close form command so you have to reopen the form to reset your target date.

Let me know if this works for you. As for the other mysterious bug, I am still trying to get it to duplicate.

I do have a life besides access (not much of one). Gotta get the garbage out. I'll check later to see if it worked for you.

Later

 

[AKMonkeyboy]

I guess I screwed that up. Sorry, you may have figured out that that didn't even come close to working. The problem is the field names. The line of code that says:

Do Until DLast(&quot;[Date]&quot;, &quot;[Table1]&quot = Me.LastDate

Should read

Do Until DLast(&quot;[Date]&quot;, &quot;[Table1]&quot = Me.TargetDate

Finally, the field that I wrote in the previous post read:
[LastDate], control source set to =DLast(&quot;[Date]&quot;,&quot;[Table1]&quot+1[

The unbound form should not contain a [LastDate] field, but rather a [NextDate] field and the control source would be =DLast(&quot;[Date]&quot;,&quot;[Table1]&quot+1


Sorry, I didn't proof that as well as I should have.

Let me know if I've screwed you up. I can e-mail you the database that I created to test this if you'd like.

A

 

[psarros]

Am I missing something?

Could you not just make the default value of the date field:

Me.date = DLast(&quot;[Date]&quot;, &quot;[Table1]&quot + 1

short and sweet

 

[AKMonkeyboy]

No, that doesn't allow you to advance through records while automatically calculating the next date. Try it, maybe I tried it wrong, but it didn't work.

 

[owizard]

Hi AK,

You are absolutely right. When I was looking for the solution, the first thing I tried was &quot;Me.date = DLast(&quot;[Date]&quot;, &quot;[Table1]&quot + 1&quot; as the default value. It didn't work. Thanks a lot for the solutions. I will inform you, if I find any problems. Cheers.