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....
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