calculate next level test

[BYoung via AccessMonster.com]

Here's what I've got. A form (frmtesting) that is based off of a table
(tbltesting). The form shows the fields in tbltesting

chrTestLevel (lookup box showing level 1-4)
chrAttempt (lookup box to choose which attempt number this is)
dtmTestDate
chrScore

I have added two unbound text boxes to the form to show what the next level
will be and when the next available test date is. I'm new to VBA and have
been trying to write code to give me the next level and test date but have
been unsuccessful.
So here's what I'm trying to do:

In the nextlevel text box I want to show
if chrTestLevel = level1 and the chrScore > 79 then
next level = Level 2
elseif chrTestLevel = level2 and the chrScore > 79 then
next level = Level 3
elseif chrTestLevel = level3 and the chrScore > 79 then
next level = Level 4
(this is not the exact code I tried, yet my elseif code isn't working)

For the NextTestDate text box I have been trying to do
if chrscore>79 then
Dateadd("m", 6, [dtmTestDate])
else Dateadd(""d", 1, [dtmTestDate])

Can anyone help me with this code?

 

[Jonathan]

Hi, it would have been helpful for you to have posted your code so that we
could review what you have done and make suggestions for correction.

Anyway here's my suggestion for you to consider...

'Check whether score greater than 79
if chrScore>79 then
'Test for value
Select Case chrTestLevel
case "Level2"
textboxShowingNextLevel="Level3"
case "Level3"
textboxShowingNextLevel="Level4"
End Select


if chrscore>79 then
textboxShowingNextDate=Dateadd("m", 6, [dtmTestDate])
else
textboxShowingNextDate=Dateadd("d", 1, [dtmTestDate])
end if

In addition to having valid checks and calculations you will have to
consider which event(s) will process these lines. That is these lines of code
could be contained in the Form_OnCurrent event.

 

[BYoung via AccessMonster.com]

Thank you for your reply Jonathan. I see where you're going with this. So
now that I've got the app in front of me I can show you what I have for code
after applying changes based on your suggestions. The form that displays
this information is a subform on one tab of a tab control. So I have entered
this code into the oncurrent event of the subform.

Private Sub Form_Current()
Dim chrFSSScore As Integer 'chrFSSScore is the test score
Dim luFSSTest As String 'luFSSTest is the lookup of the test level
Dim NextLevel As String 'NextLevel is unbound txtbox that will
display the next level test
Dim NextTestDate As Date 'NextTestDate is unbound txtbox that will
display next available test date
Dim dtmFSSTestDate As Date 'dtmFSSTestDate is date test was taken

If chrFSSScore>79 Then
Select Case luFSSTest

Case "FSS 100"
NextLevel = "FSS 200"

Case "FSS 200"
NextLevel = "FSS 300"

Case "FSS 300"
NextLevel = "FSS 400"

End Select
End IF

If chrFSSScore>79 Then
NextTestDate = Dateadd("M",6,[dtmFSSTestDate])

Else
NextTestDate = Dateadd("d",1,[dtmFSSTestDate])

End If
End Sub

What did I do wrong? At this point the code doesn't return any value into
the unbound text boxes

Hi, it would have been helpful for you to have posted your code so that we
could review what you have done and make suggestions for correction.

Anyway here's my suggestion for you to consider...

'Check whether score greater than 79
if chrScore>79 then
'Test for value
Select Case chrTestLevel
case "Level2"
textboxShowingNextLevel="Level3"
case "Level3"
textboxShowingNextLevel="Level4"
End Select

if chrscore>79 then
textboxShowingNextDate=Dateadd("m", 6, [dtmTestDate])
else
textboxShowingNextDate=Dateadd("d", 1, [dtmTestDate])
end if

In addition to having valid checks and calculations you will have to
consider which event(s) will process these lines. That is these lines of code
could be contained in the Form_OnCurrent event.

Here's what I've got. A form (frmtesting) that is based off of a table
(tbltesting). The form shows the fields in tbltesting

[quoted text clipped - 25 lines]

Can anyone help me with this code?

 

[J. Goddard]

Hi -

In the code you have posted, the names chrFSSSScore, luFSSTest etc. are
*not* the names of textboxes on the form. They are variables within the
procedure (declared with Dim statements) that HAPPEN to have the same
names as form controls, and these variables are known only within the
procedure.

You do not need Dim statements to refer to form controls

To put a value into a form text box, you use:

Me![textboxname] = somevalue. For example in your case,
me![NextLevel] = "FSS 200"

To refer to the value of a form control in an expression, for example in
an If statement, use:

if me![chrFSSScore] >79 then ....

( The square brackets around the control names are not required unless
the name of the control contains blanks (frowned on by many people!),
but I always use them anyway)

Hope this helps.

John

Thank you for your reply Jonathan. I see where you're going with this. So
now that I've got the app in front of me I can show you what I have for code
after applying changes based on your suggestions. The form that displays
this information is a subform on one tab of a tab control. So I have entered
this code into the oncurrent event of the subform.

Private Sub Form_Current()
Dim chrFSSScore As Integer 'chrFSSScore is the test score
Dim luFSSTest As String 'luFSSTest is the lookup of the test level
Dim NextLevel As String 'NextLevel is unbound txtbox that will
display the next level test
Dim NextTestDate As Date 'NextTestDate is unbound txtbox that will
display next available test date
Dim dtmFSSTestDate As Date 'dtmFSSTestDate is date test was taken

If chrFSSScore>79 Then
Select Case luFSSTest

Case "FSS 100"
NextLevel = "FSS 200"

Case "FSS 200"
NextLevel = "FSS 300"

Case "FSS 300"
NextLevel = "FSS 400"

End Select
End IF

If chrFSSScore>79 Then
NextTestDate = Dateadd("M",6,[dtmFSSTestDate])

Else
NextTestDate = Dateadd("d",1,[dtmFSSTestDate])

End If
End Sub

What did I do wrong? At this point the code doesn't return any value into
the unbound text boxes

Hi, it would have been helpful for you to have posted your code so that we
could review what you have done and make suggestions for correction.

Anyway here's my suggestion for you to consider...

'Check whether score greater than 79
if chrScore>79 then
'Test for value
Select Case chrTestLevel
case "Level2"
textboxShowingNextLevel="Level3"
case "Level3"
textboxShowingNextLevel="Level4"
End Select

if chrscore>79 then
textboxShowingNextDate=Dateadd("m", 6, [dtmTestDate])
else
textboxShowingNextDate=Dateadd("d", 1, [dtmTestDate])
end if

In addition to having valid checks and calculations you will have to
consider which event(s) will process these lines. That is these lines of code
could be contained in the Form_OnCurrent event.

Here's what I've got. A form (frmtesting) that is based off of a table
(tbltesting). The form shows the fields in tbltesting

[quoted text clipped - 25 lines]

Can anyone help me with this code?

 

[BYoung via AccessMonster.com]

Excellent, I see how it works now.
So I restructured my code and it actually returns a value; here's what I've
got.

Private Sub Form_Current()
If Me!chrFssScore>79 Then
Select Case Me!luFSSTest

Case "FSS 100"
Me!NextLevel = "FSS 200"

Case "FSS 200"
Me!NextLevel = "FSS 300"

Case "FSS 300"
Me!NextLevel = "FSS 400"
End Select
End If

If Me!chrFSSScore>79 Then
Me!NextTestDate = Dateadd("M",6,[dtmFSSTestDate])

Else
Me!NextTestDate = Dateadd("d",1,[dtmFSSTestDate])
End If
End Sub

Now my problem is that this subform is a tabular form and the same value is
being repeated in those txtboxes on all records. My thought is that maybe
the oncurrent event for this form is not the best place to run this code. Is
my assumption correct? In order for each record to show it's own independent
data shouldn't I run the code in one of the text boxes so it is separate for
all records? If so could I put it in the afterupdate event of one of my
txtboxes?

BYoung

Hi -

In the code you have posted, the names chrFSSSScore, luFSSTest etc. are
*not* the names of textboxes on the form. They are variables within the
procedure (declared with Dim statements) that HAPPEN to have the same
names as form controls, and these variables are known only within the
procedure.

You do not need Dim statements to refer to form controls

To put a value into a form text box, you use:

Me![textboxname] = somevalue. For example in your case,
me![NextLevel] = "FSS 200"

To refer to the value of a form control in an expression, for example in
an If statement, use:

if me![chrFSSScore] >79 then ....

( The square brackets around the control names are not required unless
the name of the control contains blanks (frowned on by many people!),
but I always use them anyway)

Hope this helps.

John

Thank you for your reply Jonathan. I see where you're going with this. So
now that I've got the app in front of me I can show you what I have for code

[quoted text clipped - 69 lines]

 

[Jonathan]

Hi, probably an effective location for the calculations is in the source
query so that the calculations are record related and available for each
subform independantly. You bind your textboxes to each calculated field.

To do this have the calculations a public functions in a general module
using the following as an example:

public function getNextTestLevel(chrFssScore as integer,luFSSTest as
string)as string
dim nextLevel as string

If chrFssScore>79 Then
Select Case luFSSTest

Case "FSS 100"
NextLevel = "FSS 200"

Case "FSS 200"
NextLevel = "FSS 300"

Case "FSS 300"
NextLevel = "FSS 400"
End Select
End If

getNextTestLevel=nextLevel
end function

Create a similar function for the other calculation.
In the query insert a column using the appropriate fields as parameters
nextLevel:getNextTestLevel(chrFssScore ,luFSSTest)

Benefits of this approach include:
- you no longer have to consider events to force calculations to update,
- you can use these same functions elsewhere in your application,
- much easier to maintain.
--
luck
Jonathan Parminter

Excellent, I see how it works now.
So I restructured my code and it actually returns a value; here's what I've
got.

Private Sub Form_Current()
If Me!chrFssScore>79 Then
Select Case Me!luFSSTest

Case "FSS 100"
Me!NextLevel = "FSS 200"

Case "FSS 200"
Me!NextLevel = "FSS 300"

Case "FSS 300"
Me!NextLevel = "FSS 400"
End Select
End If

If Me!chrFSSScore>79 Then
Me!NextTestDate = Dateadd("M",6,[dtmFSSTestDate])

Else
Me!NextTestDate = Dateadd("d",1,[dtmFSSTestDate])
End If
End Sub

Now my problem is that this subform is a tabular form and the same value is
being repeated in those txtboxes on all records. My thought is that maybe
the oncurrent event for this form is not the best place to run this code. Is
my assumption correct? In order for each record to show it's own independent
data shouldn't I run the code in one of the text boxes so it is separate for
all records? If so could I put it in the afterupdate event of one of my
txtboxes?

BYoung

Hi -

In the code you have posted, the names chrFSSSScore, luFSSTest etc. are
*not* the names of textboxes on the form. They are variables within the
procedure (declared with Dim statements) that HAPPEN to have the same
names as form controls, and these variables are known only within the
procedure.

You do not need Dim statements to refer to form controls

To put a value into a form text box, you use:

Me![textboxname] = somevalue. For example in your case,
me![NextLevel] = "FSS 200"

To refer to the value of a form control in an expression, for example in
an If statement, use:

if me![chrFSSScore] >79 then ....

( The square brackets around the control names are not required unless
the name of the control contains blanks (frowned on by many people!),
but I always use them anyway)

Hope this helps.

John

Thank you for your reply Jonathan. I see where you're going with this. So
now that I've got the app in front of me I can show you what I have for code

[quoted text clipped - 69 lines]

Can anyone help me with this code?

 

[BYoung via AccessMonster.com]

Jon, That worked perfectly. I didn't realize you could build code and call
it in a query. You guys have been a great help, I've learned alot. Thank
you again for your help.

Hi, probably an effective location for the calculations is in the source
query so that the calculations are record related and available for each
subform independantly. You bind your textboxes to each calculated field.

To do this have the calculations a public functions in a general module
using the following as an example:

public function getNextTestLevel(chrFssScore as integer,luFSSTest as
string)as string
dim nextLevel as string

If chrFssScore>79 Then
Select Case luFSSTest

Case "FSS 100"
NextLevel = "FSS 200"

Case "FSS 200"
NextLevel = "FSS 300"

Case "FSS 300"
NextLevel = "FSS 400"
End Select
End If

getNextTestLevel=nextLevel
end function

Create a similar function for the other calculation.
In the query insert a column using the appropriate fields as parameters
nextLevel:getNextTestLevel(chrFssScore ,luFSSTest)

Benefits of this approach include:
- you no longer have to consider events to force calculations to update,
- you can use these same functions elsewhere in your application,
- much easier to maintain.

Excellent, I see how it works now.
So I restructured my code and it actually returns a value; here's what I've

[quoted text clipped - 65 lines]