Solved

Allow only certain dates to be entered in MS Access form

Posted on 2013-01-26
10
823 Views
Last Modified: 2014-10-06
I need to only allow the user to select certain dates for data entry for production control.  No weekends and no dates that are already full.  I can generate either a list of allowed or a list of disallowed dates and I can set up a combo box fine.

The users would rather see a date picker or similar form with the invalid dates grayed out.

Any ideas?

Thanks,

Bill
0
Comment
Question by:Bill Ross
10 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38822558
You'll need to write VBA code in the date text box's BeforeUpdate event that evaluates the user-entered date based on your multiple criteria.

If it doesn't meet your criteria, you can clear the value out and set Cancel = True, which cancels the update.
0
 
LVL 14

Author Comment

by:Bill Ross
ID: 38822567
Hi Jim,

Thanks for that reply but I can already get that with the combo box, limit to list and I could write the code to let the user know they have picked an incorrect date.  The issue is that the user would then need to guess correct dates.  

I.e. in the combo box the dates 1/29/2013 and 1/30/2013 are the only available dates next week.  I want to show that to the user in the date picker or another graphical view without them needing to guess (like outlook task display for example).

Thanks,

Bill
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38822573
I would suggest sticking with a standard date picker and Jim's method for ruling out invalid dates:

<<
see a date picker or similar form with the invalid dates grayed out.
>>

Otherwise, you would have to develop a custom form, possibly using small textboxes formatted to look like the dates, with text box properties for any invalid dates set to enabled = false.  

The code to do this (and to simply populate the dates in the form) would be quite involved.

You might be able to find 3rd party solutions that do this, but otherwise it is a ton of effort for little gain.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38822580
You will not be able to do that with the standard Access date picker, as there is no way to grey-out invalid dates.  I have seen a couple of custom date pickers mentioned here in EE, but have never used any of them.  I created my own back in A2000, but you would have to write some code to modify my calendar form to disable the dates that are not available.

Take a look and see whether you can make it work for your purposes.
Calendar.mdb
0
 
LVL 14

Author Comment

by:Bill Ross
ID: 38822614
Hello fyed,

Your Calendar.mdb This looks promising.  Need to figure out a way to Disable combo boxes based on a list of dates when a month is selected.

Looks like the Caption of the various boxes changes so maybe we can test the date there...  Can you point me in the right direction with the code?

Thanks,

Bill
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 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38822738
I added two subroutines (DisableDates and DisableWeekends) and added some records to tbl_Dates to test.  This is a table of invalid dates, you could change the code and use a query for your dates, but this should give you an idea).Calendar.mdbIf the query you are using for the combo identifies valid dates, then you would need to change the logic in the FindFirst section of the code so that if the date is found, it enables the control instead of disabling it.
0
 
LVL 14

Author Comment

by:Bill Ross
ID: 38822874
I've requested that this question be closed as follows:

Accepted answer: 0 points for BillDenver's comment #a38822614

for the following reason:

Very cool. &nbsp;I can get there from here. &nbsp;Thanks! &nbsp;Bill
0
 
LVL 14

Author Closing Comment

by:Bill Ross
ID: 38822875
Very cool.  I can get there from here.  Thanks,

Bill
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38822933
Glad I could help.

I had been thinking about adding some code to block out periods of time, (Before some date, After some date, but had not gotten around to it.  Guess I need to put that back on my list of things to do.
0
 

Expert Comment

by:mdhaase
ID: 40363954
Thanks for all your help!  
I am not so good at VB and this was the only post anywhere on he internet, that I could find to help me out with blocking all days/dates on every month except for Fridays (when the weekly report is due for all employees.)  

I just changed the > 5, to <>5 and it works perfectly!

Thanks again!

Now I just have to figure out having the date selected to be passed to the actual form for submitting the data into the table.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

744 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