Build a temporary recordset ?

[colinbell]

Hi

When exiting a form I run a Function called AllocateType.
This function checks which other records the current record (SECT_PAIS) has linked to (PAIS_SECT) and checks their funding status.
These links are stored in tblPAIS_links
The link may link direct to a service action - this is fine because I can check this action for its funding status - this works ok for ther code below.
However some of the links may be to a PAIS action, which in turn may link to one or more service actions - this is what my code doesn't do.

so for example my current record, res-1000, may link to
res-1001 and res-1002
res-1001 is a service action so that is ok
res-1002 is a PAIS action that in turn links to res-1003 and res-1004
therefore within Allocate Type I need to check for
res-1001, res-1003 and res-1004

any help much appreciated

 

[colinbell]

sorry here is the current code

Private Function AllocateType()

Dim db As DAO.Database
Dim rst, rst1 As Recordset
Dim str, str1 As String
Dim current, current1, current2, current3, checkcount As Integer

checkcount = 0

Set db = CurrentDb
str = "SELECT * from tblPAIS_links where SECT_PAIS = '" & Me.saref & "'"
Set rst = db.OpenRecordset(str)
If Not rst.EOF Then
With rst
.MoveFirst
Do Until rst.EOF
str1 = "SELECT * from tblSECT_funding where saref = '" & rst("PAIS_SECT") & "'"
Set rst1 = db.OpenRecordset(str1)
If Not rst1.EOF Then
checkcount = 1
With rst1
.MoveFirst
Do Until rst1.EOF
If (rst1("revenue_existing") = True) Or (rst1("capital_existing") = True) Then
current = 1
Else
current = 0
End If
.MoveNext
current1 = current1 + current
Loop
End With

If current1 > 0 Then
current2 = 1
Else
current2 = 0
End If

Else
End If

.MoveNext
Loop
End With

If checkcount = 0 Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblactions set validated = true where saref = '" & Me.saref & "'")
DoCmd.RunSQL ("UPDATE tblactions set TRAINStatus = null where saref = '" & Me.saref & "'")
DoCmd.SetWarnings True
ElseIf (checkcount = 1 And current2 = 1) Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblactions set validated = false where saref = '" & Me.saref & "'")
DoCmd.RunSQL ("UPDATE tblactions set TRAINStatus = 1 where saref = '" & Me.saref & "'")
DoCmd.SetWarnings True
ElseIf (checkcount = 1 And current2 = 0) Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblactions set validated = false where saref = '" & Me.saref & "'")
DoCmd.RunSQL ("UPDATE tblactions set TRAINStatus = 2 where saref = '" & Me.saref & "'")
DoCmd.SetWarnings True
ElseIf (checkcount = 1 And current2 = Null) Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblactions set validated = true where saref = '" & Me.saref & "'")
DoCmd.RunSQL ("UPDATE tblactions set TRAINStatus = null where saref = '" & Me.saref & "'")
DoCmd.SetWarnings True
End If

Else
DoCmd.SetWarnings False
DoCmd.RunSQL ("UPDATE tblactions set validated = true where saref = '" & Me.saref & "'")
DoCmd.RunSQL ("UPDATE tblactions set TRAINStatus = null where saref = '" & Me.saref & "'")
DoCmd.SetWarnings True
End If

End Function

 

[Crowley16]

have you thought about a recursive function?

<fingers crossed>
I'm not 100% sure this will work since I've not tried it out in VBA before, but in theory it should work...
</fingers crossed>

private function blah(rst.bookmark)
   'check rec @ rst.bookmark,
   'perform action accordingly
   'locate bookmark of next record to be checked

   blah(rst.bookmark)
end function