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?
Thanks,
Sup
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