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
rochefcAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.