Solved

how to use date field as check box?

Posted on 2014-01-09
14
696 Views
Last Modified: 2014-01-14
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 = ""
    Else
        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,
Thanks.
0
Comment
Question by:bfuchs
  • 4
  • 4
  • 3
  • +2
14 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39769255
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
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39769534
Hi fyed,

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

SELECT * FROM MyTable WHERE (ID = ?)

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]) ?
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39770177
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
Else
    Me.yourcheckbox  = False
End If

Open in new window

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

Open in new window

0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39770385
>  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

/gustav
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39771039
gustav,
Why would you choose to use the keypress and mousedown events rather than before or after update?
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 39771079
I wouldn't, except if there was a request for the accompanying beep.
And the questioner asked ...

/gustav
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 39771109
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.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 34

Expert Comment

by:PatHartman
ID: 39771130
bfuchs,
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.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39775497
hi valued experts,
I am sorry for not being able to reply in the last two days due to personal matters.

@PatHartman,
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..

@Gustav,
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.

@PatHartman
Thanks for that additional useful info.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39778512
Hi,

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

Cheers,

Christian
0
 
LVL 3

Author Comment

by:bfuchs
ID: 39780566
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.

Thanks
Ben
Doc1.doc
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 490 total points
ID: 39780822
Hi,

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.

Cheers,

Christian
0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 39781183
That "!" worked, Thank you!

(I wish there were more  Bitsqueezer's around here...)
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39781418
Thanks, you're welcome...:-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now