Lrn2Code
Registered User.
- Local time
- Today, 16:47
- Joined
- Dec 8, 2008
- Messages
- 56
Hello,
I am stumped and need some guidance. Have a table (br_stat_edits) which has sql statements in a field. Those statements are related to edit checks and I am trying to figure out how they are running in the code module.
I've pasted the code below and am hoping someone can help me understand what's happening. The code needs to be tweaked but since I'm not sure which statement is actually running the SQL from the table field I can't create similar functionality.
FYI - the term haystack refers to certain edits needing to run more than 1 query to get a final calculated amount.
Thanks for any guidance you can provide.
Lrn2Code
Code:
stredits = "select * from br_stat_edits"
Set rstEdits = dbs.OpenRecordset(stredits)
If rstEdits.RecordCount = 0 Then
Dim a As Variant
a = MsgBox("Congratulations. Your data is clean -- and no edits have been identified.")
Exit Sub
End If
rstEdits.MoveLast
rstEdits.MoveFirst
Dim y As Variant
Do Until rstEdits.EOF
With rstEdits
strsql = .Fields("sql")
Dim strEditCheckCode As String
strEditCheckCode = .Fields("editid")
Dim strTheHayStack As String
Dim lngRunHaystack As Long
If .Fields("morethanone") = True Then
lngRunHaystack = 1
strTheHayStack = .Fields("haystack")
End If
If .Fields("morethanone") = False Then
lngRunHaystack = 0
strTheHayStack = ""
End If
Dim strTheOrg As String
Dim lngTheOrgCount As Long
lngTheOrgCount = 0
Dim rstSQL As DAO.Recordset
Set rstSQL = dbs.OpenRecordset(strsql) [COLOR="Red"]- Would this line trigger the sql to run?[/COLOR]
If rstSQL.RecordCount > 0 Then
rstSQL.MoveLast
rstSQL.MoveFirst
'insert the edits that are triggered by the sql into the d_stat_edits table
'make sure you loop through the records that are returned so you can also id the org with the bad records
Do Until rstSQL.EOF
'============================================ NEED EDIT CHECKER TO IGNORE WHEN SW3STAT-1 AMOUNT IS $0 4-20-11
If strEditCheckCode = "SW3STAT-1" Then
Dim stramt As String
Dim rstamt As DAO.Recordset
Dim amt As Variant
Dim Orgcde As String
stramt = "Select * from d_sw3"
Set rstamt = CurrentDb.OpenRecordset(stramt)
amt = rstamt.Fields("5TotalAssessment")
Orgcde = rstamt.Fields("OrgID")
'SKIP OVER THE EDIT CHECK WHEN AMOUNT IS $0
If amt = 0 And Not IsNull(Orgcde) = True Then
If rstSQL.EOF + 1 = False Then
rstSQL.MoveNext
End If
End If
End If
'============================================ END OF EDIT CHECKER TO IGNORE WHEN SW3STAT-1 AMOUNT IS $0 4-20-11
If strTheOrg = "" Then lngTheOrgCount = 0
If strTheOrg <> rstSQL.Fields("orgid") Then lngTheOrgCount = lngTheOrgCount + 1
strTheOrg = rstSQL.Fields("orgid")
strBad = "insert into d_stat_edits select editid,Description,Significance,edit,action from br_stat_edits where editid='" & strEditCheckCode & "'"
Set qdfBad = dbs.CreateQueryDef("", strBad)
qdfBad.Execute
'POPULATE ERROR REPORT WHEN ORG IS LISTED MORE THAN ONCE 5-11-09
If strEditCheckCode = "SW1STAT-6" Then
Dim strSourceName As String
strSourceName = rstSQL.Fields("5TuitionSourceName")
Dim strComments As String
strComments = " SourceName = " & strSourceName
'rstSQL.Fields("Comments") & strSourceName
End If
If strEditCheckCode = "STAT030-1" Then
Dim strFunction As String
strFunction = rstSQL.Fields("FunctionCode")
strComments = " Function = " & strFunction
'rstSQL.Fields("Comments") & strSourceName
End If
'END OF POPULATE ERROR REPORT WHEN ORG IS LISTED MORE THAN ONCE 5-11-09
'POPULATE ERROR REPORT WHEN ORG IS LISTED MORE THAN ONCE 5-11-09
If strEditCheckCode = "SW1STAT-6" Or strEditCheckCode = "STAT030-1" Then
strBad = "update d_stat_edits set comments = '" & strComments & "' where isnull(orgid)"
Set qdfBad = dbs.CreateQueryDef("", strBad)
qdfBad.Execute
End If
'END OF POPULATE ERROR REPORT WHEN ORG IS LISTED MORE THAN ONCE 5-11-09
strBad = "update d_stat_edits set orgid='" & strTheOrg & "' where isnull(orgid)"
Set qdfBad = dbs.CreateQueryDef("", strBad)
qdfBad.Execute
Set qdfBad = Nothing
'query the haystack here -- in a recordset and loop through
'fields get value --and update a memo field with the values
Dim qdfHaystack As DAO.QueryDef
Dim strHaystackSQL As String
Dim rstHaystack As DAO.Recordset
Dim strErrorDesc As String
Dim strFieldName As String
Dim strValue As String
Dim strNeedle As String
Dim Fld As DAO.Field
If lngRunHaystack = 1 Then
If strEditCheckCode = "SW1STAT-6" Then
strHaystackSQL = "select * from " & strTheHayStack & " Where orgid = '" & strTheOrg & "' and SourceName = '" & strSourceName & "'"
ElseIf strEditCheckCode = "STAT030-1" Then
strHaystackSQL = "select * from " & strTheHayStack & " Where orgid = '" & strTheOrg & "' and Function = '" & strFunction & "'"
Else
strHaystackSQL = "select * from " & strTheHayStack & " Where orgid = '" & strTheOrg & "'"
End If
Set rstHaystack = dbs.OpenRecordset(strHaystackSQL)
If rstHaystack.RecordCount > 0 Then
Dim lngHay As Long
lngHay = 0
rstHaystack.MoveLast
rstHaystack.MoveFirst
Do Until rstHaystack.EOF
strErrorDesc = ""
For Each Fld In rstHaystack.Fields
If UCase(Fld.Name) <> UCase("editID") And UCase(Fld.Name) <> UCase("ID") And UCase(Fld.Name) <> UCase("orgid") Then
strFieldName = UCase(Fld.Name)
If IsNull(rstHaystack(Fld.Name)) Then strValue = UCase("Null")
If Not IsNull(rstHaystack(Fld.Name)) Then strValue = UCase(rstHaystack(Fld.Name))
strErrorDesc = strErrorDesc & strFieldName & "=" & strValue & ","
End If
Next Fld
lngHay = lngHay + 1
Dim xNeedle As Long
If strEditCheckCode = "SW6STAT-4" Then
xNeedle = 0
End If
If strEditCheckCode = "SW1STAT-6" Or strEditCheckCode = "STAT030-1" Then
strNeedle = "update d_stat_edits set needle='" & strErrorDesc & "' where (isnull(needle) or needle= '') and orgid='" & strTheOrg & "' and comments like '*" & strComments & "*' and editid='" & strEditCheckCode & "'"
Set qdfHaystack = dbs.CreateQueryDef("", strNeedle)
qdfHaystack.Execute
Else
strNeedle = "update d_stat_edits set needle='" & strErrorDesc & "' where (isnull(needle) or needle= '') and orgid='" & strTheOrg & "' and editid='" & strEditCheckCode & "'"
Set qdfHaystack = dbs.CreateQueryDef("", strNeedle)
qdfHaystack.Execute
End If
rstHaystack.MoveNext
strErrorDesc = ""
Loop
End If
End If
rstSQL.MoveNext
Loop
End If
.MoveNext
y = rstEdits.RecordCount
End With
Loop
'have the edits --now add the comments that were entered and saved in the util_d_stat_edits table before
stredits = "select * from util_d_stat_edits"
Set rstEdits = dbs.OpenRecordset(stredits)
'if there had been no comments entered -- then exit
If rstEdits.RecordCount = 0 Then
y = MsgBox("Edit run complete.")
Exit Sub
End If
'otherwise update d_stat_edits
rstEdits.MoveLast
rstEdits.MoveFirst
Do Until rstEdits.EOF
With rstEdits
Dim strEditName As String
strEditName = .Fields("editid")
Dim strCommentUpdate As String
Dim strTheNeedle As String
If Not IsNull(.Fields("needle")) Then strTheNeedle = .Fields("needle")
strTheOrg = .Fields("orgid")
'add comments and ufapp field status where used
If Not IsNull(.Fields("comments")) And Not IsNull(.Fields("needle")) Then
strCommentUpdate = "update d_stat_edits set comments='" & .Fields("comments") & "' where orgid='" & strTheOrg & "' and needle='" & strTheNeedle & "' and editid='" & strEditName & "'"
'REPLACED strtheOrg with Orgid IN ABOVE LINE 5-1-09
Set qdfBad = dbs.CreateQueryDef("", strCommentUpdate)
qdfBad.Execute
End If
If Not IsNull(.Fields("comments")) And IsNull(.Fields("needle")) Then
strCommentUpdate = "update d_stat_edits set comments='" & .Fields("comments") & "' where orgid='" & strTheOrg & "' and editid='" & strEditName & "'"
'REPLACED strtheOrg with Orgid IN ABOVE LINE 5-1-09
Set qdfBad = dbs.CreateQueryDef("", strCommentUpdate)
qdfBad.Execute
End If
If .Fields("ufapp") = True And Not IsNull(.Fields("needle")) Then
strCommentUpdate = "update d_stat_edits set Ufapp=true where orgid='" & strTheOrg & "' and needle='" & strTheNeedle & "' and editid='" & strEditName & "'"
'REPLACED strtheOrg with Orgid IN ABOVE LINE 5-1-09
Set qdfBad = dbs.CreateQueryDef("", strCommentUpdate)
qdfBad.Execute
End If
If .Fields("ufapp") = True And IsNull(.Fields("needle")) Then
strCommentUpdate = "update d_stat_edits set Ufapp=true where orgid='" & strTheOrg & "' and editid='" & strEditName & "'"
'REPLACED strtheOrg with Orgid IN ABOVE LINE 5-1-09
Set qdfBad = dbs.CreateQueryDef("", strCommentUpdate)
qdfBad.Execute
End If
If .Fields("unflagwithpassword") = True And Not IsNull(.Fields("needle")) Then
strCommentUpdate = "update d_stat_edits set UnflagwithPassword=true where orgid='" & strTheOrg & "' and needle='" & strTheNeedle & "' and editid='" & strEditName & "'"
'REPLACED strtheOrg with Orgid IN ABOVE LINE 5-1-09
Set qdfBad = dbs.CreateQueryDef("", strCommentUpdate)
qdfBad.Execute
End If
If .Fields("unflagwithpassword") = True And IsNull(.Fields("needle")) Then
strCommentUpdate = "update d_stat_edits set UnflagwithPassword=true where orgid='" & strTheOrg & "' and editid='" & strEditName & "'"
'REPLACED strtheOrg with Orgid IN ABOVE LINE 5-1-09
Set qdfBad = dbs.CreateQueryDef("", strCommentUpdate)
qdfBad.Execute
End If
Set qdfBad = Nothing
.MoveNext
End With
Loop
