VBA Function to fill report text box

  • Thread starter Thread starter Sabosis
  • Start date Start date
S

Sabosis

Hello-

I have a report with three fields that I need to do an if...then type
statement on and am having trouble. The report should ultimately
direct me as to whether an employee needs to move to the next step in
our corrective action process. The fields I have are as follows:

CorrLevelID
TotalOccurences
UnpaidTime
NextStep

I am trying to write something that will look at the CorrLevelID
( level of corrective action, 1-5) then look to see how many
attendance occurences and unpaid time a representative has
accumulated. I will ultimately need to write several if/then/else
statements together as I will need to evaluate different corrective
levels which will each have unique combinations of total occurences &
unpaid time. Below is the code that I have started with...


Public Function NextLevelCode()
Dim CorrLevelID As Integer
Dim UnpaidTime As Integer
Dim TotalOccurances As Integer
Dim NextLevel As String



If ([CorrLevelID] = 1 And ([TotalOccurances] >= 6 Or [UnpaidTime] >=
16) Or ([TotalOccurances] >= 4 And [UnpaidTime] >= 8)) Then
NextLevel = "Y"
Else
NextLevel = ""

End If

End Function

On the report, I have the Next Step control source set to
=NextLevelCode().

Nothing is happening when I run the report.
 
Sabosis said:
I have a report with three fields that I need to do an if...then type
statement on and am having trouble. The report should ultimately
direct me as to whether an employee needs to move to the next step in
our corrective action process. The fields I have are as follows:

CorrLevelID
TotalOccurences
UnpaidTime
NextStep

I am trying to write something that will look at the CorrLevelID
( level of corrective action, 1-5) then look to see how many
attendance occurences and unpaid time a representative has
accumulated. I will ultimately need to write several if/then/else
statements together as I will need to evaluate different corrective
levels which will each have unique combinations of total occurences &
unpaid time. Below is the code that I have started with...


Public Function NextLevelCode()
Dim CorrLevelID As Integer
Dim UnpaidTime As Integer
Dim TotalOccurances As Integer
Dim NextLevel As String

If ([CorrLevelID] = 1 And ([TotalOccurances] >= 6 Or [UnpaidTime] >=
16) Or ([TotalOccurances] >= 4 And [UnpaidTime] >= 8)) Then
NextLevel = "Y"
Else
NextLevel = ""

End If

End Function

On the report, I have the Next Step control source set to
=NextLevelCode().

Nothing is happening when I run the report.
Click to expand...


Your function is declaring all the variables but never
assigns anything to them so they are all zero.

Since you said those name are the fields in the report, I
think all you need to do to get something from the function
is delete the Dim statements.
 
Sabosis said:
I have a report with three fields that I need to do an if...then type
statement on and am having trouble. The report should ultimately
direct me as to whether an employee needs to move to the next step in
our corrective action process. The fields I have are as follows:

I am trying to write something that will look at the CorrLevelID
( level of corrective action, 1-5) then look to see how many
attendance occurences and unpaid time a representative has
accumulated. I will ultimately need to write several if/then/else
statements together as I will need to evaluate different corrective
levels which will each have unique combinations of total occurences &
unpaid time. Below is the code that I have started with...
Click to expand...
Public Function NextLevelCode()
Dim CorrLevelID As Integer
Dim UnpaidTime As Integer
Dim TotalOccurances As Integer
Dim NextLevel As String
Click to expand...
If ([CorrLevelID] = 1 And ([TotalOccurances] >= 6 Or [UnpaidTime] >=
16) Or ([TotalOccurances] >= 4 And [UnpaidTime] >= 8)) Then
NextLevel = "Y"
Else
NextLevel = ""
Click to expand...
End If
Click to expand...
End Function
Click to expand...
On the report, I have the Next Step control source set to
=NextLevelCode().
Click to expand...
Nothing is happening when I run the report.
Click to expand...

Your function is declaring all the variables but never
assigns anything to them so they are all zero.

Since you said those name are the fields in the report, I
think all you need to do to get something from the function
is delete the Dim statements.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Marshall-

When I take out the DIM statements, I will open the report and get a
VB error. The box says "compile error" and "external name not
defined", and in my code the ([CorrLevelID] part is highlighted. What
does this mean?
 
Sabosis said:
Sabosis said:
I have a report with three fields that I need to do an if...then type
statement on and am having trouble. The report should ultimately
direct me as to whether an employee needs to move to the next step in
our corrective action process. The fields I have are as follows:

I am trying to write something that will look at the CorrLevelID
( level of corrective action, 1-5) then look to see how many
attendance occurences and unpaid time a representative has
accumulated. I will ultimately need to write several if/then/else
statements together as I will need to evaluate different corrective
levels which will each have unique combinations of total occurences &
unpaid time. Below is the code that I have started with...
Click to expand...
Public Function NextLevelCode()
Dim CorrLevelID As Integer
Dim UnpaidTime As Integer
Dim TotalOccurances As Integer
Dim NextLevel As String
Click to expand...
If ([CorrLevelID] = 1 And ([TotalOccurances] >= 6 Or [UnpaidTime] >=
16) Or ([TotalOccurances] >= 4 And [UnpaidTime] >= 8)) Then
NextLevel = "Y"
Else
NextLevel = ""
Click to expand...
End If
Click to expand...
End Function
Click to expand...
On the report, I have the Next Step control source set to
=NextLevelCode().
Click to expand...
Nothing is happening when I run the report.
Click to expand...

Your function is declaring all the variables but never
assigns anything to them so they are all zero.

Since you said those name are the fields in the report, I
think all you need to do to get something from the function
is delete the Dim statements.
Click to expand...
When I take out the DIM statements, I will open the report and get a
VB error. The box says "compile error" and "external name not
defined", and in my code the ([CorrLevelID] part is highlighted. What
does this mean?
Click to expand...


It probably means that you do not have text box controls
bound to the record source fields CorrLevelID, UnpaidTime
and TotalOccurances. I just noticed that NextLevel really
needs to be the name of the function, NextLevelCode

Where is the function? It should be in the report's module.
 
Sabosis said:
Sabosis wrote:
I have a report with three fields that I need to do an if...then type
statement on and am having trouble. The report should ultimately
direct me as to whether an employee needs to move to the next step in
our corrective action process. The fields I have are as follows:
CorrLevelID
TotalOccurences
UnpaidTime
NextStep
I am trying to write something that will look at the CorrLevelID
( level of corrective action, 1-5) then look to see how many
attendance occurences and unpaid time a representative has
accumulated. I will ultimately need to write several if/then/else
statements together as I will need to evaluate different corrective
levels which will each have unique combinations of total occurences &
unpaid time. Below is the code that I have started with...
Public Function NextLevelCode()
Dim CorrLevelID As Integer
Dim UnpaidTime As Integer
Dim TotalOccurances As Integer
Dim NextLevel As String
If ([CorrLevelID] = 1 And ([TotalOccurances] >= 6 Or [UnpaidTime]>=
16) Or ([TotalOccurances] >= 4 And [UnpaidTime] >= 8)) Then
NextLevel = "Y"
Else
NextLevel = ""
End If
End Function
On the report, I have the Next Step control source set to
=NextLevelCode().
Nothing is happening when I run the report.
Your function is declaring all the variables but never
assigns anything to them so they are all zero.
Since you said those name are the fields in the report, I
think all you need to do to get something from the function
is delete the Dim statements.
Click to expand...
Click to expand...
When I take out the DIM statements, I will open the report and get a
VB error. The box says "compile error" and "external name not
defined", and in my code the ([CorrLevelID] part is highlighted. What
does this mean?
Click to expand...

It probably means that you do not have text box controls
bound to the record source fields CorrLevelID, UnpaidTime
and TotalOccurances.  I just noticed that NextLevel really
needs to be the name of the function, NextLevelCode

Where is the function?  It should be in the report's module.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

The report is based on a query. The CorrLevelID and UnpaidTime fields
from the query are in the underlying table. The TotalOccurences field
in the query is a calculated field that adds up late starts, late
breaks, etc. It is in the report and is set to the query field for
it's record source as well. I changed NextLevel to NextLevelCode, but
no luck, I still get the same error. The function is just in a module.
When you say it needs to be in the reports module, how do you do that?
I really appreciate the help, trying to learn as I go....
 
Sabosis said:
Sabosis said:
Sabosis wrote:
I have a report with three fields that I need to do an if...then type
statement on and am having trouble. The report should ultimately
direct me as to whether an employee needs to move to the next step in
our corrective action process. The fields I have are as follows:

I am trying to write something that will look at the CorrLevelID
( level of corrective action, 1-5) then look to see how many
attendance occurences and unpaid time a representative has
accumulated. I will ultimately need to write several if/then/else
statements together as I will need to evaluate different corrective
levels which will each have unique combinations of total occurences &
unpaid time. Below is the code that I have started with...
Click to expand...
Public Function NextLevelCode()
Dim CorrLevelID As Integer
Dim UnpaidTime As Integer
Dim TotalOccurances As Integer
Dim NextLevel As String
Click to expand...
If ([CorrLevelID] = 1 And ([TotalOccurances] >= 6 Or [UnpaidTime] >=
16) Or ([TotalOccurances] >= 4 And [UnpaidTime] >= 8)) Then
NextLevel = "Y"
Else
NextLevel = ""
Click to expand...
End If
Click to expand...
End Function
Click to expand...
On the report, I have the Next Step control source set to
=NextLevelCode().
Click to expand...
Nothing is happening when I run the report.
Click to expand...
Your function is declaring all the variables but never
assigns anything to them so they are all zero.
Click to expand...
Since you said those name are the fields in the report, I
think all you need to do to get something from the function
is delete the Dim statements.
Click to expand...
When I take out the DIM statements, I will open the report and get a
VB error. The box says "compile error" and "external name not
defined", and in my code the ([CorrLevelID] part is highlighted. What
does this mean?
Click to expand...

It probably means that you do not have text box controls
bound to the record source fields CorrLevelID, UnpaidTime
and TotalOccurances.  I just noticed that NextLevel really
needs to be the name of the function, NextLevelCode

Where is the function?  It should be in the report's module.
Click to expand...

The report is based on a query. The CorrLevelID and UnpaidTime fields
from the query are in the underlying table. The TotalOccurences field
in the query is a calculated field that adds up late starts, late
breaks, etc. It is in the report and is set to the query field for
it's record source as well. I changed NextLevel to NextLevelCode, but
no luck, I still get the same error. The function is just in a module.
When you say it needs to be in the reports module, how do you do that?
I really appreciate the help, trying to learn as I go....
Click to expand...


A standard module does not know anything about the contents
of the report, so it would have to be rewritten to use the
report values as arguments. Since this seems to be a report
specific calculation and in the interests of simple code, I
suggest that the function be moved to the report's module.
This can be done by opening the report in design view and
using View Code. Then Cut the code from its current module
and Paste it at the bottom of the report's module.
 
Sabosis said:
Sabosis wrote:
Sabosis wrote:
I have a report with three fields that I need to do an if...then type
statement on and am having trouble. The report should ultimately
direct me as to whether an employee needs to move to the next stepin
our corrective action process. The fields I have are as follows:
CorrLevelID
TotalOccurences
UnpaidTime
NextStep
I am trying to write something that will look at the CorrLevelID
( level of corrective action, 1-5) then look to see how many
attendance occurences and unpaid time a representative has
accumulated. I will ultimately need to write several if/then/else
statements together as I will need to evaluate different corrective
levels which will each have unique combinations of total occurences &
unpaid time. Below is the code that I have started with...
Public Function NextLevelCode()
Dim CorrLevelID As Integer
Dim UnpaidTime As Integer
Dim TotalOccurances As Integer
Dim NextLevel As String
If ([CorrLevelID] = 1 And ([TotalOccurances] >= 6 Or [UnpaidTime] >=
16) Or ([TotalOccurances] >= 4 And [UnpaidTime] >= 8)) Then
NextLevel = "Y"
Else
NextLevel = ""
End If
End Function
On the report, I have the Next Step control source set to
=NextLevelCode().
Nothing is happening when I run the report.
Your function is declaring all the variables but never
assigns anything to them so they are all zero.
Since you said those name are the fields in the report, I
think all you need to do to get something from the function
is delete the Dim statements.
When I take out the DIM statements, I will open the report and get a
VB error. The box says "compile error" and "external name not
defined", and in my code the ([CorrLevelID] part is highlighted. What
does this mean?
It probably means that you do not have text box controls
bound to the record source fields CorrLevelID, UnpaidTime
and TotalOccurances.  I just noticed that NextLevel really
needs to be the name of the function, NextLevelCode
Where is the function?  It should be in the report's module.
Click to expand...
Click to expand...
The report is based on a query. The CorrLevelID and UnpaidTime fields
from the query are in the underlying table. The TotalOccurences field
in the query is a calculated field that adds up late starts, late
breaks, etc. It is in the report and is set to the query field for
it's record source as well. I changed NextLevel to NextLevelCode, but
no luck, I still get the same error. The function is just in a module.
When you say it needs to be in the reports module, how do you do that?
I really appreciate the help, trying to learn as I go....
Click to expand...

A standard module does not know anything about the contents
of the report, so it would have to be rewritten to use the
report values as arguments.  Since this seems to be a report
specific calculation and in the interests of simple code, I
suggest that the function be moved to the report's module.
This can be done by opening the report in design view and
using View Code.  Then Cut the code from its current module
and Paste it at the bottom of the report's module.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Ok, almost there.... The first part is working great, thanks for the
guidance

Now I have another text box which will indicate if a rep is on
corrective action and six months have passed without them needing to
move up to the next level. Basically, for the last 6 months they have
done well and we are going to move them down in the process. The code
I have started with is below, I am getting confused, probably in the
less than greater than parts of the code and how it needs to start out
by looking at the corrective date.

Private Function NextLevelCode1()

If ([CorrectiveDate] > DateAdd("m", -6, Date) And [CorrLevelID] = 1)
And ([TotalOccurances] < 6 And [UnpaidTime] < 16) Or
([TotalOccurances] < 4 And [UnpaidTime] < 8) Then
NextLevelCode1 = "Y"
Else
NextLevelCode1 = ""

End If
End Function
 
Back
Top