capture today's date

[strods]

hey everyone,

I'm a moderate user of Access but I'm trying to iron out a problem.
I have several Y/N boxes for various status I'm trying to track for each Y/N box I also have a date field inteded to capture today's date as the day the the Y/N box was last updated. The only problem is I do not know how to do this.

Your help would be appreciated.

 

[Banana]

Use Form's OnDirty event to trap whenever a user changes anything on it then insert this one liner:

Me.MyControlName = Date()

(Use Now() if you want time as well)

 

[strods]

thanks but I'm apparently missing something. Let me try to clarify my question.

This is a church database.
The main form is called "Family Detail".
This form has a subform called "Family Members".
Family Members has several fields but two of which are:
a Y/N box called "Converted".
and a date field called "Converted Date"

I'm trying to set it to checking the Y/N box enters today's date in the date field. I do see the code listed above but I do not know which field should have the code and specfically how to enter it.

P.S. I do need the seperate fields because there is a sizable proportion of the congregation that was saved before they kept good records so the check box will cover everyone and the Date field will be for going forward and those who remember.

Also I have several other Y/N boxes w/ date fields for this database so if I can just clearly understand how to set this up it will do a great deal for me.

 

[Moniker]

He's already told you how to do this. There's a problem with the rationale though. The second you check the checkbox, the date field will be filled. However, errors will happen, and someone will accidentally check and then uncheck the box. Now it looks like they were "saved" when they are still heathens.

What you want here is something that contains a "last edited" date and then you query on whether or not the checkbox is checked or not. Again, though, you'll run into issues in the sense that someone can accidentally check the "converted" box and not realize it for a few months, and then go back and change it. For those few months, although they were listed as "converted", they still listened to rock music and read comic books and played the MTV video games.

What I'm getting at is that your design is flawed. All you need is an extra field to contain an edit date. To do what you're trying to do, though, it's simple. If you don't want to use the form's OnDirty event, use the checkbox's AfterUpdate event.

YourCheckbox_AfterUpdate()

    If YourCheckbox Then
        YourDateField = Date()
    Else
        YourDateField = ""
    End If

End Sub

That will solve the immediate need, but it's not a long term solution. Rethink your structure. Otherwise, you're going to have headaches in the long run.

 

[pbaldy]

Now it looks like they were "saved" when they are still heathens.

That's just wrong.

 

[strods]

okay, I'm getting closer...
I've entered:
------------------------------------
Option Compare Database

Converted AfterUpdate()

If Converted Then
Conveted Date = Date
Else
Converted Date = ""
End If

End Sub

-------------------------------

But when I try to access the form I get:
"Compile error: Invalid outside procedure."

Many thanks for all this work... I know you all have better things to do than help me.

 

[strods]

Okay I think I found part of the problem. I've tried several times but the VB keeps deleting my (), so my entry of "Conveted Date = Date()", keeps getting saved as "Conveted Date = Date"...

Suggestions?

 

[Rabbie]

Its just one of Access's little quirks. You input Date() into the VBA editor and it shows it as DAte. Its OK and does what you want so need to worry.

I hope the code in your previous post doesnt have spaces in the names - that's a no-no

Use underline_ to fill in the gap.

 

[boblarson]

Well, in VBA, you don't use the () when using the keyword date. If you use it in a control source or query, THEN you use the () after it.

The same goes with Now.

Your problem lies in that you have an embedded space in your Converted Date:

It should be:

If Converted Then
Me![Converted Date] = Date
Else
Me![Converted Date] = Null
End If

(You can't set a date field to an empty string. You have to either give it a date or make it null.)

 

[Moniker]

Also, I'm not sure if you copy/pasted exactly what you have, but this would need to be there:

[COLOR="red"]Sub [/COLOR]Converted[COLOR="Red"]_[/COLOR]AfterUpdate()

    If Converted Then
        [Converted Date] = Date
    Else
        [Converted Date] = Null
    End If

End Sub

 

[strods]

Okay none of this is working for me. Is there someplace I can go for simpler instructions or talk to someone in detail. I would ask someone near me, but I'm the most fluent person in Access I know.

 

[pbaldy]

Post a sample db.

 

[strods]

I had to zip it because the forum wouldn't accept the mdb extention but here's a sample... if you could do it and I could see what the code is like and where you put it I'm sure a light bulb will go off for me so thanks.

 

[pbaldy]

Not sure why you're having a problem. I copied both Moniker and Bob's code in and they both worked fine.

 

[strods]

I was never doubting the code but I'm not understanding where or how to but that code in. I've tried about a half doezen variations and not found success. If you could copy me in on what ever you did or send me the copy with the adjustments that would be a blessing.

 

[pbaldy]

Here you go.

 

[strods]

It was the 'Me.'... I thought you were supposed to subsutite the table or forum name for that. I didn't realize you were supposed to use that specifically.

You have no idea how helpful that is to me now.

Thank you sooooooooooooooooooooooooooooooooooooooo much...

 

[paulsimo]

Having had a look at the Student Mgmt database, I figure this could help me out with my problem.
I want a form to have just 2 fields (ID Number & Rec Time).

In the Student Mgmt database the tick box, when checked inserts the current date into the Converted Date field.

What I would like is to have a text box that I can enter the ID Number and when I press enter for it to automatically insert the current time into the Rec Time field.

I tried adjusting the Student Mgmt database, removing the unwanted fields, adding a text box and altering the code in the after update section of Converted date. I managed to get it to insert the correct current time, but it filled all the ID Number records with the same information.

Could anybody please help me out with this dilemma, and possibly build the seemingly simple form for me.

 

[whatcloud]

Something missing in Access installation??

Hi,

First post, new to VB, and recently back into Access...

I'm trying something very similar to the above thread. I've been to several forums, they all recommend the same code, but for some reason none of this is working for me. No error messages, no reaction, no date entered in the form. Nada!!!

I'm running Access 2007, have I perhaps got some security problem or setting wrong? Any ideas.

I downloaded the 'student mgmt' DB zipped above, it doesn't work on my machine. Any ideas? I've tried using Command buttons, check boxes, the date field itself, and nothing works!!! Help!!!!

 

[pbaldy]

If no code is running, check the bit about trusted locations:

http://allenbrowne.com/Access2007.html#Configuration

You may also see something below the ribbon about code being disabled or something and a button to click on to enable it. Can't remember the exact wording offhand.