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

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
.

Relevant Pages

  • Re: loop through records and run a function before update a field...head strom
    ... 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 used to populate a calculated field. ... AgainstPeople() ... Dim rl1 As String ... Dim RiskLevel As String ...
    (comp.databases.ms-access)
  • Re: loop through records and run a function before update a field...head strom
    ... 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 used to populate a calculated field. ... AgainstPeople() ... Dim rl1 As String ... Dim RiskLevel As String ...
    (comp.databases.ms-access)
  • Re: Problems with Delete Command
    ... The SQL could get fairly messy if you need to construct it in code, ... ContactID, and WebComID, and create your on-the-fly SQL on that saved query, ... to find the list of ContactIDs from the junction table, ... This is a style/readability thing: if you are going to use string ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need help with Code Please!!!
    ... the actual string that gets built at the end of the SQ1 build process. ... The message says Syntax error in query expression ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ...
    (microsoft.public.access.formscoding)
  • Re: Need help with Code Please!!!
    ... putting the query SQL itself in here maybe it will help you understand what I ... the actual string that gets built at the end of the SQ1 build process. ... Dim rs As Recordset 'object ref to qryCompany\USFNumber ... Dim qd As QueryDef 'object ref to query item ...
    (microsoft.public.access.formscoding)