how to use date field as check box?

Hi all,

I am trying to avoid creating unnecessary fields and controls in both, my table as well as in my form, therefore when i get a request to create a check box YesNo field and a date, to know the date that check box was checked, i would prefer only to create the date field in the table, while on the form display only the check boxes (as the users don't necessarily need to view the date value).

i have tried with the below code on the on click event of the check box:

    If IsDate(Nz(Me.CheckControl)) Then
        Me.CheckControl = ""
        Me.CheckControl = DATE
    End If

however it doesn't work as next time the code runs, it will always have a date on it, something like '12/29/1899'

Would appreciate if someone has a neat solution for it,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
To do this, you will need to include the [DateChecked] field in the forms recordsource, but should not need to put it on the form.  Then add the unbound checkbox and in the checkboxes Click event, to change the value in the [DateChecked] field.  Something like:

Private Sub chk_Something_Click

    Me.[DateChecked] = IIf(Me.chk_Something, Date(), Null)

End Sub

BTW, you would also need to put code in the Form_Current event to set the value of that checkbox as the user moves between records:

Private Sub Form_Current

    me.chk_Something = NOT isnull(me.[DateChecked])

End Sub
bfuchsAuthor Commented:
Hi fyed,

this will not work in my scenario as i have an adp and the recordsource of the form is


therefore when i try compile it does not recognize the field names...

please let me know if you have another suggestion.

btw, in what you're suggesting, why would you have the control source blank and always assign values by code, instead of just having it as =NOT isnull(me.[DateChecked]) ?
You didn't tell us your field names so Dale made some up.   You need to modify the code to use your field names.

You need code in two events -
FORM - Current
If IsDate(Me.yourdate) Then
    Me.yourcheckbox = true
    Me.yourcheckbox  = False
End If

Open in new window

yourcheckbox CONTROL - AfterUpdate
If Me.yourcheckbox = true Then
    Me.yourdate = Date()
    Me.yourdate = null
End If

Open in new window

Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Gustav BrockCIOCommented:
>  why would you have the control source blank and always assign values by code,
> instead of just having it as =NOT isnull(me.[DateChecked]) ?

Because then you can't change the checkbox the normal way.
However, it can be done.

Use the ControlSource:

=Not IsNull(Me!DateChecked)

and this code:

Private Sub chkCheckDate_KeyPress(KeyAscii As Integer)
    Me!DateChecked = IIf(IsNull(Me!DateChecked), Date, Null)
End Sub

Private Sub chkCheckDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me!DateChecked = IIf(IsNull(Me!DateChecked), Date, Null)
End Sub

Why would you choose to use the keypress and mousedown events rather than before or after update?
Gustav BrockCIOCommented:
I wouldn't, except if there was a request for the accompanying beep.
And the questioner asked ...

I didn't think you would:)  I still don't see the request for the beep but I'm not going to read the thread again.
In answer to an earlier comment-
however it doesn't work as next time the code runs, it will always have a date on it, something like '12/29/1899'

Dates are stored internally as double precision numbers.  The integer portion represents the number of days since Dec 30, 1899 and the decimal is the time since midnight.  Negative numbers are dates prior to the origin date.  So -1 would be 12/29/1899 and +1 would be 1/1/1900.  Today (Jan 10, 2014) is 41649.
bfuchsAuthor Commented:
hi valued experts,
I am sorry for not being able to reply in the last two days due to personal matters.

You didn't tell us your field names so Dale made some up.   You need to modify the code to use your field names.
What I meant is that when the form's record source is depended on users input of a parameter, like the example above, it will not automatically recognize field names of the underlying source. the only thing it recognizes is control names, which is exactly what I am trying to avoid..

My question to Dale was as following, since i am anyways not going to update the field in the normal way, only by code as you/he suggesting, so i may as well have that control source saved within the properties, thereby avoiding the on current event procedure.

Thanks for that additional useful info.

I've tested what you said in an ADP and I see no problem in accessing a field of the RecordSource if that used the syntax "SELECT * FROM ... WHERE ID = ?". When I entered that as a RecordSource IntelliSense showed me all the fields of the table/view and the debugger compiled that without any problem using a syntax like "Me.MyNumber=0" where "MyNumber" is a table field. Means: There should be no problem in using the codes of the others to achieve what you want. Maybe you should describe your way of handling the data in that form, how do you open it, how do you get or write the data to the database and so on.

But why do you use the form with such command as a RecordSource? If you open such form Access is asking for the parameter value in the common ugly input box where you have absolutely no control what the user enters.
If you want to open a specific record with the form you should always use an own method to get that value, validate it and if correct, open a form for a detail record. Normally you do not need to do much coding to achieve that as Access can do that alone by simply using the WHERE parameter of the DoCmd.OpenForm command. The form you open then only needs to be set to load the complete table/view, Access will do the filtering itself.

Next thing is that you should always avoid using the "*" to select columns. A form should always use a concrete list of columns to make sure that not more than what is needed is loaded from the database. If you've created a view on the server which already does this (and this view is only be used by this form) than it is enough to set the form to the name of this view or use SELECT * as the concrete list is in the view already. If the view is used by more than one requester (i.e. an additional report) then a concrete field list should also be used on the form/report as they may be different (or will maybe be different in future).


bfuchsAuthor Commented:
hi Bitsqueezer,
one thing i know, once you're here, sooner or later this issue will be resolved:)

about the the issue with compiler etc.. I am attaching screenshots in order to prove what i have stated above, that the highlighted field (BclsLetterDate) does exists in the underlying recordset and also available in design view on fieldlist option, however at the vba level it is not accessible and thereby causing compilation error.

Re what you're experiencing different, is it possible that this was changed in a later version of office, as i am still using office 2000..

this could also be due to the fact that the sql database is saved in a newer version than from the office, as we have it in sql server 2005..

regardless what the story is, i am stuck with that setup and have to deal with what we have..

the way we use that form in question is as following:
the form has input parameter = "ID int = Forms!MyForm.CntrlID"
we open it blank and there is a popup screen for users to select the record of interest, the selected id value its being saved in that cntrlID  and then we use me.requery.
this is a Single Form view.

If you need more info, please let me know.


I tested that with Access 2010 ADP (which is A2003 file format but ready to work up to SQL Server 2008) and SQL Server 2008 R2.

Office 2000 was the first version with ADP as far as I remember so it is very likely that this one has a lot of issues, but moreover you are using an older Access with a newer SQL Server version which is really bad as ADPs are clearly stated to be used only with older versions of SQL Server as the Access version is. So A2010/SQL Server 2008, OK, A2007 with SS2005, OK, A2003 with SS2000, OK, but A2000 with SS2005 - no go. You can be sure that sooner or later you will get into troubles. Access MDB with SQL Server 2005 is no problem (but has of course other issues which is why I don't want to use MDBs/ACCDBs never again with SQL Server, but that's another story).

You can see how Access is retrieving it's fieldlist if you start the SQL Server profiler and see what happens when you refresh the database window to see the new objects or if you open the fieldlist and access it and so on. There you can probably also see why your field does not occur in your field list, the profiler shows all errors, also that errors which Access does not forward to you.

But nevertheless you can work around that because the dot syntax is of course not the only way to access a field. You have the variants of using the "!" instead (where everything after that will be ignored by the compiler) - but also in this case it must be available at least at runtime which you can test. Then you can also use the "Me.Recordset.Fields" collection to access the field in the recordset directly. If anything fails, another method is to add the field simply as a hidden control so that you can access it through the hidden field (as it is available in the designer's field list it must be able to be bound to a control). In that case you would be able to access it. The user doesn't see that control and so it is what you want, making the input area easier to handle for the user and reduce the number of controls for the user (but not for the developer).

By the way: Did you try to use a concrete field list instead of the asterisk which Access must translate to a field list? Maybe that can also help in this case.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
That "!" worked, Thank you!

(I wish there were more  Bitsqueezer's around here...)
Thanks, you're welcome...:-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.