Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Allow only certain dates to be entered in MS Access form

Posted on 2013-01-26
10
Medium Priority
?
1,172 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 66

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Expert Comment

by:Dale Fye
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 49

Accepted Solution

by:
Dale Fye earned 2000 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 49

Expert Comment

by:Dale Fye
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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

916 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