Date of when Yes/No control has been checked (1 Viewer)

[skilche1]

Hi, I've been working on a db for my department. I have completed it but when I demonstrated it to the higher ups (which was a big success), I got a request to indicate a date when a certain field has been changed. This field is a Yes/No control, which indicated when the tool was obsoleted and will no longer need calibration. Thing is, I can do this if I want to add more tables and change the relationships. That to me, seems to be to much work. So, what I'd like to do is know how I can do this with an existing table where when the Yes/No box has been checked, the date (Now()) is added into the field in the same table where Yes/No control is. Hope this makes sense...

Any ideas?

Thank you.

 

[RainLover]

Create a new Field in the Table where the Yes/No is. Make it a Date/Time Data Type.

Put this field on the form.

Then in the Before Update event of the Check Box place the Date and Time in the new Field using

    Me.NameOfNewControl = Now()

 

[ypma]

How about adding a new filed named dateChanged I am assuming you are using a form . This new field would be bound to the same table as the Yes/No tick box and when the tick box is ticked the [Event Procedure] afterupdate will enterr the now ()date into the new field , this would be overwritten each time you click a new tick.

Private Sub yesno_AfterUpdate()
Me.DateChanged = Now()
End Sub

I Hope i have understood your requiement.

 

[skilche1]

Thank you RainLover and YPMA, I will try both when I have a moment. I'll give you my input when I give it a go.

Much appreciated.

 

[skilche1]

YPMA, I jsut wanted to let you know that your method works like a charm.

Thanks again for your help.

 

[RainLover]

Now that you have done that you should consider an If Statement to delete the Date if the other field is changed back or re clicked by mistake.

At the moment every time an entry is made the current Date/Time is inserted. This may not be what you want. You might want to keep the original Date no matter what.

There could be various possible scenarios.

 

[skilche1]

Now that you have done that you should consider an If Statement to delete the Date if the other field is changed back or re clicked by mistake.

At the moment every time an entry is made the current Date/Time is inserted. This may not be what you want. You might want to keep the original Date no matter what.

There could be various possible scenarios.

Yes, I agree and was thinking of that yesterday. I'm not a coder so I amnot sure how that is written... lol

 

[RainLover]

It is easy but I need some info.

First the Name of the two Controls. The Check Box and the Date/Time.

Next, what do you want to happen when.

So write something in a reply like.

If Check Box clicked add Date Time

But If Date/Time is already then do nothing.

Just give it some thought then post back. Be sure to cover all situations.

 

[skilche1]

It is easy but I need some info.

First the Name of the two Controls. The Check Box and the Date/Time.

Next, what do you want to happen when.

So write something in a reply like.

If Check Box clicked add Date Time

But If Date/Time is already then do nothing.

Just give it some thought then post back. Be sure to cover all situations.

The forms I'm looking into doing this is from the Main SB, to the "Add Calibrated Tool" and "Assign User/Update Cal#/Add Comment" fields. Also in addition, in another post I posted this AM, I am having an issue where I lost the (Yes (no Duplicates) for the Cal# in the tbl_ToolInfo table. If there is a way to get that back where duplicates are not allowed (I'm auuming this would have to be done with coding of some sort... It was set originall, but lost somehoe. And no, I didn't make this field as Primary Key when I should have.... duh... lol

Thanks again...

 

[RainLover]

I don't have Access 2007 or 2010 loaded on this machine. I do have it on another which is in my study.

I can either look at it tomorrow AM ( 10:30 PM here just now) or you could convert your copy to A 2003.

The other option is for someone else to jump in who has your version.

Which option do you want to go with.

 

[RainLover]

OK

I got it and opened it. I can't find any code to do with adding a Date/Time when the other control is updated.

Can you help me to help you.

Also you did not explain what you wanted to happen when as I asked in post #8

 

[skilche1]

RL,

If you go into the "Add Calibrated Tool" and "Assign User/Update Cal#/Add Comment" Forms, under the "After Update" for the Yes/No Field, you will this code:

Private Sub Obsolete_AfterUpdate()
Me.ObsoleteDate = Now()
End Sub

 

[RainLover]

Did you have to remove code to convert to 2003. From your Menu not one of the buttons work.

Best if I look at the full version tomorrow. Besides you still havn't answered my other question as yet.

Next time you post an attachment you should do a quick test to make sure people can do what you say they should do. Things always go wrong at the worst possible times.

 

[skilche1]

Did you have to remove code to convert to 2003. From your Menu not one of the buttons work.

Best if I look at the full version tomorrow. Besides you still havn't answered my other question as yet.

Next time you post an attachment you should do a quick test to make sure people can do what you say they should do. Things always go wrong at the worst possible times.

Really, I just tried it and worked for me. Buttons as well as the date addition function. Do you have your macro set to "on"? I kow when when I didn't enable marcos, that has hapened tome as well.

 

[skilche1]

Sorry RL, I didn't follow your question in post #8. Maybe I need more coffee or something. lol Can you expand on that?

Thanks

 

[RainLover]

It is in frm_ToolAddition.

Did you tell me that earlier?

Anyway doesn't matter.

All you have to tell me now is what do you want to happen when the box is checked or unchecked. Think about mistakes by users.

I off for the night. Must get my beauty sleep.

 

[skilche1]

It is in frm_ToolAddition.

Did you tell me that earlier?

Anyway doesn't matter.

All you have to tell me now is what do you want to happen when the box is checked or unchecked. Think about mistakes by users.

I off for the night. Must get my beauty sleep.

OK, I understandnow. I had posted a tanks to YPMA for his help and getting the date to fill in when the Yes/No box was checked. You then suggested that I have it remove the date from the field in the even the user made the wrong decision on that particular tool. I had said that I wouild like that feature so the the date can be removed when the Yes/No box is unchecked, but I do not know how to code it.

Thanks for your help.

 

[Beetle]

I am having an issue where I lost the (Yes (no Duplicates) for the Cal# in the tbl_ToolInfo table. If there is a way to get that back where duplicates are not allowed (I'm auuming this would have to be done with coding of some sort

The cause of the issue is the fact that you have duplicate values in the Cal field in the table. You can't enforce No Duplicates when you already have duplicates in the table. Use the query wizard to create a Find Duplicates query based on the Cal field in tbl_ToolInfo. This will show you the duplicate numbers, then you can delete the extra rows from the table. Once that is done, open the table in design view and change the Index for the Cal field to Yes (No Duplicates).

I wouild like that feature so the the date can be removed when the Yes/No box is unchecked, but I do not know how to code it.

Modify the code as follows;

If Me.Obsolete = True Then
    Me.ObsoleteDate = Now()
Else
    Me.ObsoleteDate = Null
End If

A couple more tips:

You should avoid using special characters (like / or #, etc.) in your object naming (tables, fields, etc.) as they can cause problems when writing queries/code.

You should also avoid the use of lookup fields in your tables if possible. More on that here.

 

[skilche1]

A couple more tips:

You should avoid using special characters (like / or #, etc.) in your object naming (tables, fields, etc.) as they can cause problems when writing queries/code.

You should also avoid the use of lookup fields in your tables if possible. More on that here.

Thank you Beetle and you're right, never thought of that. Good idea. I'll make sure to change that in the event I build upon what I already have.

Thanks again,
Steve

 

[ypma]

As Rainlover has pointed you need to decide what senrio you require .
If you wish to carry on with the one you have and not have the date over writen you could us the following:

Private Sub yesno_AfterUpdate()
If IsNull(datechanged) Then
Me.datechanged = Now()
End If
End Sub

which will only enter a date if the field is blank