Automatic date Insertion

[lee123]

Hello its been awhile since i have been on this page but was wondering if someone could help me figure out this problem?

I have a field on my form named "Tdate" actually it's in a subform on a form and right now i have to enter in a certain date and the subform is set to continuous form. so my question is....how can i enter in a date and when i have entered in all the fields for that record have the next date automatically enter in the next record. for instance if i was to enter in a date such as 10/01/09 then the next record in the "Tdate" field would be 10/02/09 and so on.

Can this be done? or is there another way to do this...

lee123

[NeoPa]

In the AfterUpdate event procedure for TDate, set the .Default property to TDate + 1.

[lee123]

So i put it like this:

Private Sub tDate_AfterUpdate()
    tDate.DefaultValue = tDate + 1
End Sub

lee123

[NeoPa]

You've got the idea, but it's not the .DefaultValue property.

[lee123]

ok i don't understand what you mean by default property because there is only the "DefaultValue" there unless im not understanding what your talking about.

lee123

[NeoPa]

OK. Let me check this out for you here. Tell me, what version of Access are you using, and what type of control is tDate?

[lee123]

Ok the version of "Access is 2000 Premium / Developers Tools 1.5" and the control is a "Textbox" Sorry for the incomplete info i should of said in my first post what version i was using

[NeoPa]

I'm sorry Lee. I was sure I had it right, but it seems DefaultValue it is, and DefaultValue it always was. I do apologise for wasting your time.

[lee123]

so there is no way to do this? or someone else might know how.

lee123

[NeoPa]

No. What I'm saying is that you suggestion was perfectly correct. I was in error to try to correct you.

That code should work for you. If it doesn't then please post the details so I can think about it further. I have no reason to expect it not to though.

[lee123]

well the code works but it leaves a time instead of the date.

lee123

[NeoPa]

Dates & times are generally stored together, so I expect it is simply showing (formatted) as a time (but is a Date/Time in disguise). Having said that, I expect you really want a pure date for something like this. It looks like you need to find out why [tDate] has a date with an associated time in the first place ;)

[lee123]

gee i never new how difficult this is turning out to be thanks anyway

lee123

[NeoPa]

Can you say how [tDate] gets its original value(s)? I expect the problem is tied up with that somehow. Be careful of the formatting too. That can be particularly confusing when dealing with Date/Times.

[lee123]

well to sum it up i enter in the date manually as i said in my first post

I have a field on my form named "Tdate" actually it's in a subform on a form and right now i have to enter in a certain date and the subform is set to continuous form

but i just wanted it to enter in itself when i move to the next record. And in the table it is set to "Date/time" in the "Date Type"

lee123

[NeoPa]

OK Lee. Can you do two things for me.

1. Get (& post) the Format property of the [tDate] control of your form.
2. Add a line after line #2 of the code in your post #3 :
   

   Call MsgBox(Me.tDate, "dd/mm/yyyy hh:nn:ss")

   When the message box pops up make a note of it and post it in here.

I'll see if I can work out what is causing the strange behaviour.

[lee123]

Ok the Format Property is set to "Short Date" and the Error it keeps bringing up (not the msgbox) is a "Runtime Error 13 Type Mismatch" and it is pointing to this code.

Call MsgBox(Me.tDate, "dd/mm/yyyy hh:nn:ss") 

as you asked i have put the code like this now:

Private Sub TDate_AfterUpdate()
    TDate.DefaultValue = TDate + 1
    Call MsgBox(Me!TDate, "dd/mm/yyyy hh:nn:ss")
End Sub

is this the information you wanted or did i misunderstood your directions...

lee123

[NeoPa]

It seems this thread is where I come to make all my mistakes Lee. I'm so sorry.

Call MsgBox(Me!TDate, "dd/mm/yyyy hh:nn:ss")

should be :

Call MsgBox(Format(Me.TDate, "d mmm yyyy HH:nn:ss"))

I'd left out the Format function entirely. I generally try not to be that careless.

Also, as you're from the USA I thought I'd format it in a way more recognisable for you too.

[lee123]

ok it works and the msgbox comes up i have entered in the tdate "01/15/09" and the msgbox reads "15 Jan 2009 00:00:00"

the code i have entered in now is..

Private Sub TDate_AfterUpdate()
    TDate.DefaultValue = TDate + 1
    Call MsgBox(Format(Me.TDate, "d mmm yyyy HH:nn:ss"))
End Sub

so now what do i do..

lee123

[NeoPa]

Frankly, I'm struggling to see where post #11 is coming from. This seems to be working exactly as I'd intended. Try the following and we will see if anything shows there :

Private Sub TDate_AfterUpdate()
    With Me.TDate
        .DefaultValue = .Value + 1
        Call MsgBox(Format(.Value, "d mmm yyyy HH:nn:ss") & vbCrLf & _
                    Format(.DefaultValue, "d mmm yyyy HH:nn:ss"))
    End With
End Sub

[lee123]

Well Neopa,

This works fine the msgbox pops up with the two dates so when i enter in a date it shows the date (I entered) and the next date this is what i want it to do.

lee123

[NeoPa]

That's good to hear Lee, although I'm not sure you quite have the hang of this new feature. The idea I think is to click on the post in the thread that, in your opinion, most closely answers your question.

This is then showed (repeated) at the top of the thread, near the Original Post, so anyone searching can find the answer easily and simply.

[lee123]

i don't get what is going on or the hang of this. i still havent found a way to get the next date into the tdate field after i have entered in a date. i didn't want it to pop up in a msgbox, i wanted to know how i could enter in a date in the tdate field and then instead of me entering in the next date have it already there. so i could just enter in the other fields i have on my form.

so when i answered this:

This works fine the msgbox pops up with the two dates so when i enter in a date it shows the date (I entered) and the next date this is what i want it to do

.
thats it.. i guess i shouldn't of answered the way i did on that post, i was just telling you that the way you did the code worked fine and thats the way i wanted it to be entered in the tdate textbox (Not in a msgbox) when i entered in a date in the tdate field..things have changed on this fourm.

lee123

[NeoPa]

I have to say I cannot see why this is not working for you. The MsgBox code was to tell us that the control contains valid data first of all, and that the next entry will also default to the value you require. As far as I can see the results confirm this, yet you tell me this is not happening :S I can only guess that there is something else quite important that hasn't been posted yet. As this is likely to be hard to find via this interface (Hands-up those who are not here), I suggest it may be time to post a copy of your database. I'll include some instructions below.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If you've done anything in steps 1 or 2 then make sure that the problem you're experiencing is still evident in the updated version.
4. Compact the database.
5. Compress the database into a ZIP file.
6. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It would also be helpful, as we now understand that the issue is not resolved, to post exactly what was contained in the MsgBox.

[lee123]

hello Neopa, here is the database for you can look at it and as i said in post #21

This works fine the msgbox pops up with the two dates so when i enter in a date it shows the date (I entered) and the next date this is what i want it to do.

the msgbox works but i do understand how i can get it to be in the Tdate textbox.

take a look at the database it is a access 2000 version

lee123

[NeoPa]

I'll look at this Lee, but when I said I wanted to see exactly what was displayed I certainly wasn't after a repeat of the general description of the contents, but the actual values themselves. That way I can repeat the experiment exactly, and not get caught out if the problem is somehow related to the value you chose to test with (rather than the fundamentals of the database).

[NeoPa]

Try changing line #3 of post #20 to :

.DefaultValue = Format(.Value + 1, "\#m/d/yyyy\#")

The .DefaultValue property requires a string in the format of a SQL literal.

[NeoPa]

Actually, as normally, when the default value is already correct, the operator will not update the control, the .DefaultValue property won't be reset in normal usage.

In view of this I suggest a Form_AfterUpdate() event procedure of :

Private Sub Form_AfterUpdate()
    With Me.TDate
        .DefaultValue = Format(.Value + 1, "\#m/d/yyyy\#")
    End With
End Sub

This should be in place of the TDate_AfterUpdate() event procedure.

[NeoPa]

I did some testing and (with the help of MSquared) found that the Form_AfterUpdate event was not the best one to use as it would set the .DefaultValue wrongly when any previous records were edited. Instead I would use Form_AfterInsert.

Private Sub Form_AfterInsert()
    With Me.TDate
        .DefaultValue = Format(.Value + 1, "\#m/d/yyyy\#")
    End With
End Sub

Try this and let us know how you get on.

[lee123]

Hey Neopa,

first of all i have to say I'm Sorry for post #23 I didn't understand what was going on. i understand you guys don't have to help but you do and i know this is a free site for people to learn from and for this i'm sorry for not understanding.. and thank you for your help with this database it works great now thanks....

lee123

[NeoPa]

That's great news Lee.

BTW. It wasn't straightforward and I had to learn it myself properly before I could help, so I'm grateful for the opportunity :) That's two fundamental things I've learned about in this thread. That seems like a good yield to me ;)