Solved

Allow only certain dates to be entered in MS Access form

Posted on 2013-01-26
10
980 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 48

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
 
LVL 48

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 48

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

752 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