Auto populate a Check Box.

[enohs]

Hi All, I am new to this forum nut am assured that my queries will be satisfied.

Here we go.

I have a form with an [Enddate] field, a [Today] field and a Check box.
The [Today] field has a control source of =Format(Date(),"dd mmm yyyy") to display the system date eveytime the form is opened.

I have a check box that I want auto populating whenever the [Enddate] field is equal to the [Today] field.

A scenario.

A person enrols on a 3 year course. The [Enddate] field will be user populated with the course end date. In 3 years time the [Endate] and [Today] fields will match; it is then that I want the check box to auto populate. If this is not posible could I trigger another even such as calling another form to inform the user the the course end date has been reached.

I hope I have explained myself well enough.

Thanks for your help.

 

[vbaInet]

On the form's Current event:

IIF(CDate([EndDate]) = Date, checkBoxName.value = 1, checkBoxName.value = 0)

Change the name's in red.

 

[enohs]

Cheers for that, but it did not work. It returns the following error.

The object doesn't contain the automation objects 'Date.'.

I have changed the expression to the following but still no joy.

IIF(CDate([EndDate]) = Today, Expire.value = 1, Expire.value = 0)

I am in the dark and at the mercy of your patience with me.

 

[vbaInet]

[COLOR=Black]
[/COLOR][COLOR=Black]IIF([/COLOR][COLOR=Black]Format([EndDate],[/COLOR][COLOR=Black]"dd mmm yyyy") = [/COLOR][COLOR=Black]Format(Now,"dd mmm yyyy"), [/COLOR][COLOR=Black]Expire.value = 1, [/COLOR][COLOR=Black]Expire.value = 0)[/COLOR]

Try the above.

 

[enohs]

No good.


I tried changing =Format(Now, to =Format([Today], but still nothing.

I am baffled.

 

[Galaxiom]

Firstly, terminology. Your form has a textbox Control not a Field. Fields are in tables and queries. Controls are on forms and reports.

Don't display the date as text. Just set the textbox format property to Medium Date for the same result with the data still held as a date.

Possible normalisation alert. You should not store that checkbox value in a table. Calculate it on the form or query as required.

You will want Date >= EndDate because equals will only trigger on the exact date match.

You could use a message box.

On the forms On Current Event

If EndDate >= Date() Then
MsgBox "End Date Reached"
End If

Or as one line
If EndDate >= Date() Then: MsgBox "End Date Reached"

Sometimes using the reverse logic with Not looks better:
If Not EndDate < Date() Then

If you use the checkbox technique note that True is -1 rather than 1 but IMO it is better to use the values True or False than the numbers.

However if you use the checkbox, rather than using the On Currrent event:

In the Control Source of the checkbox:
=IIF(EndDate < Date(), False, True)

 

[vbaInet]

Zip, upload and post you db and I'll have a look. Tell me what form I should open as well.

GalaxiomAtHome: I forgot it was -1, good input. The greater than date is better.

 

[gemma-the-husky]

if, when you enter the expression date, it is not turning into date() with the brackets, then you may have a field just called date - which is interfering.

date() is a reserved function in vba that returns the system date - but if you call a field by that name, although it isnt an error, it can cause ambiguity of this sort.

 

[enohs]

Zip, upload and post you db and I'll have a look. Tell me what form I should open as well.

GalaxiomAtHome: I forgot it was -1, good input. The greater than date is better.

Thanks vbaInet, I have created a test Db with the fields on it. I can't send the orriginal due to Data Protection. I have set the [Enddate] as a user input and the [Today] as auto populate.

I really appreciate the time youo are putting into this. I was told that the forum users are helpfull; they were correct.

Cheers.

 

[enohs]

To all who are interested and have given their input, I have uploaded a version of the database in the reply above if you wish to engage your skills.

Cheers all.

 

[vbaInet]

Your database could do with some normalization. You could look that up for more info. I have attached your db. I noticed you implemented a message box after the date was checked but wasn't setting the value of your check box (as advised).

 

[CoachPhil]

would that work for dollar values if the choice was say True = 12.50 and False = 0.00?

 

[vbaInet]

would that work for dollar values if the choice was say True = 12.50 and False = 0.00?

I don't recall what I did in the attachment or how it relates to what you're after. Explain what you're trying to do?