date restrictions on all forms (1 Viewer)

eka24

Registered User.
Local time
Today, 04:50
Joined
Oct 2, 2017
Messages
41
Please I have a textbox named TxtCloseDate on the form frmCloseDate and this is what I want to achieve :
when I enter a particular date in the TxtCloseDate example 31/12/2017 on datefield , none of the forms in the database should accept a date before 31/12/2017. when I change the date in TxtCloseDate to 31/12/2018 all datefield should disallow date before 31/12/2018.

can anyone help with a function or code to achieve this.

thanks in advance
 

JHB

Have been here a while
Local time
Today, 12:50
Joined
Jun 17, 2012
Messages
7,732
One way to do it is, store the date from TxtCloseDate in a table and each time a date is typed in, compare it to the date stored, use the BeforeUpDate event in a control.
 

eka24

Registered User.
Local time
Today, 04:50
Joined
Oct 2, 2017
Messages
41
please can you help me with the code to achieve that
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:50
Joined
May 7, 2009
Messages
19,175
Create a table with that field, dateClosed.
So tou have to modify every form that has transaction date.
Add code to each textbx beforeUpdate event

If txtdate< dlookup("cliseDate", "closedatetable") then
Msgbox "date prior to clising date is not allowed"
Cancel= True
 

Wayne

Crazy Canuck
Local time
Today, 07:50
Joined
Nov 4, 2012
Messages
176
You said that the field TxtCloseDate is in a form. Please note that Arnel's solution is to look up that date in the underlying table, and not the form. You can only look up a field in a form that is currently open.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:50
Joined
May 7, 2009
Messages
19,175
if you are unable to do it
in form, there is still luck
for you. but first you must
create the table i told you
about (tblClosingDate) with
ClosedDate field (Date/Short Date).

add Data Macro (A2007-2016) to those
tables with date fields you
want to Validate.

attached is a zipped pdf with
simple instruction.

if you have done that you
do not need to add Any code
your db. just make sure you
update tblClosingDate.
 

Attachments

  • beforeChange.zip
    42.3 KB · Views: 53

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:50
Joined
Feb 28, 2001
Messages
27,001
Since your problem is on multiple forms, you will have to repeat the solution for each form where that problem applies. So get it "right" for one form and then you will know how to do it for multiple forms.

Since the value in question is entered on a form but affects other forms, and I'm going to presume that this solution should be "persistent" - that is, applies even after you have closed and re-open the database in question, you need to actually store the closure date. I don't know your structure so cannot advise on WHERE to store it except that the only place to persistently store something is a table.

Therefore, on the form where you enter that limiting date, you must find a way to store the date in a table. I don't know that it matters if there is anything else in the table and I don't know what other requirements you might have. The SIMPLEST case is a one-record, one-field table - but that might be wasteful and you might already have another, more convenient place to store such a thing. We don't know, because your description of your problem was strictly limited to your desired effect, not your surrounding database.

In general, Arnel's proposed solution is right for the limited description you provided but he only superficially covered storing the date to achieve persistence across restarts.

In general, when you want us to write code for something, it helps if we know more about your database setup or environment. Having said that, the theory behind what Arnel proposed is this:

If you have a persistent copy of the closure date that you want applied, you can use the DLookup() function to retrieve the date. Having the date, you can make comparisons against it, such as on the forms you want to protect. When you are about to save the contents of a form, something called the "BeforeUpdate" event fires and if you can put this code in place to PREVENT the update (using the Cancel parameter that is part of that event), you can stop the form and issue a warning to the user. The user would not be able to save the data as long as the closure date on the form is wrong. In fact, this is the place that you would put ANY validation code for a given field on the form if you were testing for special constraints such as this.
 

eka24

Registered User.
Local time
Today, 04:50
Joined
Oct 2, 2017
Messages
41
thanks Arnelgp and all others. Reference the the table works fine with me. I will however try the macro and give you reply. am grateful
 

eka24

Registered User.
Local time
Today, 04:50
Joined
Oct 2, 2017
Messages
41
Reference to the table woks fine with me. i will however try the macro as well and get back to you.
Thanks Arnelgp and all who contributed
 

eka24

Registered User.
Local time
Today, 04:50
Joined
Oct 2, 2017
Messages
41
Please on the attached pdf, what is not clear to me is "dteClosed" what is it referring to? I am clear with all others
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:50
Joined
May 7, 2009
Messages
19,175
It is a local variable, local to the macro
 

Users who are viewing this thread

Top Bottom