Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2014
    Posts
    2

    Unanswered: Recordset 3001 error

    Need some help here. I keep getting error 3001 on this recordset. I can run the SQL in a query and it works fine. I can see the results on dbo_CrewAboard but not from dbo_crew.

    Here is the SQL behind qryCrewOnRequest:
    Code:
    PARAMETERS RequestID Long;
    SELECT dbo_CrewAboard.RequestID, dbo_Crew.ID2, dbo_Crew.Title, dbo_Crew.LastName, dbo_Crew.FirstName, dbo_Crew.CrewGroup
    FROM dbo_CrewAboard INNER JOIN dbo_Crew ON dbo_CrewAboard.CrewID = dbo_Crew.ID2
    WHERE (((dbo_CrewAboard.RequestID)=[RequestID]));
    Here is the part of the code giving me errors:
    Code:
     myRequestID = rst![FVID]
        Set myCrewQuery = db.QueryDefs("qryCrewOnRequest")
        myCrewQuery.Parameters!RequestID = myRequestID
        Set rst2 = myCrewQuery.OpenRecordset(dbOpenDynaset, dbSeeChanges)
        Do While Not rst2.EOF
            If rst2![Title] = "DVR" Then
                Me.txtEMT = rst2![ID2]
            ElseIf rst2![Title] = "NNP" Then
                Me.txtNNP = rst2![ID2]
            ElseIf rst2![Title] = "NRN" Then
                Me.txtNurse = rst2![ID2]
            End If
        Loop
    Error shows up on the first IF statement in the loop.

    In the immediate window, I can print the name:
    Debug.print rst2(1).Name returns "ID2".
    When I try to print the value:
    Debug.print rst2(1).Value I get the '3001' error.
    Last edited by vufltrn; 08-02-14 at 10:22. Reason: Add code tags & info

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Did you try to add watch for rst.Fields(1) instead of trying to print its value? Also, how is dbo_Crew.ID2 defined?
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Its while since ive used a parameter query but you may be confusing the SQL engine with your parameter name being the same as a column in one of the tables. So id suggest renaming the parameter to remove any such scope

    Im just wondering if perhaps this is a symptom of no data being returned. I forget the loop processing logic but you may need to treat any empty recordset differently.


    if the error is being returned on the if statement then its more likely to be a problem with the title

    daft quesrion is it possible that your saved query by not be the same version that you think it is. What you could do is examine the recordsets various properties in the immediate window.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2014
    Posts
    2
    @Sinndho: dbo_Crew.ID2 is an AutoNumber long integer. I set watches for rst2![ID2], rst2![Title] and rst2![RequestID]. At the 'Set rst2=....' statement, [RequestID] is defined, but the other two (both based off dbo_Crew) show nothing in the collapsed view. Expanded view attached.


    @healdem: I changed the parameter name, and am using a parameter that returns results in the actual query and still have the same problems.

    I have a work-around that is functional but really want to understand why this doesn't work. Here's the work-around that is functional:

    Code:
    myRequestID = rst![FVID]
        Set myCrewQuery = db.QueryDefs("qryCrewOnRequest")
        myCrewQuery.Parameters!FVRequestID = myRequestID
        Set rst2 = myCrewQuery.OpenRecordset(dbOpenDynaset, dbSeeChanges)
        Do While Not rst2.EOF
            myTitle = DLookup("[Title]", "dbo_crew", "[ID2]=" & rst2![CrewID])
            If myTitle = "DVR" Then
            'rst2![Title] = "DVR" Then
                Me.txtEMT = rst2![CrewID]
            ElseIf myTitle = "NNP" Then
                Me.txtNNP = rst2![CrewID]
            ElseIf myTitle = "NRN" Then
                Me.txtNurse = rst2![CrewID]
            End If
            rst2.MoveNext
        Loop
    Just in case there is something in the complete code I am missing, here is the complete sub:
    Code:
    Private Sub cmdImport_Click()
    On Error GoTo Error_Sub
    Dim FVNumber As String, mySQL As String, mySQL2 As String, mySQL3 As String, mySQLFacility As String
    Dim myMRNSQL As String, myErrorString As String, myTitle As String
    Dim myRequestID As Long
    Dim db As Database
    Dim rst As DAO.Recordset, rst2 As DAO.Recordset, rst3 As DAO.Recordset, rstFacility As DAO.Recordset
    Dim rstMRN As DAO.Recordset
    Dim myPassThrough As QueryDef, myCrewQuery As QueryDef
    
        Set db = CurrentDb
        FVNumber = Me.txtTransportNumber
        mySQL = "SELECT " & _
                    "[Completed_Requests].[Number] as [FVNumber], " & _
                    "[Completed_Requests].[ID] as [FVID], " & _
                    "[Completed_Requests].[Date] as [RequestDate], " & _
                    "[Completed_Requests].[Disposition], " & _
                    "[Completed_Requests].[AirCraftName]," & _
                    "[Completed_Requests].[AirCraftNNumber]," & _
                    "[Completed_Requests].[CallType]," & _
                    "[Completed_Requests].[ReqAgency]," & _
                    "[Completed_Requests].[ReqAgencyID]," & _
                    "[Completed_Requests].[ReqDoctor]," & _
                    "[Completed_Requests].[RecAgency]," & _
                    "[Completed_Requests].[RecAgencyID]," & _
                    "[Completed_Requests].[RecDoctor]," & _
                    "(SELECT TOP 1 Value FROM RequestTime WHERE RequestID = Completed_Requests.ID AND Completed = 1 AND " & _
                        "TimeID = (SELECT ID FROM RequestTimeType WHERE Name = 'Launch Page')) as 'PagedTime'," & _
                    "(SELECT TOP 1 Depart FROM ParseRouteString(Route)ORDER BY RouteIndex ASC )as 'LiftoffTime', " & _
                    "(SELECT TOP 1 Arrive FROM ParseRouteString(Route) where ShortName = 'Req.')as 'ArriveReqTime', " & _
                    "(SELECT TOP 1 Depart FROM ParseRouteString(Route) where ShortName = 'Req.')as 'DepartReqTime', " & _
                    "(SELECT TOP 1 Arrive FROM ParseRouteString(Route) where ShortName = 'Rec.')as 'ArriveRecTime', " & _
                    "(SELECT TOP 1 Depart FROM ParseRouteString(Route) where ShortName = 'Rec.') as 'DepartRecTime' " & _
                "FROM [dbo].[Completed_Requests] [Completed_Requests] " & _
                "LEFT JOIN [dbo].[Assets] ON ([Completed_Requests].[AircraftNNumber] = [Assets].[Nnumber]) " & _
                "WHERE [Completed_Requests].[Number] = '" & FVNumber & "'"
        Set myPassThrough = db.CreateQueryDef("qryFVImport")
        myPassThrough.Connect = "ODBC;DSN=FlightVector;DATABASE=FlightVector"
        myPassThrough.SQL = mySQL
        myPassThrough.ReturnsRecords = True
        myPassThrough.Close
        Set rst = db.OpenRecordset("qryFVImport")
        Me.txtReceivingMD = rst![RecDoctor]
        Me.txtTimeOfCall = rst![RequestDate]
        Me.txtDispatched = rst![PagedTime]
        Me.txtEnroute = rst![LiftoffTime]
        Me.txtArrivedReferring = rst![ArriveReqTime]
        Me.txtDepartReferring = rst![DepartReqTime]
        If Not rst![RecAgency] = "Vanderbilt Childrens Hospital" Then
            Me.cboTypeOfTransport = 3
        ElseIf rst![AirCraftName] = "Angel Primary" Then
            Me.cboTypeOfTransport = 1
        Else
            Me.cboTypeOfTransport = 2
        End If
        
        myMRNSQL = "SELECT dbo_Completed_Patients.MedicalRecordNum, dbo_Completed_Patients.RequestID " & _
                    "FROM dbo_Completed_Patients " & _
                    "WHERE (((dbo_Completed_Patients.RequestID)=" & rst![FVID] & "));"
        Set rstMRN = db.OpenRecordset(myMRNSQL)
        Me.txtMRN = rstMRN![MedicalRecordNum]
        
        mySQLFacility = "SELECT tblHospitals.ID FROM tblHospitals"
        Set rstFacility = db.OpenRecordset(mySQLFacility)
        With rstFacility
            .FindFirst "ID=" & rst![ReqAgencyID]
            If .NoMatch Then
                .AddNew
                    .Fields("ID") = rst![ReqAgencyID]
                .Update
            End If
            .FindFirst "ID=" & rst![RecAgencyID]
            If .NoMatch Then
                .AddNew
                    .Fields("ID") = rst![RecAgencyID]
                .Update
            End If
        End With
        
        Me.txtReferring = rst![ReqAgency]
        Me.txtReferringMD = rst![ReqDoctor]
        Me.txtReceiving = rst![RecAgency]
        Me.txtReceivingMD = rst![RecDoctor]
    
        myRequestID = rst![FVID]
        mySQL2 = "SELECT dbo_CrewAboard.RequestID, dbo_Crew.ID2, dbo_Crew.Title, " & _
                    "dbo_Crew.LastName, dbo_Crew.FirstName, dbo_Crew.CrewGroup " & _
                 "FROM dbo_CrewAboard INNER JOIN dbo_Crew ON dbo_CrewAboard.CrewID = dbo_Crew.ID2 " & _
                 "WHERE dbo_CrewAboard.RequestID = " & myRequestID
        'Set rst2 = db.OpenRecordset(mySQL2)
        Set myCrewQuery = db.QueryDefs("qryCrewOnRequest")
        myCrewQuery.Parameters!FVRequestID = myRequestID
        Set rst2 = myCrewQuery.OpenRecordset(dbOpenDynaset, dbSeeChanges)
        Do While Not rst2.EOF
            myTitle = DLookup("[Title]", "dbo_crew", "[ID2]=" & rst2![CrewID])
            If myTitle = "DVR" Then
            'rst2![Title] = "DVR" Then
                Me.txtEMT = rst2![CrewID]
            ElseIf myTitle = "NNP" Then
                Me.txtNNP = rst2![CrewID]
            ElseIf myTitle = "NRN" Then
                Me.txtNurse = rst2![CrewID]
            End If
            rst2.MoveNext
        Loop
        
        mySQL3 = "SELECT " & _
                    "dbo_Completed_Patients.Weight, " & _
                    "dbo_Completed_Patients.Age, " & _
                    "dbo_Completed_Patients.MedicalRecordNum " & _
                "FROM dbo_Completed_Patients " & _
                "WHERE dbo_Completed_Patients.Mission = '" & FVNumber & "'"
        Set rst3 = db.OpenRecordset(mySQL3)
        Me.txtAge = rst3![Age]
        Me.txtWeight = rst3![Weight]
        
    Exit_Sub:
        If Not myPassThrough Is Nothing Then
            myPassThrough.Close
            Set myPassThrough = Nothing
        End If
        
        If Not rst Is Nothing Then
            rst.Close
            Set rst = Nothing
        End If
        
        If Not rst2 Is Nothing Then
            rst2.Close
            Set rst2 = Nothing
        End If
        
        If Not rst3 Is Nothing Then
            rst3.Close
            Set rst3 = Nothing
        End If
        
        If Not rstFacility Is Nothing Then
            rstFacility.Close
            Set rstFacility = Nothing
        End If
        
        If Not rstMRN Is Nothing Then
            rstMRN.Close
            Set rstMRN = Nothing
        End If
        
        db.QueryDefs.Delete "qryFVImport"
        
        Set db = Nothing
          
        Exit Sub
        
    Error_Sub:
        myErrorString = Err.Number & ": " & Err.Description & " Called by: " & FormName
        MsgBox myErrorString
        Resume Next
        Resume
        
    End Sub
    Attached Thumbnails Attached Thumbnails expanded.png  

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by vufltrn View Post
    ... but the other two (both based off dbo_Crew) show nothing in the collapsed view. Expanded view attached.
    Not sure to understand what you mean. There is no attachment to your post.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •