Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Allow only certain dates to be entered in MS Access form

Posted on 2013-01-26
10
Medium Priority
?
1,106 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 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
LVL 48

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 48

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 48

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

670 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