Results 1 to 11 of 11
  1. #1
    BarryH is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    6

    Using two recordsets

    I wish to write the VBA code to:
    1. Search a table identified as Recordset#1 and read two pieces of data from a particular record. [They are locations]
    2. Search for two matching records in a second table identified as Recordset#2 [This is a list of Locations]


    3. Take two pieces of data from each, four in all. [They are the map references of the two locations]
    4. Calculate the airline distance from one location to the other.
    5. Write that value back on the original record in Recordset#1

    I seem to be unable to use two Recordsets at the same time.

    BarryH

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post your effort and we'll try to fix it.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    BarryH is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    6

    My code as asked for

    Quote Originally Posted by BarryH View Post
    I wish to write the VBA code to:
    1. Search a table identified as Recordset#1 and read two pieces of data from a particular record. [They are locations]
    2. Search for two matching records in a second table identified as Recordset#2 [This is a list of Locations]
    3. Take two pieces of data from each, four in all. [They are the map references of the two locations]
    4. Calculate the airline distance from one location to the other.
    5. Write that value back on the original record in Recordset#1

    I seem to be unable to use two Recordsets at the same time.

    BarryH
    Option Compare Database
    Option Explicit
    Dim Msg As String
    Dim sglSectionLength As Single
    Dim rstNode As adodb.Recordset
    Dim rstSection As adodb.Recordset
    Dim sglEastDiff As Single
    Dim sglNorthDiff As Single
    Dim lngGrid(2, 3) As Long
    'First index 1=EndA, 2=EndB
    'Second index 1=EndID,2=Easting, 3= Northing
    Sub OpenTables()

    'open tblNode
    Set rstNode = New adodb.Recordset
    rstNode.ActiveConnection = CurrentProject.Connection
    rstNode.CursorType = adOpenDynamic
    rstNode.Open Source:="tblNode"

    'Open tblSection
    Set rstSection = New adodb.Recordset
    rstSection.ActiveConnection = CurrentProject.Connection
    rstSection.CursorType = adOpenDynamic
    rstSection.Open Source:="tblSection"
    Call SectionLength

    'close both tables
    rstSection.Close
    Set rstSection = Nothing
    rstNode.Close
    Set rstNode = Nothing
    End Sub

    Sub SectionLength()

    'Riffle through all the section records one at a time from first to last and
    'read the NodeIDs of the two ends of any records which show zero length.

    rstSection.MoveFirst
    'This repeats through all the secton records
    Do Until rstSection.EOF
    'This skips over the sectons that aleady show their length
    If rstSection!SectionLength = 0 Then
    'There is a need to calclate this section length
    'First, read the node IDs at the A and B section ends
    lngGrid(1, 1) = rstSection!nodeA
    Msg = "ID of NodeA: " & Str(lngGrid(1, 1))
    MsgBox Msg
    lngGrid(2, 1) = rstSection!nodeB
    Msg = "ID of NodeB " & Str(lngGrid(2, 1))
    MsgBox Msg
    'MapRefs reads eastings and northings of NodA and NodeB
    Call MapRefs
    Call Pythag
    End If
    rstSection.MoveNext
    Loop

    End Sub 'This is the end of the core Sub SectionLength
    Sub MapRefs()
    'Grid references held in an array lngGrid(End,Ref)
    'where End may be: 1=EndA or 2=EndB.
    'and Ref may be: 1=Easting or 2=Northing or 3=Difference
    'Fiist a ForLoop to find Eastings of EndNodes (i=1)
    'and then Northings (i=2)
    Dim intCounter As Integer
    For intCounter = 1 To 2
    'DoLoop searchesing through rstNodes to find, first EndA's easting and northing
    'and then those for EndB.
    rstNode.MoveFirst
    Do Until lngGrid(intCounter, 1) = rstNode!nodeID
    lngGrid(intCounter, 2) = rstNode!easting
    lngGrid(intCounter, 3) = rstNode!northing
    If intCounter = 1 Then
    Msg = "Easting of NodeA: " & Str(lngGrid(intCounter, 2)) & "Northing of nodeA: " & Str(lngGrid(intCounter, 3))
    MsgBox Msg
    Else
    Msg = "Easting of NodeB: " & Str(lngGrid(intCounter, 2)) & "Northing of nodeB: " & Str(lngGrid(intCounter, 3))
    MsgBox Msg
    Exit Do 'Eastings and northings have been found for both end nodes
    End If
    rstNode.MoveNext
    If rstNode.EOF Then rstNode.MoveFirst 'and continue searching nodes
    Loop 'riffling through nodes
    Next intCounter
    End Sub

    Sub Pythag()
    'Now calculate the section length
    'the diff in eastings
    sglEastDiff = lngGrid(1, 2) - lngGrid(2, 2)
    'and in northings
    sglNorthDiff = lngGrid(1, 3) - lngGrid(2, 3)
    'Now square the differences
    sglEastDiff = sglEastDiff ^ 2
    sglNorthDiff = sglNorthDiff ^ 2
    sglSectionLength = sglEastDiff + sglNorthDiff
    sglSectionLength = sglSectionLength ^ 0.5
    Msg = "Section Length = " & Str(sglSectionLength)
    rstSection!SectionLength = sglSectionLength
    End Sub

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Can you not do this using a query?

    Please use code tags when posting VBA code.

  5. #5
    BarryH is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    6

    Answer to questions

    Quote Originally Posted by aytee111 View Post
    Can you not do this using a query?

    Please use code tags when posting VBA code.
    I do not know what a 'code tag' is

    I expect I could do it with a query (but I don't know how)

    But I want to do it with VBA because I want to extend the code later on to do more complex things,
    Am I asking too much of ADO or, perhaps of ACCESS?

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Here's the code with indenting and code tags:

    Code:
    Option Compare DatabaseOption Explicit
    Dim Msg                       As String
    Dim sglSectionLength          As Single
    Dim rstNode                   As adodb.Recordset
    Dim rstSection                As adodb.Recordset
    Dim sglEastDiff               As Single
    Dim sglNorthDiff              As Single
    Dim lngGrid(2, 3)             As Long
    'First index 1=EndA, 2=EndB
    'Second index 1=EndID,2=Easting, 3= Northing
    Sub OpenTables()
    
    
        'open tblNode
        Set rstNode = New adodb.Recordset
        rstNode.ActiveConnection = CurrentProject.Connection
        rstNode.CursorType = adOpenDynamic
        rstNode.Open Source:="tblNode"
    
    
        'Open tblSection
        Set rstSection = New adodb.Recordset
        rstSection.ActiveConnection = CurrentProject.Connection
        rstSection.CursorType = adOpenDynamic
        rstSection.Open Source:="tblSection"
        Call SectionLength
    
    
        'close both tables
        rstSection.Close
        Set rstSection = Nothing
        rstNode.Close
        Set rstNode = Nothing
    End Sub
    
    
    Sub SectionLength()
    
    
        'Riffle through all the section records one at a time from first to last and
        'read the NodeIDs of the two ends of any records which show zero length.
    
    
        rstSection.MoveFirst
        'This repeats through all the secton records
        Do Until rstSection.EOF
            'This skips over the sectons that aleady show their length
            If rstSection!SectionLength = 0 Then
                'There is a need to calclate this section length
                'First, read the node IDs at the A and B section ends
                lngGrid(1, 1) = rstSection!nodeA
                Msg = "ID of NodeA: " & Str(lngGrid(1, 1))
                MsgBox Msg
                lngGrid(2, 1) = rstSection!nodeB
                Msg = "ID of NodeB " & Str(lngGrid(2, 1))
                MsgBox Msg
                'MapRefs reads eastings and northings of NodA and NodeB
                Call MapRefs
                Call Pythag
            End If
            rstSection.MoveNext
        Loop
    
    
    End Sub    'This is the end of the core Sub SectionLength
    Sub MapRefs()
        'Grid references held in an array lngGrid(End,Ref)
        'where End may be: 1=EndA or 2=EndB.
        'and Ref may be: 1=Easting or 2=Northing or 3=Difference
        'Fiist a ForLoop to find Eastings of EndNodes (i=1)
        'and then Northings (i=2)
        Dim intCounter            As Integer
        For intCounter = 1 To 2
            'DoLoop searchesing through rstNodes to find, first EndA's easting and northing
            'and then those for EndB.
            rstNode.MoveFirst
            Do Until lngGrid(intCounter, 1) = rstNode!nodeID
                lngGrid(intCounter, 2) = rstNode!easting
                lngGrid(intCounter, 3) = rstNode!northing
                If intCounter = 1 Then
                    Msg = "Easting of NodeA: " & Str(lngGrid(intCounter, 2)) & "Northing of nodeA: " & Str(lngGrid(intCounter, 3))
                    MsgBox Msg
                Else
                    Msg = "Easting of NodeB: " & Str(lngGrid(intCounter, 2)) & "Northing of nodeB: " & Str(lngGrid(intCounter, 3))
                    MsgBox Msg
                    Exit Do    'Eastings and northings have been found for both end nodes
                End If
                rstNode.MoveNext
                If rstNode.EOF Then rstNode.MoveFirst    'and continue searching nodes
            Loop    'riffling through nodes
        Next intCounter
    End Sub
    
    
    Sub Pythag()
        'Now calculate the section length
        'the diff in eastings
        sglEastDiff = lngGrid(1, 2) - lngGrid(2, 2)
        'and in northings
        sglNorthDiff = lngGrid(1, 3) - lngGrid(2, 3)
        'Now square the differences
        sglEastDiff = sglEastDiff ^ 2
        sglNorthDiff = sglNorthDiff ^ 2
        sglSectionLength = sglEastDiff + sglNorthDiff
        sglSectionLength = sglSectionLength ^ 0.5
        Msg = "Section Length = " & Str(sglSectionLength)
        rstSection!SectionLength = sglSectionLength
    End Sub
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I can't visualize the data so I'm not clear on what the code is doing. From your description I'd expect something like:

    Code:
    Open recordset on first table
    Start loop of first table
      Open recordset on second table, using criteria from first recordset
         do what needs doing
      Loop second recordset if appropriate
    Loop first recordset
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    BarryH is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    6
    Quote Originally Posted by BarryH View Post
    I do not know what a 'code tag' is

    I expect I could do it with a query (but I don't know how)

    But I want to do it with VBA because I want to extend the code later on to do more complex things,
    Am I asking too much of ADO or, perhaps of ACCESS?
    .
    ______________________________________

    PBaldy

    Thanks for your advice
    It seems that tags are just comment lines??
    Perhaps it would help if I gave some idea of what this is all about:

    Recordset#1 is a table of lengths of road. Each length has a node at either end.
    Recordset#2 is a table of these nodes.

    The two tables together describe a road network.
    Each is node is located by its map reference consisting of two pieces of data:
    first, the distance to the east measured from a line of longitude.
    second, the distance to the north from a line of latitude.
    your suggestion, I think, assumes that I trying to engineer a loop concerned with Recordset#2 inside another loop concerned with Recordset #1 But what I want to do is to use the two record sets alternately whilst keeping them both open continuously. This implies Opening them both, using them both simultaneously and then closing them both together. The alternative would seem to be
    1. Open set#1: the road lengths
    2. Read the IDs of the two nodes, one at each end.
    3. Close set#1
    4. Open set#2: The nodes
    5. Seek the first node ID and read its easting and nothing
    6. Seek the second node ID and read its easting and nothing
    7. Close set#2
    8. Calculate the road length using Pythagrus
    9. Reopen set#1 and write the road length to the correct record
    10. Reclose Set#2
    And repeat that sequence a few hundred times using a Do loop with three recordset openings and three closings for every record in set#1
    It seems very clumsy.

  9. #9
    BarryH is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    6
    Is my problem unresolvable??

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I wouldn't say that, but I haven't had time to try and digest what you're trying to do. Perhaps I'm having brain cramps.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    BarryH is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    6
    Quote Originally Posted by pbaldy View Post
    I wouldn't say that, but I haven't had time to try and digest what you're trying to do. Perhaps I'm having brain cramps.
    Thanks for your reply. Brain cramps are very common amongst politicians of all countries at the moment. I hope your cramps are not a symptom of a pandemic

Please reply to this thread with any new information or opinions.

Similar Threads

  1. About recordsets
    By Lou_Reed in forum Access
    Replies: 4
    Last Post: 11-10-2016, 11:20 AM
  2. Do Loop with 2 different recordsets
    By jamarogers in forum Programming
    Replies: 4
    Last Post: 09-25-2013, 04:21 PM
  3. DAO Recordsets vs. Action Queries
    By Paul H in forum Programming
    Replies: 2
    Last Post: 05-16-2012, 07:02 AM
  4. Add records using recordsets
    By mrfixit1170 in forum Programming
    Replies: 12
    Last Post: 05-15-2012, 09:29 AM
  5. Recordsets, add new, DAO and ADO
    By Arca in forum Programming
    Replies: 1
    Last Post: 04-07-2008, 03:57 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums