Solved

Allow only certain dates to be entered in MS Access form

Posted on 2013-01-26
10
851 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
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…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

920 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

16 Experts available now in Live!

Get 1:1 Help Now