Getting Error "End Select without Select Case" (1 Viewer)

B

bconner

Registered User.
Local time
Today, 02:01
Joined
Dec 22, 2008
Messages
183
Hello,

When I run the below code I am getting the error "End Select without Select Case" I figured it might be because I have the "End Select" before the "End With" however when I move the "End Select" after the "End With" I get the error "Loop Without Do". I am stuck!

Any help is greatly appreciated....


Code: 
Private Sub cmd_Update_Conditional_Codes_Click()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Frmt_Conditional_Diagnosis_Data", dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
 rs.MoveNext
 
  Do Until rs.EOF
 
   On Error Resume Next
    Set rs2 = db.OpenRecordset("Diagnosis_Data_Updated_With_HCC", dbOpenDynaset)
     With rs2
 
   Select Case rs![DIAG_CD] 'Take the diagnosis codes and map to the appropriate HCC code based on conditions
 
     Case "1940", "20400", "20401", "20402", "20600", "20601", "20602", "20700", "20701", "20702", "20800", "20801", "20802" And rs![Age_At_Diag] < 18
         strAgeCond = "age_last < 18"
         strICD9 = rs![DIAG_CD]
         strGender = "N/A"
         strWhere = "Age_Last = '" & strAgeCond & "' and Diagnosis_Code = '" & strICD9 & "' and Gender = '" & strGender & "'"
         strHCC = DLookup("HCC", "Diagnosis_Codes", strWhere)
         strAdditionalHCC = DLookup("Additional_HCC", "Diagnosis_Codes", strWhere)
 
 
         .AddNew
            rs2!REV_OPT_CLM_KEY = rs!REV_OPT_CLM_KEY
            rs2!REV_OPT_DIAG_SQNC_NBR = rs!REV_OPT_DIAG_SQNC_NBR
            rs2!OWNG_MBR_KEY = rs!OWNG_MBR_KEY
            rs2!DIAG_CD = rs!DIAG_CD
            rs2!REV_OPT_DIAG_VRSN_NBR = rs!REV_OPT_DIAG_VRSN_NBR
            rs2!REV_OPT_SRC_ID = rs!REV_OPT_SRC_ID
            rs2!REV_OPT_DIAG_INFNT_ONLY_IND_CD = rs!REV_OPT_DIAG_INFNT_ONLY_IND_CD
            rs2!REV_OPT_RCRD_TYPE_CD = rs!REV_OPT_RCRD_TYPE_CD
            rs2!RVW_IND_CD = rs!RVW_IND_CD
            rs2!RETRACTED_IND_CD = rs!RETRACTED_IND_CD
            rs2!REV_OPT_DIAG_STRT_DTM = rs!REV_OPT_DIAG_STRT_DTM
            rs2!REV_OPT_DIAG_END_DTM = rs!REV_OPT_DIAG_END_DTM
            rs2!VNDR_PAT_CNTRL_NBR = rs!VNDR_PAT_CNTRL_NBR
            rs2!LAST_UPDT_USER_ID = rs!LAST_UPDT_USER_ID
            rs2!REV_OPT_LOAD_LOG_KEY = rs!REV_OPT_LOAD_LOG_KEY
            rs2!FNC_SOR_CD = rs!FNC_SOR_CD
            rs2!RCRD_STTS_CD = rs!RCRD_STTS_CD
            rs2!LOAD_LOG_KEY = rs!LOAD_LOG_KEY
            rs2!SOR_DTM = rs!SOR_DTM
            rs2!CRCTD_LOAD_LOG_KEY = rs!CRCTD_LOAD_LOG_KEY
            rs2!UPDTD_LOAD_LOG_KEY = rs!UPDTD_LOAD_LOG_KEY
            rs2!MBR_KEY = rs!MBR_KEY
            rs2!CLM_SOR_CD = rs!CLM_SOR_CD
            rs2!CLM_NBR = rs!CLM_NBR
            rs2!REV_OPT_BNFT_YEAR_NBR = rs!REV_OPT_BNFT_YEAR_NBR
            rs2!CLM_REV_OPT_DIAG_VRSN_NBR = rs!CLM_REV_OPT_DIAG_VRSN_NBR
            rs2!CLM_LOAD_ACPTNC_CD = rs!CLM_LOAD_ACPTNC_CD
            rs2!CREATD_CLNDR_DTM = rs!CREATD_CLNDR_DTM
            rs2!LAST_UPDT_DTM = rs!LAST_UPDT_DTM
            rs2!FRST_NM = rs!FRST_NM
            rs2!LAST_NM = rs!LAST_NM
            rs2!BRTH_DT = rs!BRTH_DT
            rs2!HC_ID = rs!HC_ID
            rs2!SRC_MBR_CD = rs!SRC_MBR_CD
            rs2!GNDR_CD = rs!GNDR_CD
            rs2!MBRSHP_SOR_CD = rs!MBRSHP_SOR_CD
            rs2!HCC = strHCC
            rs2!HCC_ADTL = strAdditionalHCC
         .Update
 
     Case "4910", "4911", "49120", "49121", "49122", "4918", "4919", "49320", "49321", "49322" And rs![Age_At_Diag] < 18
         strAgeCond = "age_last < 18"
         strICD9 = rs![DIAG_CD]
         strGender = "N/A"
         strWhere = "Age_Last = '" & strAgeCond & "' and Diagnosis_Code = '" & strICD9 & "' and Gender = '" & strGender & "'"
         strHCC = DLookup("HCC", "Diagnosis_Codes", strWhere)
         strAdditionalHCC = DLookup("Additional_HCC", "Diagnosis_Codes", strWhere)
 
         With rs2
         .AddNew
            rs2!REV_OPT_CLM_KEY = rs!REV_OPT_CLM_KEY
            rs2!REV_OPT_DIAG_SQNC_NBR = rs!REV_OPT_DIAG_SQNC_NBR
            rs2!OWNG_MBR_KEY = rs!OWNG_MBR_KEY
            rs2!DIAG_CD = rs!DIAG_CD
            rs2!REV_OPT_DIAG_VRSN_NBR = rs!REV_OPT_DIAG_VRSN_NBR
            rs2!REV_OPT_SRC_ID = rs!REV_OPT_SRC_ID
            rs2!REV_OPT_DIAG_INFNT_ONLY_IND_CD = rs!REV_OPT_DIAG_INFNT_ONLY_IND_CD
            rs2!REV_OPT_RCRD_TYPE_CD = rs!REV_OPT_RCRD_TYPE_CD
            rs2!RVW_IND_CD = rs!RVW_IND_CD
            rs2!RETRACTED_IND_CD = rs!RETRACTED_IND_CD
            rs2!REV_OPT_DIAG_STRT_DTM = rs!REV_OPT_DIAG_STRT_DTM
            rs2!REV_OPT_DIAG_END_DTM = rs!REV_OPT_DIAG_END_DTM
            rs2!VNDR_PAT_CNTRL_NBR = rs!VNDR_PAT_CNTRL_NBR
            rs2!LAST_UPDT_USER_ID = rs!LAST_UPDT_USER_ID
            rs2!REV_OPT_LOAD_LOG_KEY = rs!REV_OPT_LOAD_LOG_KEY
            rs2!FNC_SOR_CD = rs!FNC_SOR_CD
            rs2!RCRD_STTS_CD = rs!RCRD_STTS_CD
            rs2!LOAD_LOG_KEY = rs!LOAD_LOG_KEY
            rs2!SOR_DTM = rs!SOR_DTM
            rs2!CRCTD_LOAD_LOG_KEY = rs!CRCTD_LOAD_LOG_KEY
            rs2!UPDTD_LOAD_LOG_KEY = rs!UPDTD_LOAD_LOG_KEY
            rs2!MBR_KEY = rs!MBR_KEY
            rs2!CLM_SOR_CD = rs!CLM_SOR_CD
            rs2!CLM_NBR = rs!CLM_NBR
            rs2!REV_OPT_BNFT_YEAR_NBR = rs!REV_OPT_BNFT_YEAR_NBR
            rs2!CLM_REV_OPT_DIAG_VRSN_NBR = rs!CLM_REV_OPT_DIAG_VRSN_NBR
            rs2!CLM_LOAD_ACPTNC_CD = rs!CLM_LOAD_ACPTNC_CD
            rs2!CREATD_CLNDR_DTM = rs!CREATD_CLNDR_DTM
            rs2!LAST_UPDT_DTM = rs!LAST_UPDT_DTM
            rs2!FRST_NM = rs!FRST_NM
            rs2!LAST_NM = rs!LAST_NM
            rs2!BRTH_DT = rs!BRTH_DT
            rs2!HC_ID = rs!HC_ID
            rs2!SRC_MBR_CD = rs!SRC_MBR_CD
            rs2!GNDR_CD = rs!GNDR_CD
            rs2!MBRSHP_SOR_CD = rs!MBRSHP_SOR_CD
            rs2!HCC = strHCC
            rs2!HCC_ADTL = strAdditionalHCC
         .Update
 
 
 
   End Select
     End With
     rs2.Close
     Set rs2 = Nothing
 
 
   rs.MoveNext
 
  Loop
 
 
Else
MsgBox "There are no Records in the recordset"
End If
 
rs.Close
Set rs = Nothing
End Sub
 
Brianwarnock

Brianwarnock

Retired
Local time
Today, 08:01
Joined
Jun 2, 2003
Messages
12,701
I think that your End With is in the wrong place, the With starts within the SelectCase but ends outside of it.

Brian
 
B

bconner

Registered User.
Local time
Today, 02:01
Joined
Dec 22, 2008
Messages
183
Hey Brian thanks for the tip I finally got it to work.
I opened rs2 and placed the end with and recordset cleanup outside of the loop (below is the revised).

Thanks again for the pointer....

Code: 
Private Sub cmd_Update_Conditional_Codes_Click()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strSQL As String
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Frmt_Conditional_Diagnosis_Data", dbOpenDynaset)
Set rs2 = db.OpenRecordset("Diagnosis_Data_Updated_With_HCC", dbOpenDynaset)
With rs2
If Not (rs.BOF And rs.EOF) Then
 rs.MoveNext
  
  Do Until rs.EOF
  
   On Error Resume Next
    
     
   Select Case rs![DIAG_CD] 'Take the diagnosis codes and map to the appropriate HCC code based on conditions
     
     Case "1940", "20400", "20401", "20402", "20600", "20601", "20602", "20700", "20701", "20702", "20800", "20801", "20802" And rs![Age_At_Diag] < 18
         
         strAgeCond = "age_last < 18"
         strICD9 = rs![DIAG_CD]
         strGender = "N/A"
         strWhere = "Age_Last = '" & strAgeCond & "' and Diagnosis_Code = '" & strICD9 & "' and Gender = '" & strGender & "'"
         strHCC = DLookup("HCC", "Diagnosis_Codes", strWhere)
         strAdditionalHCC = DLookup("Additional_HCC", "Diagnosis_Codes", strWhere)
             
         .AddNew
            rs2!REV_OPT_CLM_KEY = rs!REV_OPT_CLM_KEY
            rs2!REV_OPT_DIAG_SQNC_NBR = rs!REV_OPT_DIAG_SQNC_NBR
            rs2!OWNG_MBR_KEY = rs!OWNG_MBR_KEY
            rs2!DIAG_CD = rs!DIAG_CD
            rs2!REV_OPT_DIAG_VRSN_NBR = rs!REV_OPT_DIAG_VRSN_NBR
            rs2!REV_OPT_SRC_ID = rs!REV_OPT_SRC_ID
            rs2!REV_OPT_DIAG_INFNT_ONLY_IND_CD = rs!REV_OPT_DIAG_INFNT_ONLY_IND_CD
            rs2!REV_OPT_RCRD_TYPE_CD = rs!REV_OPT_RCRD_TYPE_CD
            rs2!RVW_IND_CD = rs!RVW_IND_CD
            rs2!RETRACTED_IND_CD = rs!RETRACTED_IND_CD
            rs2!REV_OPT_DIAG_STRT_DTM = rs!REV_OPT_DIAG_STRT_DTM
            rs2!REV_OPT_DIAG_END_DTM = rs!REV_OPT_DIAG_END_DTM
            rs2!VNDR_PAT_CNTRL_NBR = rs!VNDR_PAT_CNTRL_NBR
            rs2!LAST_UPDT_USER_ID = rs!LAST_UPDT_USER_ID
            rs2!REV_OPT_LOAD_LOG_KEY = rs!REV_OPT_LOAD_LOG_KEY
            rs2!FNC_SOR_CD = rs!FNC_SOR_CD
            rs2!RCRD_STTS_CD = rs!RCRD_STTS_CD
            rs2!LOAD_LOG_KEY = rs!LOAD_LOG_KEY
            rs2!SOR_DTM = rs!SOR_DTM
            rs2!CRCTD_LOAD_LOG_KEY = rs!CRCTD_LOAD_LOG_KEY
            rs2!UPDTD_LOAD_LOG_KEY = rs!UPDTD_LOAD_LOG_KEY
            rs2!MBR_KEY = rs!MBR_KEY
            rs2!CLM_SOR_CD = rs!CLM_SOR_CD
            rs2!CLM_NBR = rs!CLM_NBR
            rs2!REV_OPT_BNFT_YEAR_NBR = rs!REV_OPT_BNFT_YEAR_NBR
            rs2!CLM_REV_OPT_DIAG_VRSN_NBR = rs!CLM_REV_OPT_DIAG_VRSN_NBR
            rs2!CLM_LOAD_ACPTNC_CD = rs!CLM_LOAD_ACPTNC_CD
            rs2!CREATD_CLNDR_DTM = rs!CREATD_CLNDR_DTM
            rs2!LAST_UPDT_DTM = rs!LAST_UPDT_DTM
            rs2!FRST_NM = rs!FRST_NM
            rs2!LAST_NM = rs!LAST_NM
            rs2!BRTH_DT = rs!BRTH_DT
            rs2!HC_ID = rs!HC_ID
            rs2!SRC_MBR_CD = rs!SRC_MBR_CD
            rs2!GNDR_CD = rs!GNDR_CD
            rs2!MBRSHP_SOR_CD = rs!MBRSHP_SOR_CD
            rs2!HCC = strHCC
            rs2!HCC_ADTL = strAdditionalHCC
         .Update
          
     Case "4910", "4911", "49120", "49121", "49122", "4918", "4919", "49320", "49321", "49322" And rs![Age_At_Diag] < 18
         
         strAgeCond = "age_last < 18"
         strICD9 = rs![DIAG_CD]
         strGender = "N/A"
         strWhere = "Age_Last = '" & strAgeCond & "' and Diagnosis_Code = '" & strICD9 & "' and Gender = '" & strGender & "'"
         strHCC = DLookup("HCC", "Diagnosis_Codes", strWhere)
         strAdditionalHCC = DLookup("Additional_HCC", "Diagnosis_Codes", strWhere)
            
         .AddNew
            rs2!REV_OPT_CLM_KEY = rs!REV_OPT_CLM_KEY
            rs2!REV_OPT_DIAG_SQNC_NBR = rs!REV_OPT_DIAG_SQNC_NBR
            rs2!OWNG_MBR_KEY = rs!OWNG_MBR_KEY
            rs2!DIAG_CD = rs!DIAG_CD
            rs2!REV_OPT_DIAG_VRSN_NBR = rs!REV_OPT_DIAG_VRSN_NBR
            rs2!REV_OPT_SRC_ID = rs!REV_OPT_SRC_ID
            rs2!REV_OPT_DIAG_INFNT_ONLY_IND_CD = rs!REV_OPT_DIAG_INFNT_ONLY_IND_CD
            rs2!REV_OPT_RCRD_TYPE_CD = rs!REV_OPT_RCRD_TYPE_CD
            rs2!RVW_IND_CD = rs!RVW_IND_CD
            rs2!RETRACTED_IND_CD = rs!RETRACTED_IND_CD
            rs2!REV_OPT_DIAG_STRT_DTM = rs!REV_OPT_DIAG_STRT_DTM
            rs2!REV_OPT_DIAG_END_DTM = rs!REV_OPT_DIAG_END_DTM
            rs2!VNDR_PAT_CNTRL_NBR = rs!VNDR_PAT_CNTRL_NBR
            rs2!LAST_UPDT_USER_ID = rs!LAST_UPDT_USER_ID
            rs2!REV_OPT_LOAD_LOG_KEY = rs!REV_OPT_LOAD_LOG_KEY
            rs2!FNC_SOR_CD = rs!FNC_SOR_CD
            rs2!RCRD_STTS_CD = rs!RCRD_STTS_CD
            rs2!LOAD_LOG_KEY = rs!LOAD_LOG_KEY
            rs2!SOR_DTM = rs!SOR_DTM
            rs2!CRCTD_LOAD_LOG_KEY = rs!CRCTD_LOAD_LOG_KEY
            rs2!UPDTD_LOAD_LOG_KEY = rs!UPDTD_LOAD_LOG_KEY
            rs2!MBR_KEY = rs!MBR_KEY
            rs2!CLM_SOR_CD = rs!CLM_SOR_CD
            rs2!CLM_NBR = rs!CLM_NBR
            rs2!REV_OPT_BNFT_YEAR_NBR = rs!REV_OPT_BNFT_YEAR_NBR
            rs2!CLM_REV_OPT_DIAG_VRSN_NBR = rs!CLM_REV_OPT_DIAG_VRSN_NBR
            rs2!CLM_LOAD_ACPTNC_CD = rs!CLM_LOAD_ACPTNC_CD
            rs2!CREATD_CLNDR_DTM = rs!CREATD_CLNDR_DTM
            rs2!LAST_UPDT_DTM = rs!LAST_UPDT_DTM
            rs2!FRST_NM = rs!FRST_NM
            rs2!LAST_NM = rs!LAST_NM
            rs2!BRTH_DT = rs!BRTH_DT
            rs2!HC_ID = rs!HC_ID
            rs2!SRC_MBR_CD = rs!SRC_MBR_CD
            rs2!GNDR_CD = rs!GNDR_CD
            rs2!MBRSHP_SOR_CD = rs!MBRSHP_SOR_CD
            rs2!HCC = strHCC
            rs2!HCC_ADTL = strAdditionalHCC
         .Update
         
        
  End Select
   
     
      
   rs.MoveNext
  
  Loop
  

Else
MsgBox "There are no Records in the recordset"
End If
End With
rs.Close
Set rs = Nothing
rs2.Close
Set rs2 = Nothing

End Sub
 
You must log in or register to reply here.

Similar threads

B
Trying to get Case Statement to recognize Multiple conditions
Replies
1
Views
1,779
spikepl
spikepl

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom