Which event to call a module on?

RCheesley

RCheesley

Registered User.
Local time
Today, 07:00
Joined
Aug 12, 2008
Messages
243
Hi all,

I wonder if you can suggest what event I should use to call a module on?

I've been using beforeupdate but this doesn't seem to be working as I intended.

Basically my module runs through a series of dates, and if those dates are within a certain number of days from today it'll change the back colour of another field.

I want it to run whenever the data is first shown (i.e. when the form is opened) but also when moving between records, to ensure the colour shown is appropriate to the current date.

Ruth
 
current event is almost certainly correct - this fires every time you navigate to a new recrd

you may also need it in the afterupdate event for a record (maybe the afterupdate event for a particular control) if changing the record means you need to run the procedure

not beforeupdate, although it probably doesnt matter, as the before update event could in theory be cancelled after you run the module
 
Thanks for that, I hadn't thought about afterupdate but will incorporate that also :)

Ruth
 
When I call it on form_current I get the error

Procedure declaration does not match description of event or procedure having the same name

The VBA is:

Private Sub Form_Current()
Call ColourFeatures
End Sub

and the module is as follows:

Code: 
Option Compare Database
Option Explicit

Public Function ColourFeatures()

Dim MyForm As Form
Set MyForm = Screen.ActiveForm

'This code changes the colour for the Initial Investigation box depending on deadline date
If MyForm.SUIDeadlineInitialInvestigation.Value < Now() And IsNull(MyForm.SUIDateInitialInvestigation.Value) Then
     MyForm.SUIDateInitialInvestigation.BackColor = vbRed
ElseIf (MyForm.SUIDeadlineInitialInvestigation.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDateInitialInvestigation)) Then
    MyForm.SUIDateInitialInvestigation.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDateInitialInvestigation) Then
    MyForm.SUIDateInitialInvestigation.BackColor = vbGreen
Else
     MyForm.SUIDateInitialInvestigation.BackColor = vbWhite
End If

'This code changes the colour for the SHA Informed box depending on deadline date
If MyForm.SUIDeadlineSHAInformed.Value < Now() And IsNull(MyForm.SUIDateSHAInformed.Value) Then
     MyForm.SUIDateSHAInformed.BackColor = vbRed
ElseIf (MyForm.SUIDeadlineSHAInformed.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDateSHAInformed)) Then
    MyForm.SUIDateSHAInformed.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDateSHAInformed) Then
   MyForm.SUIDateSHAInformed.BackColor = vbGreen
Else
     MyForm.SUIDateSHAInformed.BackColor = vbWhite
End If



'This code changes the colour for the Initial Investigation box depending on deadline date
If MyForm.SUIDeadlineIOReport.Value < Now() And IsNull(MyForm.SUIDateIOReport.Value) Then
    MyForm.SUIDateIOReport.BackColor = vbRed
ElseIf (MyForm.SUIDeadlineIOReport.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDateIOReport)) Then
    MyForm.SUIDateIOReport.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDateIOReport) Then
    MyForm.SUIDateIOReport.BackColor = vbGreen
Else
    MyForm.SUIDateIOReport.BackColor = vbWhite
End If


'This code changes the colour for the Date of Panel Decided box depending on the deadline date
If MyForm.SUIDeadlinePanelDecided.Value < Now() And IsNull(MyForm.SUIDatePanelDecided.Value) Then
   MyForm.SUIDatePanelDecided.BackColor = vbRed
ElseIf (MyForm.SUIDeadlinePanelDecided.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDatePanelDecided)) Then
    MyForm.SUIDatePanelDecided.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDatePanelDecided) Then
    MyForm.SUIDatePanelDecided.BackColor = vbGreen
Else
     MyForm.SUIDatePanelDecided.BackColor = vbWhite
End If


'This code changes the colour for the Interviewees Identified box depending on the deadline date
If MyForm.SUIDeadlineIntervieweesDecided.Value < Now() And IsNull(MyForm.SUIDateIntervieweesDecided.Value) Then
    MyForm.SUIDateIntervieweesDecided.BackColor = vbRed
ElseIf (MyForm.SUIDeadlineIntervieweesDecided.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDateIntervieweesDecided)) Then
    MyForm.SUIDateIntervieweesDecided.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDateIntervieweesDecided) Then
    MyForm.SUIDateIntervieweesDecided.BackColor = vbGreen
Else
     MyForm.SUIDateIntervieweesDecided.BackColor = vbWhite
End If


'This code changes the colour for the Deadline for Panel box depending on the deadline date
If MyForm.SUIDeadlinePanelHearing.Value < Now() And IsNull(MyForm.SUIDatePanelHearing.Value) Then
    MyForm.SUIDatePanelHearing.BackColor = vbRed
ElseIf (MyForm.SUIDeadlinePanelHearing.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDatePanelHearing)) Then
    MyForm.SUIDatePanelHearing.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDatePanelHearing) Then
    MyForm.SUIDatePanelHearing.BackColor = vbGreen
Else
     MyForm.SUIDatePanelHearing.BackColor = vbWhite
End If


'This code changes the colour for the Deadline for final report box depending on the deadline date
If MyForm.SUIDeadlineFinalReport.Value < Now() And IsNull(MyForm.SUIDateFinalReport.Value) Then
    MyForm.SUIDateFinalReport.BackColor = vbRed
ElseIf (MyForm.SUIDeadlineFinalReport.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDateFinalReport)) Then
    MyForm.SUIDateFinalReport.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDateFinalReport) Then
    MyForm.SUIDateFinalReport.BackColor = vbGreen
Else
     MyForm.SUIDateFinalReport.BackColor = vbWhite
End If


'This code changes the colour for the Deadline for SHA report box depending on the deadline date
If MyForm.SUIDeadlineSHAReport.Value < Now() And IsNull(MyForm.SUIDateSHAReport.Value) Then
    MyForm.SUIDateSHAReport.BackColor = vbRed
ElseIf (MyForm.SUIDeadlineSHAReport.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDateSHAReport)) Then
    MyForm.SUIDateSHAReport.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDateSHAReport) Then
    MyForm.SUIDateSHAReport.BackColor = vbGreen
Else
     MyForm.SUIDateSHAReport.BackColor = vbWhite
End If
End Function
 
Last edited:
You might also correct your terminology (just to be able to talk apples to apples). You do not run a module. You run procedures that reside IN that module, but you do not "run a module" nor do you "call a module."
 
As my sig says .. i'm a noobie learning quickly. Hopefully its possible to get the gist from my apparent poor terminology. :)

Ruth
 
OK I've commented out some lines and played around, one part of the problem seems to be that the first time the form loads I get

Runtime error 2475: You entered an expression that requires a form to be the active window.

I assume this is because the "procedure" in the module is using the following:
Code: 
Option Compare Database
Option Explicit

Public Function ColourFeatures()

Dim MyForm As Form
[B]Set MyForm = Screen.ActiveForm[/B]

Is there any way I can delay the procedures being run until the form actually loads? Assuming it'd work fine once the form loads.

I want to use this so I don't have to specify the form name as I want to use this module in multiple places where the form name will not be the same.

Ruth
 
Change your function like this:
Code: 
Public Function ColourFeatures(MyForm As Form)

then just run the function from the form's On Open or On Load event (the On Load event if the function requires data from the form)
Code: 
Call ColourFeatures(Me)
 
I tried it with this, and with the original

Code: 
Option Compare Database
Option Explicit

Public Function ColourFeatures()

Dim MyForm As Form
Set MyForm = Screen.ActiveForm
And neither get around that error (that it requires a form to be the active window)
 
Last edited:
RCheesley said:
I tried it with this, and with the original

Code: 
Option Compare Database
Option Explicit

Public Function ColourFeatures()

Dim MyForm As Form
Set MyForm = Screen.ActiveForm
And neither get around that error (that it requires a form to be the active window)
Click to expand...

Post the rest of the code of the module. You aren't understanding that you do NOT need Screen.ActiveForm if you call the function from the form with the way I showed.
 
RCheesley said:
I tried it with this, and with the original

Code: 
Option Compare Database
Option Explicit

Public Function ColourFeatures()

Dim MyForm As Form
Set MyForm = Screen.ActiveForm
And neither get around that error (that it requires a form to be the active window)
Click to expand...

Try making this in your module:
Code: 
Option Compare Database
Option Explicit

Public Function ColourFeatures(ByVal frmForm As Form)

    Dim MyForm As Form

    Set MyForm = frmForm

End Function
That's for setting the passed-by-value frmForm to your MyForm in your procedure inside your module.

Try calling the procedure in your Form like this.
Code: 
    Call ColourFeatures(Me.Form)
------------------------------------------------------------------------------------------------------------
Note:
The Function in "Public Function ColourFeatures(ByVal frmForm As Form)" is used if you are trying to return a value to your procedure.

A sample below returns a String:
Code: 
Public Function ColourFeatures(ByVal sConcat As String) As String
  
    ColourFeatures = "Hello " & sConcat

End Function
The code can be tested in your Form:
Code: 
    MsgBox ColourFeatures("Access-Programmers") 'Displays "Hello Access-Programmers"
If you are not returning anything, try using "Sub" like this:
Code: 
Public Sub ColourFeatures(ByVal frmForm As Form)
  
    .......

End Sub
Tip:
It is nice to use the Debug mode of MS Access.
 
keirnus:
keirnus said:
Try making this in your module:
Code: 
Option Compare Database
Option Explicit

Public Function ColourFeatures(ByVal frmForm As Form)

    Dim MyForm As Form

    Set MyForm = frmForm

End Function
Click to expand...
Not necessary to do up another varible. Just use frmForm.
Try calling the procedure in your Form like this.
Code: 
    Call ColourFeatures(Me.Form)
Click to expand...

Just call it
Call ColurFeatures (Me)
which passes the form, no .Form is required.
Note:
The Function in "Public Function ColourFeatures(ByVal frmForm As Form)" is used if you are trying to return a value to your procedure.
Click to expand...
It doesn't have to - I typically will use functions exclusively except for form/report subs. There is no need for a function to return a value, but it can. There is no performance reason to create a sub over a function.
If you are not returning anything, try using "Sub"...
Click to expand...
Not necessary to use subs; it is all personal preference. In fact, using functions, even when not returning a value does have a benefit over using a sub. Functions can be visible to macros and queries and control sources where subs are not.
 
Bob,

Thanks for pointing out what to you probably seems like an obvious fact, but to us mere mortals who are just teetering into the world of VBA, may not be quite so blatent if not explicitly stated as such.

It now works, to some extent, but doesn't work!

It's not giving the error, but I'm not sure it's actually working as intended. I originally had this on the AfterUpdate for a field where the date on which the calculations (and hence the changing of colours) is based upon, which worked fine - but I need this to happen every time the form/record is shown to ensure the colour is relevant to the current time.

I still have the module procedure (assume I got the terminology right this time) on the AfterUpdate, and this still seems to work fine.

I don't think it is working, however, on Load. Some fields should go green if there is a date present in the "date completed" column, for example, and they don't unless I make an update to the aforementioned field.

Tried stepping through and it does actually break at my break points on the Load for the first load of the form, so I'm fairly sure it's working for the first load, but when I go through records it is not - does this need to be under yet another event? If so which (as you can see below it relies on the data from the record being displayed, if thsi is relevant?)

Ruth

Form code
Code: 
Private Sub Form_Load()
    Call ColourFeatures(Me)
End Sub

Module code
Code: 
Option Compare Database
Option Explicit

Public Function ColourFeatures(MyForm As Form)

'This code changes the colour for the Initial Investigation box depending on deadline date
If MyForm.SUIDeadlineInitialInvestigation.Value < Now() And IsNull(MyForm.SUIDateInitialInvestigation.Value) Then
     MyForm.SUIDateInitialInvestigation.BackColor = vbRed
ElseIf (MyForm.SUIDeadlineInitialInvestigation.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDateInitialInvestigation)) Then
    MyForm.SUIDateInitialInvestigation.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDateInitialInvestigation) Then
    MyForm.SUIDateInitialInvestigation.BackColor = vbGreen
Else
     MyForm.SUIDateInitialInvestigation.BackColor = vbWhite
End If

'This code changes the colour for the SHA Informed box depending on deadline date
If MyForm.SUIDeadlineSHAInformed.Value < Now() And IsNull(MyForm.SUIDateSHAInformed.Value) Then
     MyForm.SUIDateSHAInformed.BackColor = vbRed
ElseIf (MyForm.SUIDeadlineSHAInformed.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDateSHAInformed)) Then
    MyForm.SUIDateSHAInformed.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDateSHAInformed) Then
   MyForm.SUIDateSHAInformed.BackColor = vbGreen
Else
     MyForm.SUIDateSHAInformed.BackColor = vbWhite
End If



'This code changes the colour for the Initial Investigation box depending on deadline date
If MyForm.SUIDeadlineIOReport.Value < Now() And IsNull(MyForm.SUIDateIOReport.Value) Then
    MyForm.SUIDateIOReport.BackColor = vbRed
ElseIf (MyForm.SUIDeadlineIOReport.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDateIOReport)) Then
    MyForm.SUIDateIOReport.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDateIOReport) Then
    MyForm.SUIDateIOReport.BackColor = vbGreen
Else
    MyForm.SUIDateIOReport.BackColor = vbWhite
End If


'This code changes the colour for the Date of Panel Decided box depending on the deadline date
If MyForm.SUIDeadlinePanelDecided.Value < Now() And IsNull(MyForm.SUIDatePanelDecided.Value) Then
   MyForm.SUIDatePanelDecided.BackColor = vbRed
ElseIf (MyForm.SUIDeadlinePanelDecided.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDatePanelDecided)) Then
    MyForm.SUIDatePanelDecided.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDatePanelDecided) Then
    MyForm.SUIDatePanelDecided.BackColor = vbGreen
Else
     MyForm.SUIDatePanelDecided.BackColor = vbWhite
End If


'This code changes the colour for the Interviewees Identified box depending on the deadline date
If MyForm.SUIDeadlineIntervieweesDecided.Value < Now() And IsNull(MyForm.SUIDateIntervieweesDecided.Value) Then
    MyForm.SUIDateIntervieweesDecided.BackColor = vbRed
ElseIf (MyForm.SUIDeadlineIntervieweesDecided.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDateIntervieweesDecided)) Then
    MyForm.SUIDateIntervieweesDecided.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDateIntervieweesDecided) Then
    MyForm.SUIDateIntervieweesDecided.BackColor = vbGreen
Else
     MyForm.SUIDateIntervieweesDecided.BackColor = vbWhite
End If


'This code changes the colour for the Deadline for Panel box depending on the deadline date
If MyForm.SUIDeadlinePanelHearing.Value < Now() And IsNull(MyForm.SUIDatePanelHearing.Value) Then
    MyForm.SUIDatePanelHearing.BackColor = vbRed
ElseIf (MyForm.SUIDeadlinePanelHearing.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDatePanelHearing)) Then
    MyForm.SUIDatePanelHearing.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDatePanelHearing) Then
    MyForm.SUIDatePanelHearing.BackColor = vbGreen
Else
     MyForm.SUIDatePanelHearing.BackColor = vbWhite
End If


'This code changes the colour for the Deadline for final report box depending on the deadline date
If MyForm.SUIDeadlineFinalReport.Value < Now() And IsNull(MyForm.SUIDateFinalReport.Value) Then
    MyForm.SUIDateFinalReport.BackColor = vbRed
ElseIf (MyForm.SUIDeadlineFinalReport.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDateFinalReport)) Then
    MyForm.SUIDateFinalReport.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDateFinalReport) Then
    MyForm.SUIDateFinalReport.BackColor = vbGreen
Else
     MyForm.SUIDateFinalReport.BackColor = vbWhite
End If


'This code changes the colour for the Deadline for SHA report box depending on the deadline date
If MyForm.SUIDeadlineSHAReport.Value < Now() And IsNull(MyForm.SUIDateSHAReport.Value) Then
    MyForm.SUIDateSHAReport.BackColor = vbRed
ElseIf (MyForm.SUIDeadlineSHAReport.Value > Now() - 7 < Now() - 31 And IsNull(MyForm.SUIDateSHAReport)) Then
    MyForm.SUIDateSHAReport.BackColor = 39662
ElseIf Not IsNull(MyForm.SUIDateSHAReport) Then
    MyForm.SUIDateSHAReport.BackColor = vbGreen
Else
     MyForm.SUIDateSHAReport.BackColor = vbWhite
End If
End Function
 
You might need it in the ON CURRENT event (which fires when you move from record to record).
 
Aha!!!!

I had used that before but it wasn't working for the first one, so now using the two in conjunction seems to be working as intended :)

Thanks for your help!

Ruth
 
Glad to be able to help. It is good to know how to create functions (generic) and pass objects to them. Then you become very powerful with VBA indeed. Reusability is a good thing to be thinking of whenever writing functions. How can I make this generic, so I can use it for ANY form, or ANY control, etc.

:)
 
I definitely agree - This module started out life as code for the actual form, but then I started to think about how I could use it elsewhere in the database for these fields, hence attempting to figure out what modules actually are and how to use them properly!

Ruth
 
You must log in or register to reply here.

Similar threads

amorosik
Variable defined as Public on a standard module, is not recognized
Replies
18
Views
468
amorosik
amorosik
P
Module not calling a function
Replies
6
Views
733
CJ_London
C
AOB
  • Solved
Windows Colour Dialog (aka ChooseColorA) - can the "basic" colours be changed?
2
Replies
20
Views
1,479
JTrilloJ
J
P
Need VBA Code Guidance - BeforeUpdate Event
Replies
6
Views
876
The_Doc_Man
The_Doc_Man
A
Solved VBA AfterUpdate event
2
Replies
23
Views
1,844
Isaac
Isaac

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom