needing help and direction with an equated value

[vicsaccess]

ok I have a value that comes from a excel equation that I need to convert. this DB is for asset tracking and maintenance and this value is the schedule index that is used several times during the job process to evaluate its ranking of need to be repaired. my thought thus far is that during the process of the job I don't need to add the value to the job table because it will change the closer it gets to the jobs due date and once the job is complete I can insert it into the table as a job tracking archive value. during the process of the job all the job details including this index number will be viewed in either a continuous form or a split form. to me this sounds like a good use of a class module to be called when needed but I don't' have any experience with them and to be honest right now i'm doing so much research for this database that my mind is getting confused and needs direction and help. am I right to think of a module to be called when needed or should this just be a text box equation?

my constant values are:
m=1000
n=3
p=5

my variable from the form are:
cr=me.txtcriticalid.value
h=me.txthealthid.value


pr=me.priorityid.value

the schedule index is (((cr * h * (6 - pr)) / 1000) / (((cr * n * p) / 1000) + ((h * m * p) / 1000) + (((6 - pr) * m * n) / 1000))) * 1000

[pbaldy]

I think it's a great candidate for a public function. The 3 variable values could be passed to it from anywhere. More here:

http://www.baldyweb.com/Function.htm

[vicsaccess]

ok, here we go, here's my function

Option Compare Database
Option Explicit
Public Function sch(cr As Integer, h As Integer, pr As Integer) As Integer
    
    Dim x As Integer
    x = (((cr * h * (6 - pr)) / 1000) / (((cr * 3 * 5) / 1000) + ((h * 1000 * 5) / 1000) + (((6 - pr) * 1000 * 3) / 1000))) * 1000
    sch = x
    
End Function

.

and me trying to call in on the form

Option Compare Database
Option Explicit
        
Private Sub Form_Load()
    Dim cr As Integer
    Dim h As Integer
    Dim pr As Integer
    txtSchInd.Value = sch(Me.txtCriticalID, Me.txthealthID, Me.txtPriorityID)
    
    
End Sub

I think I did it but it has the same value for all the values of txtschind on my continuous form? should I be looking somehow and hows this looking?

[vicsaccess]

here's a screen shot of what i'm seeing. text3 is the schind

[pbaldy]

You can't use an unbound textbox like that. Try this as its control source:

=sch(CriticalID, healthID, PriorityID)

Changing to the actual field names as appropriate.

[ssanfu]

I don't think you can use the form load event. You said

this index number will be viewed in either a continuous form or a split form

so you need to bind the text box to a field in the record source. The record source should be a query.

The SQL of the query should look something like:

SELECT CriticalID, healthID, PriorityID, sch(CriticalID, healthID, PriorityID) as SchInd
FROM TableName

[vicsaccess]

ok, you got me on that one. was not aware that you could call a function from a text box. I had to get rid of my on load event to get it to work but it looks like it does indeed work, thanks. I should be able to use the value of txtschind on my closing of the job in an insert SQL to archive it shouldn't I?

[vicsaccess]

thanks Steve, what's the difference from yours and Paul's?

[pbaldy]

thanks. I should be able to use the value of txtschind on my closing of the job in an insert SQL to archive it shouldn't I?

No problem. Yes you should be able to call it from insert SQL as well.

[ssanfu]

Edit: took out bad info

If the record source is a query instead of a table, you can create another "field" (column) and use that "calculated" field to act just like an actual field in a table.
Bind the new "Field" (SchInd) to the control Text3 and you should see different calculated numbers.

Alternatively, because this is a relatively simple calculation, you don't have to create a function, you could just enter the formula in the query:

SELECT CriticalID, healthID, PriorityID, (((criticalid * healthid * (6 - priorityid)) / 1000) / (((criticalid * 3 * 5) / 1000) + ((healthid * 1000 * 5) / 1000) + (((6 - priorityid) * 1000 * 3) / 1000))) * 1000 as SchInd
FROM TableName

Neither method (the formula or the function) is better than the other.....

[vicsaccess]

thanks guys, again my brain has gotten a little clearer.

[pbaldy]

If the form is in Single Form view, Paul's method will work because the form is displaying only one record.
In Continuous Forms view, multiple records are displayed. But the control Text3 is not bound and thus displays the same data in every record.

Not sure what you mean, as the method I posted would work in a continuous form. If you're referring to the load event code, that didn't come from me.

[ssanfu]

@Paul

Well butter my behind and call me a biscuit!!
I sit corrected.

Every time I have tried to use an unbound field in a continuous form, the same number was repeated down the form.
I just made a dB to test this....... and it works like you said. (not that I doubt you)
Learn something new every day.


OK, must be time to go....

See you next year......