Automatic date Insertion

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

re: Automatic date Insertion

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

re: Automatic date Insertion

So i put it like this:

lee123

re: Automatic date Insertion

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

re: Automatic date Insertion

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

re: Automatic date Insertion

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?

re: Automatic date Insertion

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

re: Automatic date Insertion

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.

re: Automatic date Insertion

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

lee123

re: Automatic date Insertion

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.

re: Automatic date Insertion

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

lee123

re: Automatic date Insertion

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 ;)

re: Automatic date Insertion

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

lee123

re: Automatic date Insertion

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.

re: Automatic date Insertion

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

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

re: Automatic date Insertion

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 :
   
   Expand|Select|Wrap|Line Numbers

   1. 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.

re: Automatic date Insertion

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.

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

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

lee123

re: Automatic date Insertion

It seems this thread is where I come to make all my mistakes Lee. I'm so sorry.
should be :
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.

re: Automatic date Insertion

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..

so now what do i do..

lee123

re: Automatic date Insertion

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 :

re: Automatic date Insertion

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

re: Automatic date Insertion

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.

re: Automatic date Insertion

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:

.
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

re: Automatic date Insertion

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.

re: Automatic date Insertion

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

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

Attached Files

[Alsco's] Stockrooms Daily Analysis.zip (100.3 KB, 2 views)

re: Automatic date Insertion

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).

re: Automatic date Insertion

Try changing line #3 of post #20 to :
The .DefaultValue property requires a string in the format of a SQL literal.

re: Automatic date Insertion

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 :
This should be in place of the TDate_AfterUpdate() event procedure.

re: Automatic date Insertion

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.
Try this and let us know how you get on.

re: Automatic date Insertion

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

re: Automatic date Insertion

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 ;)