3021 no current record (1 Viewer)

supmktg

Registered User.
Local time
Today, 12:11
Joined
Mar 25, 2002
Messages
360
I'm trying to build a string for a report that contains a summary of a combination of service description and teeth for a group of records.

ie)
Record , Service , Tooth
Record1 , Examine , 1
Record2 , Examine , 2
Record3 , Examine , 3
Record4 , Fix , 4
Record5 , Fix , 5
Record6 , Fix , 6

strDescr = "Examine(1),(2),(3), Fix(4),(5),(6)"

Can someone help me figure out what I'm doing wrong, I'm getting a 3021 error, no current record when I run the following function as part of a query that has records?

Or, is there a simpler way of doing this?
Code:
Function strVisitPurp2(strSSN As String, dteAssign As Date)

Dim rst As DAO.Recordset
Dim strSQL As String
Dim strDescr As String, strBuild As String, strTooth As String, strHold As String, strHoldTooth As String

   On Error GoTo strVisitPurp2_Error

strSQL = "Select * From qryVisitPurp "
strSQL = strSQL & "Where [SSN] ='" & strSSN & "' and [DateAssigned] =#" & dteAssign & "#"
strSQL = strSQL & " ORDER BY VisitPurp ;"

Set rst = CurrentDb.OpenRecordset(strSQL)

    ' Build beginning of Description string
    strDescr = rst!VisitPurp
    ' set variable to compare later for duplicate descriptions
    strHold = rst!VisitPurp
    ' if the record includes a tooth add it to the string
    If Not IsNull(rst!Tooth) Then
    strTooth = rst!Tooth
    ' set variable to compare later for duplicate tooth
    strHoldTooth = rst!Tooth
    strDescr = strDescr & "(" & strTooth & ")"
    End If
    
    ' continue building thru the rest of the records
    Do Until rst.EOF
    ' set variable to add to description
    strBuild = rst!VisitPurp
    ' check if already part of description
    If strBuild = strHold Then
        ' add only tooth if descrption is there and the record includes a tooth
        If Not IsNull(rst!Tooth) Then
        ' reset variable to compare later for duplicate descriptions
        strTooth = rst!Tooth
            If strTooth <> strHoldTooth Then
            strDescr = strDescr & ",(" & strTooth & ")"
            End If
        ' reset variable to compare later for duplicate tooth
        strHoldTooth = rst!Tooth
        End If
    Else
    ' if new description then add it
    strDescr = strDescr & ", " & strBuild
        ' if the record includes a tooth add it to the string
        If Not IsNull(rst!Tooth) Then
        ' reset variable to compare later for duplicate descriptions
        strTooth = rst!Tooth
            If strTooth <> strHoldTooth Then
            strDescr = strDescr & ",(" & strTooth & ")"
            End If
        ' reset variable to compare later for duplicate tooth
        strHoldTooth = rst!Tooth
        End If

    End If
    rst.MoveNext

    Loop
    
strVisitPurp2 = strDescr

rst.Close

   On Error GoTo 0
   Exit Function

strVisitPurp2_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure strVisitPurp2 of Module basVisitPurpConc"

End Function

Thanks,
Sup
 

bvan

Registered User.
Local time
Today, 17:11
Joined
Dec 8, 2000
Messages
23
I believe that you will need to put a "rst.movefirst" line in before you start your loop. The recordset is starting out at BOF=true, so you are not on a record yet. Make sure that you test for an empty recordset before moving to any record or it will throw an error if that occurs.
 

Lister

Z Shift
Local time
Tomorrow, 06:11
Joined
Aug 24, 2003
Messages
305
Code:
Dim db As DAO.Database
Dim inCount as Integer

Set db = CurrentDB() 'Assuming you are using the current database
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveLast
rst.MoveFirst
inCount = rst.RecordCount

If inCount > 0 Then 'Check to make sure you have records in the recordset
'The rest of your code

See how you get on :)
 
Last edited:

supmktg

Registered User.
Local time
Today, 12:11
Joined
Mar 25, 2002
Messages
360
I've played around with the code , including your suggestions, and I've got it working!

Thank you very much for your help!
Sup
 

Users who are viewing this thread

Top Bottom