loop through records and run a function before update a field...head strom

[Maya]

Dear all,
I'm back, yes unfortunately. Thanks to yous I was able to advance my work but got stuck again. I have a table with over 3000 records, I needed to add a column called Risk Level, I've created a massive "if-then-end if" function and need to run a loop to assign a risk level of each record according to the data it contains in all fields. I have a button with that function that works very good, but how do I update all empty Risk Levels. I know it needs to read the record first, then run the function and update the field, BUT HOW? :)

Here's what I was trying so far:

Private Sub btnUpdateRiskLevels_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("T_Incidents")

rst.MoveFirst
Do Until rst.EOF
If rst!RiskLevel = "" Or IsNull(rst!RiskLevel) = True Then
rst.Edit
rst!RiskLevel = XXX
rst.Update
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
End Sub

Now, what do I put in the XXX, all my tryouts gave either one value to one field or the same value to all fields what is not correct.
Many thank for any help out there...
Maya

[Clif McIrvin]

Something to try:

Option 1 - preferred:
Re-cast your function as a user defined function (UDF) and call it directly from an update query.

Put your UDF in a standard code module (NOT a form module).

Option Explicit
Public Function CalculateRiskLevel (Val1, Val2, Val3, ...) as Variant

-- your code here, using Val1, Val2, etc. instead of me.Fieldname (or me![Fieldname]) --

End Function

Then in the query design grid of an update query in the UpdateTo of RiskLevel use:

=CalculateRiskLevel([fieldname1],[fieldname2],[fieldname3], ...)

in the query design grid you can use the Expression Builder wizard to assist you in getting the fieldnames all entered correctly.

Option 2-

As you mention calling your function from a button I presume that it reads its parameters from the form using either me.fieldname or me![fieldname] syntax.

Redefine your function to include the recordset from your code loop like this:

Function CalculateRiskLevel (rst as recordset) as variant
-- your existing code, replacing the form (me) references with rst![fieldname] syntax
CalculateRiskLevel = function result
End Function

and replace XXX above with CalculateRiskLevel(rst)

HTH!

Clif

[Maya]

Clif, many thanks for your input. I am trying out your 2nd option but am getting compilation error at

CalculateRiskLevel = function result
it highlights "function" saying Expected: Expression
any hints?

[Clif McIrvin]

You're welcome!

"function result" is simply words that mean make sure you actually assign the results of your calculation to the function name. I wondered if that line would cause trouble ... you said you already had a function working, so you must have already been doing that. Not knowing your function, I just used words for the concept .... something like:

CalculateRiskLevel = (Val1 + Val2) / Val3

You get the idea.

----

After shutting off the computer last evening, it hit me that normally one would not store calculated values at all (normalized table structure).

Forget the RiskLevel column (field) in your table, and always return it as a calculated field in a query ... that's another reason why the UDF is a better idea.

Another minor suggestion ... instead of:

If rst!RiskLevel = "" Or IsNull(rst!RiskLevel) = True Then

use:

If nz(rst!RiskLevel,"") = "" Then

And one more observation: I've read a lot about allowing zero length strings ( "" ) causing unexpected -- and hard to diagnose -- erroneous results from queries. Presumably those issues still are true in Access 2010.

More info from MVP Allen Browne here:
http://allenbrowne.com/bug-09.html

HTH!

Clif

[Maya]

Clif, Thanks. I was actually asking myslef why doing this, would it not be better if I use the evaluation function in the reports only and run it via sql on report open, it would then assign that Risl Level value to an unbound field on the run. It's just an idea of a newby, it makes me thinking it would take too long to load...
At the same time it's a shame, cause in this project it is all about the past, old incidents are very important and need to be updated.
As to your first option I have no idea how to even start, UDF? And the function in question is actually 2 parts function with so many ifs that I had to split them because it was going over 64K in one sub.
I have a massive number of scenarios, if something then something and so on, split into two groups
1. AgainstBuildings()
2. AgainstPeople()
that look like this:

Function AgainstBuildings()

Dim rl1 As String
Dim rl2 As String
Dim rl3 As String
Dim rl4 As String
Dim rl5 As String
Dim rl6 As String
rl1 = "1. Minimum"
rl2 = "2. Low"
rl3 = "3. Moderate"
rl4 = "4. High"
rl5 = "5. Extreme"
rl6 = "6. Not rated"

' Target is C_Infrastructure
If Me.cboTrigger = "Ter" And Me.cboTarget = "C_Infrastructure" Then
Me.cboRiskLevel = rl1
End If
If Me.cboTrigger = "Ter" And Me.cboTarget = "C_Infrastructure" And Me.chkExpUsed = True Then
Me.cboRiskLevel = rl2
End If

and so on...
--------------
the function I run from a button on my form's before update that work fine is:

Dim RiskLevel As String

If Me.cboTarget = "C_Infrastructure" Or Me.cboTarget = "G_Infrastructure" Or Me.cboTarget = "F_Infrastructure" Then
RiskLevel = AgainstBuildings
Else
RiskLevel = AgainstPeople
End If

If Me.cboTrigger = "SF" And Me.cboTarget = "SF" Or Me.cboTarget = "Civ" Then
RiskLevel = AgainstPeople
End If

...and the rest of validation code, all good.

I really would like to update teh old missing Risk Level fields by looping through the table and applying this function to update one by one the fields.
It is a combination of incidents with many triggers and targets, with or without casualties, etc...
Can you walk me through either way VBA or Query please? I'm e self learner and quite new in this level of coding :). Cheers,

[Clif McIrvin]

No time just now ... I'll check back this evening or so to see if anyone else has chimed in. If not, I'll have some suggestions.

Meanwhile, Allen Browne's website has lots of really good introductory information, and links to other good sites also. Between lurking here on cdma and researching those Access websites there is lots of really good instruction to be found.

Clif

[Maya]

Cool, many thanks. And send my regards to Allen, he actually helped me big time in the past; and I sure have his web site in favourite :). Laters,

[Clif McIrvin]

Busy weekend .. getting back in front of the computer again...

On Saturday, May 12, 2012 8:05:18 AM UTC-5, Maya wrote:
<snip>
> > >
> > > I really would like to update the old missing Risk Level fields by looping through the table and applying this function to update one by one the fields.

> > > It is a combination of incidents with many triggers and targets, with or without casualties, etc...

I'm thinking there could well be several different issues to examine here, not just your question about VBA recordsets and functions.

For instance -- it seems that you are examining a set of events and circumstances related to a property, and based on the results of that examination you are assigning a risk level. Is this risk level 'fixed', or is there a possibility of new events, or changing circumstances going forward in time, that might cause the risk level to change? If so, that is another argument for making risk level a calculated field instead of a table value.

I also have this little voice somewhere in the back of my head asking if your table structure is well normalized. Again, if there is the possibility of new events as time goes on, or if some properties have few events / circumstances and others have many, that suggests a set of related parent / child tables.

The table structure can have a huge influence on how one approaches analysis of the data -- poorly normalized data can make analysis much, much more difficult.

> > > Can you walk me through either way VBA or Query please? I'm e self learner and quite new in this level of coding :). Cheers,

I have some ideas in mind ... for one of them, I need to to a little research now that I'm back at a computer with Access on it.


Also -- as you look at your code, do you find that you have a very similar 'chunk' of code repeated over and over? If so, that is a good candidate for a function. Be looking for that, and I'd be happy to work you through how to revise similar code that is nearly the same except for field names and make it into a function call.


I'd consider it good coding practice to have one function - a 'top level' function, perhaps, call multiple other functions. The ability to 're-use' a bit of code instead of copying it over and over is one advantage. Another advantage is the ability to keep the logical structure of your process 'tight' and easy to see ... where the code that actually does the work is in called procedures (functions).

I'm off to earn my paycheck, and find an opportunity to to that testing I mentioned earlier. I'll be posting again later.

> >
<snip>

>
> Cool, many thanks. And send my regards to Allen, he actually helped me big time in the past; and I sure have his web site in favourite :). Laters,

Yeah -- I've learned a lot from Allen, and others in this room. This is a good place to come for help!

Oh- before I forget: what version of Access are you working in? My company upgraded to Office 2010 a while back, and that is all I have to work with. If you're working in an earlier version, menus, etc. will be somewhat different.

Clif

[David Hare-Scott]

If the value of risk is based on data already in other fields of the table
you have a normalisation problem (that is one field is dependent on other
fields) and you should seriously consider calculating the risk value on the
fly as needed. For 3000 records you are unlikely to ever have a
performance problem even if all have to be calculated at run time. How will
you ensure that when records are edited or added the new value of risk will
be updated without fail?

As a general rule update queries are much quicker and easier to write than
looping through a recordset. Unless the formula is very complex you could
probably write it in 15 minutes.

David

[Clif McIrvin]

Back again. I'm writing my comments "in-line" with more at the bottom.

On Saturday, May 12, 2012 7:13:45 AM UTC-5, Maya wrote:
<snip>
>

> Clif, Thanks. I was actually asking myslef why doing this, would it not be better if I use the evaluation function in the reports only and run it via sql on report open, it would then assign that Risl Level value to an unbound field on the run. It's just an idea of a newby, it makes me thinking it would take too long to load...

I also am self-taught, and work with Access only once in a while. Keeping that in mind, some of my suggestions may be quite "non-standard".

Actually, this idea is very close to how I would suggest doing it. However, instead of putting the code in either a report or a form I would put the code behind a query as a user defined function (UDF) used to populate a calculated field.

Calculations are pretty fast -- if calculating the risk level on the fly (on the run, as you said) is slow then you need to carefully examine your algorithms to see if you can make your code more efficient. David's post from earlier today lists several very good reasons to always calculate risk level on the fly. That question can wait until you see how well your functions perform as a calculated field.

> At the same time it's a shame, cause in this project it is all about the past, old incidents are very important and need to be updated.

It's difficult for me to imagine a business model that does not require updating (adding to) historical records as time moves on. However, you are the one that knows your situation, and can best determine whether an item's risk level will ever change in the future.

> As to your first option I have no idea how to even start, UDF? And the function in question is actually 2 parts function with so many ifs that I had to split them because it was going over 64K in one sub.

The model of using many different functions to accomplish one calculation is a good model; there is nothing at all wrong with nesting function calls. I said more about that in a reply earlier today.

I will place an example of a UDF that is written to be used in a query, not a form, at the bottom of this post. The example UDF can be used to call your functions to do the work.

> I have a massive number of scenarios, if something then something and so on, split into two groups
> 1. AgainstBuildings()
> 2. AgainstPeople()
> that look like this:

>
> Function AgainstBuildings()
>
> Dim rl1 As String
> Dim rl2 As String
> Dim rl3 As String
> Dim rl4 As String
> Dim rl5 As String
> Dim rl6 As String
> rl1 = "1. Minimum"
> rl2 = "2. Low"
> rl3 = "3. Moderate"
> rl4 = "4. High"
> rl5 = "5. Extreme"
> rl6 = "6. Not rated"
>
> ' Target is C_Infrastructure
> If Me.cboTrigger = "Ter" And Me.cboTarget = "C_Infrastructure" Then
> Me.cboRiskLevel = rl1
> End If
> If Me.cboTrigger = "Ter" And Me.cboTarget = "C_Infrastructure" And Me.chkExpUsed = True Then
> Me.cboRiskLevel = rl2
> End If
>

Try:
Function AgainstBuildings(rst As recorsdet) As Variant

Const rl1 As String = "1. Minimum"
Const rl2 As String = "2. Low"
Const rl3 As String = "3. Moderate"
Const rl4 As String = "4. High"
Const rl5 As String = "5. Extreme"
Const rl6 As String = "6. Not rated"

' Target is C_Infrastructure
Select Case rst!Trigger
Case "Ter"
Select Case rst!Target
Case "C_Infrastructure"
If rst!ExpUsed = True Then
AgainstBuildings = rl2
Else
AgainstBuildings = rl1
End If
End Select 'Case rst!Target
End Select 'Case rst!Trigger

> and so on...
> --------------
> the function I run from a button on my form's before update that work fine is:
>
> Dim RiskLevel As String
>
> If Me.cboTarget = "C_Infrastructure" Or Me.cboTarget = "G_Infrastructure" Or Me.cboTarget = "F_Infrastructure" Then
> RiskLevel = AgainstBuildings
> Else
> RiskLevel = AgainstPeople
> End If
>
> If Me.cboTrigger = "SF" And Me.cboTarget = "SF" Or Me.cboTarget = "Civ" Then
> RiskLevel = AgainstPeople
> End If
>

becomes:

Dim RiskLevel As String

Select Case rst!Target
Case "C_Infrastructure", "G_Infrastructure", "F_Infrastructure"
RiskLevel = AgainstBuildings
Case Else
RiskLevel = AgainstPeople
End Select 'Case rst!Target

If rst!Trigger = "SF" And rst!Target = "SF" Or rst!Target = "Civ" Then

RiskLevel = AgainstPeople
End If

'do you mean (1)
If rst!Trigger = "SF" Then
If rst!Target = "SF" Or rst!Target = "Civ" Then

RiskLevel = AgainstPeople
End If

End If

'or (2)
If rst!Trigger = "SF" And rst!Target = "SF" Then

RiskLevel = AgainstPeople
End If

If rst!Target = "Civ" Then

RiskLevel = AgainstPeople
End If

'I suspect that you meant (1), but (2) is what you wrote.
'(1) could also be written:
If rst!Trigger = "SF" And (rst!Target = "SF" Or rst!Target = "Civ") Then

RiskLevel = AgainstPeople
End If


> ...and the rest of validation code, all good.
>
> I really would like to update teh old missing Risk Level fields by looping through the table and applying this function to update one by one the fields.
> It is a combination of incidents with many triggers and targets, with or without casualties, etc...
> Can you walk me through either way VBA or Query please? I'm e self learner and quite new in this level of coding :). Cheers,

Without knowing more of your rules it is quite difficult to suggest ways to improve your code's efficiency.

The change from mc.cbo... (or me.chk...) to rst!... assumes that 1) all your comboboxes and checkboxes are bound to fields of the same name, and 2) that there are no spaces or reserved words in your field names. If 2) is a bad assumption, then the fieldname will need to be wrapped in square brackets, like this: rst![Trigger]. If 1) is a bad assumption, then you need to use the actual fieldname for the suggestion below to work.

---------

Starting with the code snippet you said is behind your form's before update and re-casting it as a UDF (all UDFs must be in a standard code module, not a form module):

Function RiskLevel(rst As Recordset) As String

Select Case rst!Target
Case "C_Infrastructure", "G_Infrastructure", "F_Infrastructure"
RiskLevel = AgainstBuildings(rst)
Case Else
RiskLevel = AgainstPeople(rst)
End Select 'Case rst!Target

'if you meant (1)
If rst!Trigger = "SF" Then
If rst!Target = "SF" Or rst!Target = "Civ" Then
RiskLevel = AgainstPeople(rst)
End If
End If

End Function

This new function RiskLEvel will be called from my sample function GetCurrentRecord.

I created this example against existing data in a test project I have, so the query fits my data, not yours. The SQL query definition is a Copy / Paste from the SQL View of the Query Design Editor. The calculated field is the last field in the query and is named (aliased- AS UDF) UDF.

The Query definition:

SELECT MixItems.MixID, MixItems.ItemType, MixItems.Item, MixItems.ItemQty, MixItems.SpG, MixItems.Source, ItemTypes.ItemCode, MixItems.TypeSeq, MixItems.ItemPct, GetCurrentRecord("MixItemSubform_q",[ItemID]) AS UDF
FROM ItemTypes INNER JOIN MixItems ON ItemTypes.TypeID = MixItems.ItemType
ORDER BY MixItems.ItemType, MixItems.TypeSeq;

The sample UDF:

Public Function GetCurrentRecord(strQdef As String, _
lngPKValue As Long) As Variant
' Example UDF showing how to make the current record of a
' running saved query available to VBA directly from the query
' without using a form, using the query name and the primary key.
' This code assumes a simple, ordered SELECT query, and adds
' a WHERE clause using the passed in primary key value.

' I do not know any way to learn the name of the running query,
' so the query name must be passed in as a paramater.

' To keep the example simple, I 'hard-coded' the primary key
' fieldname.

' This example assumes that each row returned by the query is
' uniquely identified by a single (autonumber) primary key field.

' There may be better ways to accomplish this task -- a somewhat
' limited search did not turn up any other examples, so this is
' the results of my own experimentation. I have attempted to
' annotate the what's and the why's of this code, and to call
' attention to the places where I am uncertain.

' Static variables are used to avoid unnecessary execution of
' portions of the code-- for instance, getting the SQL of the
' running query is needed only once. I came across a comment
' from Larry Linson that sometimes a UDF is called multiple times
' for a single record -- I observed that to happen if I scrolled
' (left and right) away from and back to the calculated field in
' the query's datasheet view, so I include a check to see if
' this call is for the record last processed, and if so simply
' return the previous result.

' Error handling is not included.

' Developed using Access 14

' Enjoy!

' Clif McIrvin 14 May 2012

Static stlngPKValue As Long 'local copy of last primary key value
Static stLastReturnValue As Variant
Static ststrQdef As String 'local copy of the query name
Static strSQLlt As String 'the 'left' portion of the query SQL
Static strSQLrt As String 'the 'right' protion ...

Dim strSQL As String
Dim varY As Long

Dim dbs As dao.Database
Dim rst As dao.Recordset
Dim qdf As dao.QueryDef

' (these GetCurrentRecords assume that there can be only one process calling
' this code)

' is this a repeat of the same record?
' If yes, simply return previous result
If stlngPKValue <> lngPKValue Then 'no - begin processing
stlngPKValue = lngPKValue ' remember this for the next call

Set dbs = DBEngine(0)(0) 'set pointer to current database

' is this the same query as last time?
If ststrQdef <> strQdef Then ' no - get the SQL behind the query
ststrQdef = strQdef ' remember for next call
Set qdf = dbs.QueryDefs(strQdef)
strSQL = qdf.SQL
' prepare to insert WHERE clause before the ORDER BY
' the query designer inserts a CR/LF pair between clauses
varY = InStrRev(strSQL, vbCrLf & "ORDER BY")
strSQLlt = Left(strSQL, varY - 1) ' all clauses before the ORDER BY
strSQLrt = Mid(strSQL, varY) 'everything else
Set qdf = Nothing 'finished with the querydef object
End If
'insert the WHERE clause for this record
strSQL = strSQLlt & vbCrLf & "WHERE ItemID=" & lngPKValue & strSQLrt
'Debug.Print strSQL 'use immediate window to verify created SQL statement

Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
' for this example, I simply returned one of the text columns from
' the current record that was unique over the sample data, so it was
' easy to see that this code was working correctly:

stLastReturnValue = rst!Item

' one could insert processing code right here, or insert a call to
' another function to do the actual processing, like this:

' stLastReturnValue=YourCustomFunction(rst)

' where YourCustomFunction is defined like this:

' [Private] Function YourCustomFunction (rst as recordset) as variant
' .... your code here
' YourCustomFunction=<result of your procedure>
' end function

' done with the recordset, clean up the instantiated objects

rst.Close
Set rst = Nothing

Set dbs = Nothing
End If 'stlngPKValue = lngPKValue

' return the calculated result
GetCurrentRecord = stLastReturnValue
End Function


To use (your) RiskLevel function, change

stLastReturnValue = rst!Item

to

stLastReturnValue=RiskLevel(rst)

Have fun!

Clif

[Maya]

Clif, GOD you lost me there big time... I printed it out and keep looking like it's Chineese...ha ha.
I feel like I need to make a few precisions, the 3000 records are past incidents, their risk level values once assigned, will never change. The whole project is about actually terrorism related incidents in northern Africa, in the first place we were in Algeria only, now we want to run about 12 different countries, the risk level field was added recently. So, the actual incident entry form calculates the values mentioned before (who attacks, who is targeted, explosives used yes/no, who the victims are and what is the type of injury (killed, injured, kidnaped etc...), obviously we care more about life risk to expatriate personnel rather than locals or local army or police force.
It took a few days of brainstorm to set the model of level definition, and still I think the way I decided to go with if then etc, was not the best option. Like you said, it could be done shorter and faster, I just got lost in my own lines of ifs I guess. By deffault the risk value is "6. Not rated", so there are no empty Risk levels ever, only in a few combinations not meeting any of the criterias assigning a higher level, this value will not change.
You know what? I will make a few screen shots and post them in, if possible, pictures speak of a thousand words. If not, I can send you a sample on your email to have a look, with just a few incidents in. Don't really care if you sell it and make millions ha ha...joking.
I'll sit and read your code fow a few hours now and try to get my head around.
Thanks to both, Ta

[Maya]

David, thanks.
Yes, I think you are right about the query approach. It only needs to be done once for the old records. All the new entries will have the risk value calculated before update event and saved.
The history is, we have created a new data base on the old model, added risk value field, modified a few others and restructured all old entries according to the new model. I have the old records in Excel.xlsb (so it does not truncate the Note field to 255 chars)
Exapmle:
ID IncDate RiskLevel Region Country City ... TKill TInj etc...
1 01/01/2004 <empty> Africa Algeria Algiers ... 4 2 etc...

so maybe an update query mentioned by Clif using a function would do the trick, I guess...
Once it is performed, we won't need to come back to it anymore.
All in all, my gut feeling it that I need to review the if then scenarios to make it faster and easier. Cheers, I'm getting back to my screenshots, will find a way to show you.

[Clif McIrvin]

Thanks for the explanation - it does help to have more background when trying to answer questions.

Yeah, there's a lot there. You mentioned being self-taught .... the knowledge behind my example UDF represents things I have learned over several years ... much, if not most, of it in mpa / mpe (microsoft.public.access / .excel), and now here in cdma. I find that my knowledge of Access / VBA continues to increase the longer I use it.

As to screenshots -- I think you'll either have to upload them to some file sharing service and post links, or use email. I know that the actual USENET cdma newsgroup does not accept binaries. I don't regularly check this email address, so if you do send something post a "heads up" here.

If you want to be able to use your code from either a query or a form,

> > Function RiskLevel(rst As Recordset) As String

> > Function AgainstBuildings(rst As recorsdet) As Variant

> > Function AgainstPeople(rst As recorsdet) As Variant

should be changed to

Function RiskLevel(FormOrRecord As object) As String
Function AgainstBuildings(FormOrRecord As object) As Variant
Function AgainstPeople(FormOrRecord As object) As Variant

then RiskLevel could be called from GetCurrentRecord with

stLastReturnValue=RiskLevel(rst)

or from your form with

me.cboRiskLevel=RiskLevel(me)

Since I used 'rst' to stand for 'recordset', I would suggest replacing 'rst' with something like 'obj', or something more self-documenting like 'FormOrRecord' ... which would also require replacing all the occurrences of 'rst!...' with 'FormOrRecord!...'


Clif

[Clif McIrvin]

The GetCurrentRecord UDF I posted originally included no error handling, which cost me big time when I made a change in the FUNCTION statement and did not make the corresponding change to the call in the query.

I also lost time during testing before I realized why changes I was making to the query definition were not showing up to the VBA code -- that traced back to the fact that DBEngine does not refresh its collections -- explanation below.

Revised GetCurrentRecord:

I created this example against existing data in a test project I have, so the query fits my data, not yours. The SQL query definition is a Copy / Paste from the SQL View of the Query Design Editor. The calculated field is the last field in the query and is named (aliased- AS UDF) UDF.

The Query definition:

SELECT MixItems.MixID, MixItems.ItemType, MixItems.Item, MixItems.ItemQty, MixItems.SpG, MixItems.Source, ItemTypes.ItemCode, MixItems.TypeSeq, MixItems.ItemPct, GetCurrentRecord("MixItemsSubform_q",[ItemID]) AS UDF

FROM ItemTypes INNER JOIN MixItems ON ItemTypes.TypeID = MixItems.ItemType
ORDER BY MixItems.ItemType, MixItems.TypeSeq;

The UDF:

Public Function GetCurrentRecord(Optional svarQdef As Variant, _
Optional lvarPKValue As Variant) As Variant

' Minimal error handling is included. By not adding error handling
' I cost myself way too much time: during testing I changed the
' function paramater definitions and failed to change the call in
' the query- which resulted in the very UNhelpful return value of
' #ERROR ... the simple process of making the parameters optional
' variants allows the UDF to return a meaningful error message if
' the supplied parameters are missing or invalid.

' Developed using Access 14

' Enjoy!

' Clif McIrvin 15 May 2012

Static stlngPKValue As Long 'local copy of last primary key value
Static stLastReturnValue As Variant

Static stsvarQdef As String 'local copy of the query name

Static strSQLlt As String 'the 'left' portion of the query SQL
Static strSQLrt As String 'the 'right' protion ...

Dim strSQL As String

Dim strErr As String
Dim lngY As Long

Dim dbs As dao.Database
Dim rst As dao.Recordset
Dim qdf As dao.QueryDef

' check for missing or invalid paramteters
If IsMissing(svarQdef) Then
strErr = ", Query name missing"
End If
If VarType(svarQdef) <> vbString Then
strErr = strErr & ", Query name not string"
End If
If IsMissing(lvarPKValue) Then
strErr = strErr & ", PK value missing"
End If
Select Case VarType(lvarPKValue)
Case vbLong, vbInteger
' an autonumber PK field is passed as a Long
' if called with a constant, small values are passed as Integers
Case Else
strErr = strErr & ", PK value expected type long"
End Select
If strErr <> "" Then
stLastReturnValue = strErr
GoTo ExitFunction
End If

' (this code assumes there is only one process calling it)

' is this a repeat of the same record?
' If yes, simply return previous result

If stlngPKValue <> lvarPKValue Then 'no - begin processing
stlngPKValue = lvarPKValue ' remember this for the next call

'Set dbs = DBEngine(0)(0) 'set pointer to current database

Set dbs = CurrentDb 'use during testing -- slower call
'Calls to DBEngine(0)(0) do not automatically refresh collections
'as does a call to CurrentDb- so if (for instance) the query
'definition is changed during testing the instantiated dbs object will
'not reflect those changes.
'"That is, the collections of DBEngine(0)(0) are initialized when the
'database is opened and not refreshed when they are changed. Each
'time you call CurrentDB, the collections are refreshed (this is why
'it is many times slower than DBEngine(0)(0))." (DWF)
'This means that in production use, DBEngine(0)(0) will
'execute faster than CurrentDb -- in this model where the call
'is made for every record in the recordset the difference
'could be significant.
'For more in depth discussion see the newsgroup thread at


'http://groups.google.com/group/microsoft.public.access/browse_thread/thread/4c1cba5e6ca34fa7

/9fec1206408c9a50?q=DBEngine

' is this the same query as last time?

If stsvarQdef <> svarQdef Then ' no - get the SQL behind the query
stsvarQdef = svarQdef ' remember for next call
On Error Resume Next
Err.Clear
Set qdf = dbs.QueryDefs(svarQdef)
If Err.Number <> 0 Then
stLastReturnValue = "Query not found"
GoTo ExitFunction
End If
On Error GoTo 0

strSQL = qdf.SQL
' prepare to insert WHERE clause before the ORDER BY
' the query designer inserts a CR/LF pair between clauses

lngY = InStrRev(strSQL, vbCrLf & "ORDER BY")
strSQLlt = Left(strSQL, lngY - 1) ' all clauses before the ORDER BY
strSQLrt = Mid(strSQL, lngY) 'everything else

Set qdf = Nothing 'finished with the querydef object
End If
'insert the WHERE clause for this record

strSQL = strSQLlt & vbCrLf & "WHERE ItemID=" & lvarPKValue & strSQLrt

'Debug.Print strSQL 'use immediate window to verify created SQL statement

Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)

' check for empty recordset (PK value invalid)
If rst.RecordCount = 0 Then
stLastReturnValue = "PK Value invalid"
GoTo ExitFunction
End If

' for this example, I simply returned one of the text columns from
' the current record that was unique over the sample data, so it was
' easy to see that this code was working correctly
stLastReturnValue = rst!Item

' one could insert processing code right here, or insert a call to

' another function to do the actuall processing, like this:

' stLastReturnValue=YourCustomFunction(rst)

' where YourCustomFunction is defined like this:

' [Private] Function YourCustomFunction (rst as recordset) as variant
' .... your code here
' YourCustomFunction=<result of your procedure>
' end function

' done with the recordset, clean up the instantiated objects
rst.Close
Set rst = Nothing
Set dbs = Nothing

End If 'stlngPKValue = lvarPKValue

' return the calculated result
ExitFunction:
If Left(stLastReturnValue, 2) = ", " Then
GetCurrentRecord = Mid(stLastReturnValue, 3)
Else
GetCurrentRecord = stLastReturnValue
End If
End Function


Clif

[Maya]

Clif,
Sorry man, I'm lost. With no disrespect to your work and time, thank you for that, I spoke to my colleagues and considering the db standards, why don't we start from the scratch?
1.
Say I agree not to store calculated fields in a table. I got rid of the RiskLevel field from my table.
Now, all querries and forms are clear of that field.
2.
We have reviewed the risk level assigning method and decided to go on the edge and just keep 3 options:
deffault value of the txtRiskLevel field (still in the reports) would be:
1. Low,
if any of the casualties fields is not null, then change txtRiskLevel to:
2. Medium,
and in the end if any of the Expatriate casualties fields not null, then set the txtRiskLevel to:
3. High.

Pretty simple heih? Replacing 250 lines of code by 10 :)
BUT, how do I start? I tried a function in a separate module, but how to call it from a query or directly from the report so it fills each entry accordingly?
I have done a jpeg, will try to put it on my server and provide a link. Or if you could send me an email to smm...@yahoo.fr then I will reply and attach the pic or even the db for your review? Many thanks, and again sorry for the hustle, I hope you understand the point of view and the needs...
Maya

[Maya]

So, I continue.
In my query I have added a field RiskLevel:SetRiskLevel().
On my report I have a bound field to that query called RiskLevel.
A separate Module1 with a public function SetRiskLevel().

So how should my code look like please?

Public Function SetRiskLevel()
Dim rsk1 as String
Dim rsk2 As String
Dim rsk3 As String
rsk1 = "Low"
rsk2 = "Medium"
rsk3 = "High"

then I'm blind, how to loop record by record and assign a level of risk according to the criterions I mentioned above?
Or maybe use the Case? how? my eyes are poping up by now ha ha.
Cheers,
Maya

[Clif McIrvin]

On Wednesday, May 16, 2012 5:35:59 AM UTC-5, Maya wrote:

<snip>

Maya-

> Clif,
> Sorry man, I'm lost. With no disrespect to your work and time, thank you for that, I spoke to my colleagues and considering the db standards, why don't we start from the scratch?

you're welcome. No offense taken.

> 1.
> Say I agree not to store calculated fields in a table. I got rid of the RiskLevel field from my table.
> Now, all querries and forms are clear of that field.

Remove from table -- not necessary to remove from queries or forms.

One could choose to write a procedure to calculate the risk level only for a report, BUT:

Why not allow users to see the calculated risk level when looking at the data using a form?

If you remove RiskLevel from the queries, you would need to include your code in every report and form where you want to see it.

On the other hand, by leaving risk level in the query as a calculated field, RiskLevel is available to any form or report bound to that query just like any other field in that query. In other words, by having the query calculate the field, reports and forms require no code -- it's just another field, and can be sorted, grouped, counted ... whatever you need done with it.

> 2.
> We have reviewed the risk level assigning method and decided to go on the edge and just keep 3 options:
> deffault value of the txtRiskLevel field (still in the reports) would be:
> 1. Low,
> if any of the casualties fields is not null, then change txtRiskLevel to:
> 2. Medium,
> and in the end if any of the Expatriate casualties fields not null, then set the txtRiskLevel to:
> 3. High.
>
> Pretty simple heih? Replacing 250 lines of code by 10 :)

It may not be necessary to abandon your more detailed evaluation. The process would be the same whether it is 250 lines of code, or only 10. The difference in execution time between 250 and 10 would be difficult to measure ... if you were processing millions of records it might be an issue worth considering.

Point: It might be possible to replace the 10 lines of code entirely by using built-in functions and not use VBA at all. More on that later.

Point: "if any of the ... fields is not null" suggests to me that you have columns (fields) that should be related child records in other tables. From my limited experience, I know that it can sometimes be difficult to justify taking the time to redesign your table structure. On the other hand, one can expect that over the life span of the application the amount of time lost in continuing to cope with the issues caused by non-normalized data structures will eventually far exceed the time spent in redesign.

If you want to explore normalizing this data, I would suggest starting a new thread, as it really is a different topic.

> BUT, how do I start? I tried a function in a separate module, but how to call it from a query or directly from the report so it fills each entry accordingly?

That is the purpose of GetCurrentRecord. Post the SQL of the query that the report is bound to, and I should be able to post the exact code you need in the standard module to call your code as well as the exact change to the SQL to add RiskLevel as a calculated field.

If you do not wish to post your full SQL, you can copy the query and delete most of the fields, and post that SQL. Then, take the calculated field that I give you and add it to your query.

My code requires that every row in the query result is associated in a 1:1 relationship with a unique index in the parent table. I assumed that unique index to be an autonumber primary key, but that is not necessary. I will need to know enough about your table structure to know what the unique index is.

Another way to call your code is to have your code work with passed parameters instead of fields. That is, instead of using code like this in your procedure:

If rst![fieldname1] = "some value" then

use code like this:

If varField1 = "some value" then

where the Function statement looks something like this:

Public Function CalculateRiskLevel(varField1, varField2, ... , varFieldX)

(this makes both the function and all the parameters type Variant - you can explicitly declare the type, but you want the parameters to be Variant so that they can accept a Null as a valid value)

and is called from the query like this:

RiskLevel: CalculateRiskLevel([Fieldname1], [Fieldname2], ... , [FieldnameX])

> I have done a jpeg, will try to put it on my server and provide a link. Or if you could send me an email to smmaya at yahoo dot fr then I will reply and attach the pic or even the db for your review? Many thanks, and again sorry for the hustle, I hope you understand the point of view and the needs...
> Maya

You can send email to clare....@gmail.com

(clare reads his mail with moe, nomail feeds the bit bucket :-)

I would be willing to look at the db ... no promises how fast that will be.


Simplified Calculated RiskLevel:

> deffault value of the txtRiskLevel field (still in the reports) would be:
> 1. Low,
> if any of the casualties fields is not null, then change txtRiskLevel to:
> 2. Medium,
> and in the end if any of the Expatriate casualties fields not null, then set the txtRiskLevel to:
> 3. High.
>

"Psuedo-code"

RiskLevel = "1. Low"

If sum ( casualties ) <> 0 then
RiskLevel = "2. Medium"
End If

If sum ( expat_casualties ) <> 0 then
RiskLevel = "3. High"
End If



Equivalent Query Builder Formula:

***
RiskLevel: Switch(nz(expat1,"") & nz(expat2,"") & ... & nz(expatX,"") <> "", "3. High",
nz(cas1,"") & nz(cas2,"") & ... & nz(casX,"") <> "", "2. Medium",
True, "1. Low")
***

Everything between *** and *** should be one single line; or can be pasted into the Query Expression Builder window as multiple lines.

From the Expression Builder you can get to the help for switch() and nz() if you are not familiar with them.

Now you have reduced your 10 lines of VBA code to zero lines.

Clif

[Maya]

Clif, here's the pic, hope it's all clear now and you can have a better idea of what I am trying to do:
http://www.sm-line.pl/dl/Untitled-1.jpg
Cheers, Maya

[Clif McIrvin]

> > I have done a jpeg, will try to put it on my server and provide a link. Or if you could send me an email to smmaya at yahoo dot fr then I will reply and attach the pic or even the db for your review? Many thanks, and again sorry for the hustle, I hope you understand the point of view and the needs...

> > Maya
>
> So, I continue.
> In my query I have added a field RiskLevel:SetRiskLevel().
> On my report I have a bound field to that query called RiskLevel.
> A separate Module1 with a public function SetRiskLevel().
>
> So how should my code look like please?
>
> Public Function SetRiskLevel()
> Dim rsk1 as String
> Dim rsk2 As String
> Dim rsk3 As String
> rsk1 = "Low"
> rsk2 = "Medium"
> rsk3 = "High"
>
> then I'm blind, how to loop record by record and assign a level of risk according to the criterions I mentioned above?
> Or maybe use the Case? how? my eyes are poping up by now ha ha.
> Cheers,
> Maya

Maya - look for the reply to your earlier post I just sent a few minutes ago.

Clif

[Clif McIrvin]

Very clear, Thank you.

Even with 14 fields involved, you could still use the switch function I mentioned earlier ... it would simply be a long expression in the query.

Note that there is no need for the green and red circles to overlap. My psuedo-code example from this morning's post "should" explain that. It's all in the order that the fields are processed.

Unless I still left you confused with the reply I posted about an hour ago I may now have you on your way with the simplified rules.

I think you will want to come back to your original risk level definitions eventually.

Clif

[Maya]

Hey, Thanks, and yes, I don't quite get the entire picture when you paste bits of functions or/and query... SORRY. what's a pseudo code?
it looks like I might be gong back to the initial idea of having that field in the table, but simplify the ifs to 3 levels, like in your pseudo code. I'll drop you an email with the db, best option I guess.
So if I understood well, I can keep the RiskLevel field in my table and just add that part of switch query?

[Clif McIrvin]

psuedo == fake, or imitation. the idea is that the psuedo code conveys a concept, not syntax for any particular language.

> it looks like I might be gong back to the initial idea of having that field in the table, but simplify the ifs to 3 levels, like in your pseudo code. I'll drop you an email with the db, best option I guess.

please no ... although in your case, where an incident is purely historical and will never change the potential for loss of integrity by carrying a calculated field should be low. As long as your calculation is on the entry form, if errors are discovered and an incident needs to be updated the risk level can be automatically re-calculated (form before update event, not a button click).

> So if I understood well, I can keep the RiskLevel field in my table and just add that part of switch query?

no - the field would not be in the table, it would be "created" by the query.

From your jpeg, I see that you are using A2007. In A2010, when I open a query in design view the ribbon gives me a "Query Tools Design" tab. On that tab, in the Query Setup group is a "Builder" Wizard button. Go to an empty column in the query design grid and click the Builder button. Paste my switch code into the large expression window at the top of the form that opens up, and modify it as needed to reflect your field names. Now when you run your query (datasheet view) you should see the RiskLevel field as the last column on the right.

As I said, I will look at your db as soon as I can.

Clif

[Clif McIrvin]

Maya-

Post your 10 line procedure, and I can change it to work with GetCurrentRecord.

Post the SQL for Q_Incidents and I can change it as well to call GetCurrentRecord and add the calculated RiskLevel field.

From there, I may be able to post the revisions to GetCurrentRecord to work with Q_Incidents - depends on whether I can see what the unique index key is or not.

With those complete pieces - instead of fragments - you may be able to realize what I have been trying to explain with my various fragments.

Clif

[Maya]

Clif,
Thanks for all, I actually got it all working on form and report, here is the function I built upon your instructions:

-----
Function SetRiskLevel()

Dim rsk1 As String

Dim rsk2 As String
Dim rsk3 As String

rsk1 = "1. Low"
rsk2 = "2. Meduim"
rsk3 = "3. High"

Dim txtRiskLevel As String

txtRiskLevel = rsk1

If (TerKilled Or TerWounded Or SFKilled Or SFWounded Or SFKidnaped Or CivKilled Or CivWounded Or CivKidnaped) <> 0 Then
txtRiskLevel = rsk2
End If

If (ExpKilled Or ExpWounded Or ExpKidnaped) <> 0 Then
txtRiskLevel = rsk3
End If

SetRiskLevel = txtRiskLevel

End Function
-------------

I have a field (unboud) on the form and on the report called txtRiskLevel with as recordsource =SetRiskLevel()

Reports look good too, but I need to have the same function in each report and form, the way it is written I guess it would not work as from the module.

You asked for the query? The entry form is data entry=yes and based on a T_Incidents table. Is it because you want to make a query as the source for each form and/or report with that function in it?
I can hear the bells but don't yet in which church ha ha.

Here is the Query for your review:
------------------------
SELECT T_Incidents.IncID, T_Incidents.IncDate, T_Incidents.Region, T_Incidents.Country, T_Incidents.District, T_Incidents.Trigger, T_Incidents.Target, T_Incidents.Action, T_Incidents.Incident, T_Incidents.TimeZone, T_Incidents.IncTime, T_Incidents.ExpKilled, T_Incidents.ExpWounded, T_Incidents.ExpKidnaped, T_Incidents.ExpRansomDemanded, T_Incidents.TerCountry, T_Incidents.TerName, T_Incidents.TerGroupSize, T_Incidents.TerKilled, T_Incidents.TerWounded, T_Incidents.TerCaptured, T_Incidents.TerSurrendered, T_Incidents.SupNetArrested, T_Incidents.SFKilled, T_Incidents.SFWounded, T_Incidents.SFKidnaped, T_Incidents.SFRansomDemanded, T_Incidents.CivKilled, T_Incidents.CivWounded, T_Incidents.CivKidnaped, T_Incidents.CivRansomDemanded, T_Incidents.ExploUsed, T_Incidents.ExploType, T_Incidents.ExploTrigger, T_Incidents.Source, *
FROM T_Incidents
ORDER BY T_Incidents.IncDate;

-----------

I'll zip and send you the new db on your email to have a look, I have only changed the R_LastWeekIncidents report Cheers

[Clif McIrvin]

Maya -

I'm happy to hear that you can hear the bells!

Here is a replacement for SetRiskLevel and Q_Incidents.

In SetRiskLevel I added strTemp .. the only reason for the extra line of code using strTemp is to give you something that you can examine during debugging if things don't work as expected.

Because I am working directly with the field values instead of a bound text box, I use the nz function to allow for the presence of any null values.

By using the concatenation operator ( & ) I coerce the result of the expression into a string data type .. so the expression works regardless of the data type of the different fields.

I also use the line continuation characters ( _) to improve readability ... I noticed that you had long lines in your VBA.

Here's the good part: when you understand how this works, you can modify your original code defining 6 risk levels and replace this simplified SetRiskLevel procedure. By changing only one procedure, every reference to risk level in any query, form or report receives the new values!


I made the necessary changes to my GetCurrentRecord, removed all the extra remarks, and renamed it as GetCurrentIncident since both T_Incidents.IncID and SetRiskLevel are hard-coded. This code could be revised to become generic, so that it could call any procedure, and use any field in the WHERE clause.


I added the calculated RiskLevel field as the second column to Q_Incidents. (I don't understand why you have the * in the SELECT clause. It looks like you explicitly specify every field you need.)


Here are the replacements:

Function SetRiskLevel(rst As Recordset)

Dim rsk1 As String
Dim rsk2 As String
Dim rsk3 As String
rsk1 = "1. Low"
rsk2 = "2. Meduim"
rsk3 = "3. High"

Dim txtRiskLevel As String

Dim strTemp As String

txtRiskLevel = rsk1

strTemp = Nz(rst!TerKilled, "") & Nz(rst!TerWounded, "") & Nz(rst!SFKilled, "") _
& Nz(rst!SFWounded, "") & Nz(rst!SFKidnaped, "") & Nz(rst!CivKilled, "") _
& Nz(rst!CivWounded, "") & Nz(rst!CivKidnaped, "")
If strTemp < 0 Then

txtRiskLevel = rsk2
End If

strTemp = Nz(rst!ExpKilled, "") & Nz(rst!ExpWounded, "") & Nz(rst!ExpKidnaped, "")
If strTemp < 0 Then

txtRiskLevel = rsk3
End If

SetRiskLevel = txtRiskLevel

End Function

Public Function GetCurrentIncident(Optional svarQdef As Variant, _

Optional lvarPKValue As Variant) As Variant

' is this the same query as last time?
If stsvarQdef <> svarQdef Then ' no - get the SQL behind the query
stsvarQdef = svarQdef ' remember for next call
On Error Resume Next
Err.Clear
Set qdf = dbs.QueryDefs(svarQdef)
If Err.Number <> 0 Then
stLastReturnValue = "Query not found"
GoTo ExitFunction
End If
On Error GoTo 0
strSQL = qdf.SQL
' prepare to insert WHERE clause before the ORDER BY
' the query designer inserts a CR/LF pair between clauses
lngY = InStrRev(strSQL, vbCrLf & "ORDER BY")
strSQLlt = Left(strSQL, lngY - 1) ' all clauses before the ORDER BY
strSQLrt = Mid(strSQL, lngY) 'everything else
Set qdf = Nothing 'finished with the querydef object
End If
'insert the WHERE clause for this record

strSQL = strSQLlt & vbCrLf & "WHERE T_Incidents.IncID=" & lvarPKValue & strSQLrt

'Debug.Print strSQL 'use immediate window to verify created SQL statement

Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
' check for empty recordset (PK value invalid)
If rst.RecordCount = 0 Then
stLastReturnValue = "PK Value invalid"
GoTo ExitFunction
End If

stLastReturnValue = SetRiskLevel(rst)

' done with the recordset, clean up the instantiated objects
rst.Close
Set rst = Nothing
Set dbs = Nothing
End If 'stlngPKValue = lvarPKValue

' return the calculated result
ExitFunction:
If Left(stLastReturnValue, 2) = ", " Then

GetCurrentIncident = Mid(stLastReturnValue, 3)
Else
GetCurrentIncident = stLastReturnValue
End If
End Function



SELECT T_Incidents.IncID,
GetCurrentIncident("Q_Incidents",T_Incidents.IncID) AS RiskLevel, T_Incidents.IncDate,

[Clif McIrvin]

I see that Google Groups considered parts of my posted code as quotations, and hid some of it.

I did a copy / paste through notepad to try and remove whatever it is that google groups uses to identify quotations ... but that evidently didn't help. Make certain you get all the code when you copy it.

Clif

[Clif McIrvin]

On Thursday, May 17, 2012 9:12:27 AM UTC-5, Clif McIrvin wrote:

<snip>

>
> Here is a replacement for SetRiskLevel and Q_Incidents.
>
> In SetRiskLevel I added strTemp .. the only reason for the extra line of code using strTemp is to give you something that you can examine during debugging if things don't work as expected.
>

<snip>

I posted in haste ... and left a couple errors in SetRiskLevel.

Because the code is called for every record returned by the query, the error just keeps popping back up.

I don't know that one can abort the query when there is a modal error dialog open ... my suggestion is to limit the query to return only a very few records while testing. (But expect my code to choke if there is already a WHERE clause in the query. Changing the SELECT to SELECT TOP 3 would likely be better.)

The corrected code is:

Function SetRiskLevel(rst As Recordset)

Dim rsk1 As String
Dim rsk2 As String
Dim rsk3 As String
rsk1 = "1. Low"
rsk2 = "2. Meduim"
rsk3 = "3. High"

Dim txtRiskLevel As String
Dim strTemp As String

txtRiskLevel = rsk1

strTemp = Nz(rst!TerKilled, "") & Nz(rst!TerWounded, "") & Nz(rst!SFKilled, "") _
& Nz(rst!SFWounded, "") & Nz(rst!SFKidnaped, "") & Nz(rst!CivKilled, "") _
& Nz(rst!CivWounded, "") & Nz(rst!CivKidnaped, "")

If strTemp <> "" Then

txtRiskLevel = rsk2
End If

strTemp = Nz(rst!ExpKilled, "") & Nz(rst!ExpWounded, "") & Nz(rst!ExpKidnaped, "")

If strTemp <> "" Then

[Maya]

Clif,
Thanks, so far so good. Fresh morning and it works in the report.
I am getting my RiskLevel field in the incidents entry form filled with PK Value Invalid, any idea why? It sure is an empty recordset since the ID dos not exist yet...how to get around that? cheers

[Maya]

I have noticed that this PK Value Invalid dissapears after about 3 minutes...so when I reopen the form and double click on the incident to open it, the function then recalculates.

[Clif McIrvin]

An empty recordset will return "PK Value Invalid" ... in my testing (from a query, not from a form) when I changed other fields in the record the calculated field automatically recalculated.

You could modify GetCurrentIncident to return null instead:

' check for empty recordset (PK value invalid)
If rst.RecordCount = 0 Then
stLastReturnValue = "PK Value invalid"
GoTo ExitFunction
End If

becomes

' check for empty recordset (PK value invalid)
If rst.RecordCount = 0 Then

stLastReturnValue = null
GoTo ExitFunction
End If

or - simply ignore the empty recordset and SetRiskLEvel should return the default value

' check for empty recordset (PK value invalid)
' If rst.RecordCount = 0 Then
' stLastReturnValue = "PK Value invalid"
' GoTo ExitFunction
' End If

If RiskLevel does not dynamically update as a new incident is entered, it might work to add a Me.Requery to the AfterUpdate event of each control that could effect the risk level.

Clif

[Maya]

Clif,
I have

cha

I have changed the PK Value invalid text by null, it's ok, but the form does not recalculates on the run. It has to save the data first, no Requery works.
You have any other idea how to pass it by? Cheers
I feel it comes to an end.

[Clif McIrvin]

I was afraid using GetCurrentIncident would not see the new fields before the entire record is written. Makes sense; the form does not commit the updated record until after the Form.BeforeUpdate event.

For the data entry from, you could try making txtRiskLevel unbound, and setting it's recordsource to

=SetRiskLevel(me)

The input form can look at the form controls instead of the record from the query-- then it can recalculate on the run.

Two changes are required:

1. change the controlsource for txtRiskLevel to
=SetRiskLevel([Forms]![Frm_Incidents])
2. change the SetRiskLevel parameter to object:

Public Function SetRiskLevel(rst As Recordset)
becomes
Public Function SetRiskLevel(rst As Object)

With those changes the input form will dynamically update risk level, and the calculated field in the query will also continue to work.

The change to SetRiskLevel is necessary because a form object is a different type than a recordset object. By using the generic "object" type, the function will work correctly with both object types.

For improved self-documentation, I would change rst to RecordOrForm as follows:

Public Function SetRiskLevel(RecordOrForm As Object)

Dim rsk1 As String
Dim rsk2 As String
Dim rsk3 As String
rsk1 = "1. Low"
rsk2 = "2. Meduim"
rsk3 = "3. High"

Dim txtRiskLevel As String
Dim strTemp As String

txtRiskLevel = rsk1

strTemp = Nz(RecordOrForm!TerKilled, "") _
& Nz(RecordOrForm!TerWounded, "") & Nz(RecordOrForm!SFKilled, "") _
& Nz(RecordOrForm!SFWounded, "") & Nz(RecordOrForm!SFKidnaped, "") _
& Nz(RecordOrForm!CivKilled, "") & Nz(RecordOrForm!CivWounded, "") _
& Nz(RecordOrForm!CivKidnaped, "")

If strTemp <> "" Then
txtRiskLevel = rsk2
End If

strTemp = Nz(RecordOrForm!ExpKilled, "") & _
Nz(RecordOrForm!ExpWounded, "") & Nz(RecordOrForm!ExpKidnaped, _

"")
If strTemp <> "" Then
txtRiskLevel = rsk3
End If

SetRiskLevel = txtRiskLevel

End Function

When I think about it, that makes sense. Thr

When I think about it, that makes sense. The form does not commit the changes to the record until after the form's BeforeUpdate event.

To keep only one SetRiskLevel function which will work for both the calculated RiskLEvel field in queries and for dynamically recalculating txtRiskLevel on the input form, two changes are needed:

1. Change SetRiskLevel to work with either a form object, or a recordset object:

Public Function SetRiskLevel(rst As recordset)
becomes
Public Function SetRiskLevel(rst As object)

2. Change the controlsource of txtRiskLevel to

=SetRiskLevel([Forms]![Frm_Incidents])

Now the input form will dynamically recalculate txtRiskLevel as other controls are changed, and the calculated RiskLevel field in the queries will continue to work.

Because SetRiskLevel will now work with either a recordset -OR- a form as it's parameter, I would also change rst to RecordOrForm to improve the 'self documentation' of the code:

Public Function SetRiskLevel(RecordOrForm As Object)

Dim rsk1 As String
Dim rsk2 As String
Dim rsk3 As String
rsk1 = "1. Low"
rsk2 = "2. Meduim"
rsk3 = "3. High"

Dim txtRiskLevel As String
Dim strTemp As String

txtRiskLevel = rsk1

strTemp = Nz(RecordOrForm!TerKilled, "") _
& Nz(RecordOrForm!TerWounded, "") & Nz(RecordOrForm!SFKilled, "") _
& Nz(RecordOrForm!SFWounded, "") & Nz(RecordOrForm!SFKidnaped, "") _
& Nz(RecordOrForm!CivKilled, "") & Nz(RecordOrForm!CivWounded, "") _
& Nz(RecordOrForm!CivKidnaped, "")

If strTemp <> "" Then
txtRiskLevel = rsk2
End If

strTemp = Nz(RecordOrForm!ExpKilled, "") & _
Nz(RecordOrForm!ExpWounded, "") & Nz(RecordOrForm!ExpKidnaped, "")

[Clif McIrvin]

On Friday, May 18, 2012 10:29:00 AM UTC-5, Maya wrote:

> I have changed the PK Value invalid text by null, it's ok, but the form does not recalculates on the run. It has to save the data first, no Requery works.
> You have any other idea how to pass it by? Cheers
> I feel it comes to an end.

Hmm ... I see that somehow between Internet Explorer and Google Groups, my last reply got pretty badly mangled. I'll try it again here:

When I think about it, that makes sense. The form does not commit the changes to the record until after the form's BeforeUpdate event.

To keep only one SetRiskLevel function which will work for both the calculated RiskLEvel field in queries and for dynamically recalculating txtRiskLevel on the input form, two changes are needed:

1. Change SetRiskLevel to work with either a form object, or a recordset object:

Public Function SetRiskLevel(rst As recordset)
becomes
Public Function SetRiskLevel(rst As object)

2. Change the controlsource of txtRiskLevel to

=SetRiskLevel([Forms]![Frm_Incidents])

Now the input form will dynamically recalculate txtRiskLevel as other controls are changed, and the calculated RiskLevel field in the queries will continue to work.

Because SetRiskLevel will now work with either a recordset -OR- a form as it's parameter, I would also change rst to RecordOrForm to improve the 'self documentation' of the code:

Public Function SetRiskLevel(RecordOrForm As Object)

Dim rsk1 As String
Dim rsk2 As String
Dim rsk3 As String
rsk1 = "1. Low"
rsk2 = "2. Meduim"
rsk3 = "3. High"

Dim txtRiskLevel As String
Dim strTemp As String

txtRiskLevel = rsk1

strTemp = Nz(RecordOrForm!TerKilled, "") _
& Nz(RecordOrForm!TerWounded, "") & Nz(RecordOrForm!SFKilled, "") _
& Nz(RecordOrForm!SFWounded, "") & Nz(RecordOrForm!SFKidnaped, "") _
& Nz(RecordOrForm!CivKilled, "") & Nz(RecordOrForm!CivWounded, "") _

& Nz(RecordOrForm!CivKidnaped, "")

If strTemp <> "" Then
txtRiskLevel = rsk2
End If

strTemp = Nz(RecordOrForm!ExpKilled, "") & _

Nz(RecordOrForm!ExpWounded, "") & Nz(RecordOrForm!ExpKidnaped, "")

[Maya]

Clif,
Dog's b...cks :)
It works great now, I guess this faze is finished now, thanks to you man.
It takes 3-5 seconds to calculate on a form but it's working. Reports too.
As I said, tomorrow I will post a recap for the others on this group.
MANY THANKS!
Maya

[Clif McIrvin]

Good to hear!

[Maya]

The initial idea was to, after modification of the table structure, fill out new missing values of risk levels of each past incident. All the way through the conclusion was not to store those calculated values, because why would you? you have a machine to work for you...
Finally 2 funtions are created in a separate module, I had to make them both Public for obvious reasons. One evaluates the risk levels and the other ??? Clif knows, I'm still trying to understand ha ha...

And this is it, the table does not need to store the Risk Level values since what's important here are the numbers of casualties (killed, Injured etc...)
The function in forms and reports calculates the risk levels on the fly.

Perfect, it has been a great learning curve.

THANKS to Clif McIrvin and Google Groups!

Here's a recap:
---------------

Public functions module content:

Option Compare Database
Option Explicit

Public Function SetRiskLevel(RecordOrForm As Object)

Dim rsk1 As String
Dim rsk2 As String
Dim rsk3 As String
rsk1 = "1. Low"

rsk2 = "2. Medium"

rsk3 = "3. High"

Dim txtRiskLevel As String
Dim strTemp As String

' deffault risk level
txtRiskLevel = rsk1

' level assigned if any of the below fields are not empty (string)
strTemp = Nz(RecordOrForm!TerKilled, "") & Nz(RecordOrForm!TerWounded, "") & Nz(RecordOrForm!SFKilled, "") _
& Nz(RecordOrForm!SFWounded, "") & Nz(RecordOrForm!SFKidnaped, "") & Nz(RecordOrForm!CivKilled, "") _
& Nz(RecordOrForm!CivWounded, "") & Nz(RecordOrForm!CivKidnaped, "")

If strTemp <> "" Then
txtRiskLevel = rsk2
End If

' level assigned if expatriates involved anyhow
strTemp = Nz(RecordOrForm!ExpKilled, "") & Nz(RecordOrForm!ExpWounded, "") & Nz(RecordOrForm!ExpKidnaped, "")

If strTemp <> "" Then
txtRiskLevel = rsk3
End If

SetRiskLevel = txtRiskLevel

End Function

--------------

Public Function GetCurrentIncident(Optional svarQdef As Variant, _
Optional lvarPKValue As Variant) As Variant

Static stlngPKValue As Long 'local copy of last primary key value
Static stLastReturnValue As Variant
Static stsvarQdef As String 'local copy of the query name
Static strSQLlt As String 'the 'left' portion of the query SQL
Static strSQLrt As String 'the 'right' protion ...

Dim strsql As String

Dim strErr As String
Dim lngY As Long

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

strsql = qdf.SQL

' prepare to insert WHERE clause before the ORDER BY
' the query designer inserts a CR/LF pair between clauses

lngY = InStrRev(strsql, vbCrLf & "ORDER BY")
strSQLlt = Left(strsql, lngY - 1) ' all clauses before the ORDER BY
strSQLrt = Mid(strsql, lngY) 'everything else

Set qdf = Nothing 'finished with the querydef object
End If
'insert the WHERE clause for this record

strsql = strSQLlt & vbCrLf & "WHERE T_Incidents.IncID=" & lvarPKValue & strSQLrt

'Debug.Print strSQL 'use immediate window to verify created SQL statement

Set rst = dbs.OpenRecordset(strsql, dbOpenSnapshot)

' check for empty recordset (PK value invalid)
If rst.RecordCount = 0 Then

stLastReturnValue = Null

GoTo ExitFunction
End If

stLastReturnValue = SetRiskLevel(rst)

' done with the recordset, clean up the instantiated objects
rst.Close
Set rst = Nothing
Set dbs = Nothing
End If 'stlngPKValue = lvarPKValue

' return the calculated result
ExitFunction:
If Left(stLastReturnValue, 2) = ", " Then

GetCurrentIncident = Mid(stLastReturnValue, 3)
Else
GetCurrentIncident = stLastReturnValue
End If

End Function

------------

Incidents Form is based on a query "Q_Incidents" as (SQL view):

SELECT T_Incidents.IncID, GetCurrentIncident("Q_Incidents",T_Incidents.IncID) AS RiskLevel, T_Incidents.IncDate, T_Incidents.Region, T_Incidents.Country, T_Incidents.District, T_Incidents.Trigger, T_Incidents.Target, T_Incidents.Action, T_Incidents.Incident, T_Incidents.TimeZone, T_Incidents.IncTime, T_Incidents.ExpKilled, T_Incidents.ExpWounded, T_Incidents.ExpKidnaped, T_Incidents.ExpRansomDemanded, T_Incidents.TerCountry, T_Incidents.TerName, T_Incidents.TerGroupSize, T_Incidents.TerKilled, T_Incidents.TerWounded, T_Incidents.TerCaptured, T_Incidents.TerSurrendered, T_Incidents.SupNetArrested, T_Incidents.SFKilled, T_Incidents.SFWounded, T_Incidents.SFKidnaped, T_Incidents.SFRansomDemanded, T_Incidents.CivKilled, T_Incidents.CivWounded, T_Incidents.CivKidnaped, T_Incidents.CivRansomDemanded, T_Incidents.ExploUsed, T_Incidents.ExploType, T_Incidents.ExploTrigger, T_Incidents.Source, *
FROM T_Incidents
ORDER BY T_Incidents.IncDate;

in the same form, the RiskLevel field has as soucre:
=SetRiskLevel([Formularze]![Frm_Incidents])

------------

Ta, Maya

[Clif McIrvin]

On Saturday, May 19, 2012 5:12:26 AM UTC-5, Maya wrote:
> The initial idea was to, after modification of the table structure, fill out new missing values of risk levels of each past incident. All the way through the conclusion was not to store those calculated values, because why would you? you have a machine to work for you...
> Finally 2 funtions are created in a separate module, I had to make them both Public for obvious reasons. One evaluates the risk levels and the other ??? Clif knows, I'm still trying to understand ha ha...
>

<snip>

It's great to hear it's up and working for you!

I will try to outline the essential steps my function does ... maybe that will help in understanding it.

That "other" function is there to pass a record from the query to Maya's function so it can evaluate the risk level for that record.

Since Maya's function was written to work with fields of a record instead of passed parameters I wrote a function to get the record to pass along, then pass the result back to the query as the calculated field.

To do this, I have to make a copy of the running query and add a criteria to return only a single record.

Any procedure running from a standard module is "blind" to the rest of the world -- it can only see any public variables that have been defined, the parameters passed when it was called, and anything it defines itself.

Rather than getting a new copy of the query for every record (that is, every time the function is called) I used STATIC variables because they remember their value between calls to the procedure. Using Static variables (STATIC instead of DIM) allows my function to go get the SQL of the query only once.

My function then breaks the SQL into two pieces, because the WHERE clause needs to be inserted in front of the ORDER BY clause.

For each call (each record), my procedure takes the value of the primary key that is passed in as a parameter, creates the WHERE clause, and executes a new query that returns a single record.

Then, it calls the risk level evaluation function, passing it the record as a parameter..

Finally, I return the evaluated risk level as my own function result.

I hope the explanation helps.

Clif