• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

DAO Recordset Dynamic Update - How to Order by Timestamp prior to Update

How can I get this dynamic update to Order by TimeStamp prior to updating.

I have a Many to Many dynamic update that takes information from Table 1
and information from Table 2 for each instance of Table 1 Current and each
instance of Table 2 where Table1Key = Table2Key then updates an XML procedural page.

The following code works fine to update however I need to make sure the update is being done in order by the timestamp field located in table 2

in other words
     for each Table1.current.key the Table2.URL must update by Table2.Timestamp
     ascending to the XML file



Dim GoTu as String
Dim CheckURL as String
Dim rstdoc as DAO.Recordset
Dim rstAssess as DAO.Recordset  
Set rstdoc = CurrentDB.OpenRecordset("Table1", dbOpenDynaset)
Set rstAssess = CurrentDB.OpenRecordset("Table2", dbOpenDynaset)
    With rstdoc  ' --------------------------------------------------------------Open Table 1
        .MoveFirst
        Do While Not .EOF
            If rstdoc!Current = True Then '------------------------------------Look for the current
                                                                                                      record in table 1
                With rstAssess ' --------------------------------------------------Open Table 2
                    Do While Not .EOF
                        GetKey = rstdoc!VBKey '-----------------------------------Look for the specific
                                                                                                      record in table 2 that
                                                                                                      matches table 1
                        If rstAssess!VBKey = GetKey Then
                            GoTu = rstAssess!AssessmentURL
                            CheckURL = Left(GoTu, 4)
                                                                      ' ------------Check to see that it is a valid URL
                            If CheckURL = "http" Then
                                                             ' -----------------------Verify it has not been updated
                                If rstAssess!Updated = False Then
                                                                          '--------------Allow an XML page to execute
                                                                                             based on the URL passed
                                    ShellExecute 0, "open", GoTu, Chr$(0), "", False *
                                    .Edit                         '------------------Change the Update flag to True
                                    rstAssess!Updated = True
                                    .Update
                                End If
                            End If
                        End If
                    .MoveNext
                    Loop
                End With
            End If
        .MoveNext
        Loop
    End With
rstAssess.Close
rstdoc.Close
0
rochefc
Asked:
rochefc
  • 2
  • 2
  • 2
1 Solution
 
Arthur_WoodCommented:
>>"I need to make sure the update is being done in order by the timestamp field located in table 2"

Why do you think that you must do the update in a specific order?

AW
0
 
rochefcAuthor Commented:
The updates are creating assessments, The XML link does this assessment automatically based on the parameters passed in the URL.  If the assessment is not done in the order that they were selected, it could Over Assess or Under assess.

For instance if the user choses to clear all previous assessments, then enters new ones, the order that they are updated in becomes critical.
0
 
Arthur_WoodCommented:
the problem that you have is that you are opening both recordsets by simpley opening the table, with no ordering at all.

try this change:

Dim GoTu as String
Dim CheckURL as String
Dim rstdoc as DAO.Recordset
Dim rstAssess as DAO.Recordset  
dim strAssess as String
Set rstdoc = CurrentDB.OpenRecordset("Table1", dbOpenDynaset)

    With rstdoc  ' --------------------------------------------------------------Open Table 1
        .MoveFirst
        Do While Not .EOF
            If rstdoc!Current = True Then '------------------------------------Look for the current
                strAssess = "Select Updated from Table2 where VBKey = '" & rstDoc!VBKey & "' ORDER BY  Table2.Timestamp"
                Set rstAssess = CurrentDB.OpenRecordset( strAssess,dbOpenDynaset)
      ' this will only return the records for the desired VBKey
                With rstAssess ' --------------------------------------------------Open Table 2
                    Do While Not .EOF

                            GoTu = rstAssess!AssessmentURL
                            CheckURL = Left(GoTu, 4)
                                                                      ' ------------Check to see that it is a valid URL
                            If CheckURL = "http" Then
                                                             ' -----------------------Verify it has not been updated
                                If rstAssess!Updated = False Then
                                                                          '--------------Allow an XML page to execute
                                                                                             based on the URL passed
                                    ShellExecute 0, "open", GoTu, Chr$(0), "", False *
                                    .Edit                         '------------------Change the Update flag to True
                                    rstAssess!Updated = True
                                    .Update
                                End If
                            End If
                    .MoveNext
                    Loop
                End With
            End If
        .MoveNext
        Loop
    End With
rstAssess.Close
rstdoc.Close

note that the rstAssess is only opened after a specific rstDoc record has been accessed, so the rstAssess will only ever hold the records that are relevant to the current VBKey, from rstDoc.  The recordset also only pulss the Updated field, rather than ALL of the fields, so the performance should be better.  It sould be further imporved by only pulling those records for which Update is currently FALSE, since those are the only records whose values you are Updating to True.

AW
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jim P.Commented:
Change to this
-------------------------------------------
Dim rstAssess as DAO.Recordset  
Dim SQL as String

SQL = "Select * " & _
          "FROM Table2 " & _
          "ORDER BY TimStampFld"

Set rstdoc = CurrentDB.OpenRecordset("Table1", dbOpenDynaset)
Set rstAssess = CurrentDB.OpenRecordset(SQL, dbOpenDynaset)
0
 
rochefcAuthor Commented:
I figured I would have to base this on a query, I was concerned with the processing speed by adding the extra step, This is a great solution - thanks

I also came up with basing the rstassess on selectquerytable1 instead of directly from  table1.  - basically the same idea, but yours is better because it limits the result set.

0
 
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now