Event Procedures on Forms

[Al]

Hoping that someone can help with the following few queries...

1) I have the following procedure, which is used to unhide a field if a
tickbox is ticked. This works fine on the existing 'record'. However when I
navigate to other records it still remembers the tick box option of the
previous record? Any ideas?

Private Sub ContractNoExpiration_AfterUpdate()
If ContractNoExpiration = True Then
ContractNoExpirationNotes.Visible = True
Else
ContractNoExpirationNotes.Visible = False
End If
End Sub

2) I have the following procedure for when the Status is Changed to Closed.
Basically this will then set the Date Closed field to be current date/time.
However, I now need to be a bit more flexible with this so what I'd like to
do is add on an extra field within the status table (tblStatus) which I've
named bitStatusClosed. This is either set to 0 (open) or 1 (closed). Would
it be possible to amend this query to actually look @ the attribute of the
status and if it's = 1 then set the closed date?

Private Sub Status_Change()
If Status = "Closed" Then
DateClosed = Now()
Else
DateClosed = Null
End If
End Sub

Thanks in advance for any help. Really appreciated. Al.

 

[Rick Brandt]

Hoping that someone can help with the following few queries...

1) I have the following procedure, which is used to unhide a field
if a tickbox is ticked. This works fine on the existing 'record'.
However when I navigate to other records it still remembers the tick
box option of the previous record? Any ideas?

You have to repeat the code (or call it) in the Current event of the form.
That fires as you arrive at a different record.

 

[Scott Lichtenberg]

Al,

Add the same code as you show below to the OnCurrent event of the form.
This event fires off when the user enters the record. This will allow you
to show or hide the fields based on the value of the check box. You can
even call the AfterUpdate subroutine for the check box rather than copying
the code. Just add the following to your OnCurrent event:
ContractNoExpiration_AfterUpdate.

One thing to look at is whether your check box is bound or unbound. If it
is bound to a field in your form's recordsource, it will change depending on
its value in the record. If it's not bound, it will continue to show
whatever value it has been set to, regardless of what data is in your
record.

Scott

 

[Al]

Hi,

Thanks for your help with this. I'd tried this before but it seems to hang
for ages when going into a new record (status bar reflects 'calculating' so
I'd assumed that this was what was causing the delay - even though it's not
doing anything complicated!). I'll try again with this as the users will
still be able to work with it.

Have you got any suggestions on how I can achieve the status option as below?

Thanks again, Al.

 

[Scott Lichtenberg]

Hi Al,

1. If you are seeing a delay moving into a record, then you have something
else going on. The code to show/hide the fields should be virtually
instantaneous and it is not the cause of your problem. Do you have any
functions or Dlookups in either the recordsource (if it is a query or SQL
statement) or in any of the field control sources. Sometimes this can cause
the delay as Access has to go out to get the data.

2. Your code for the status change is OK. Check whether the completion
date in your table allows null (it should), and whether you put any
validation code on the field in the form. One thing you might want to look
at is putting some code in the BeforeUpdate event of the form. With what
you have now, a user can check the complete box which would set/clear the
completion date, then go back and fill in or remove the completion date.
You should check this in the BeforeUpdate event of the form to make sure
that your fields are consistent.

Scott