Hello, I need help returning the data from the SQL statement and using the data in my if statement. The sSql should return a numeric value between 1 and 5. Then I would like to use that value in the IF else statement to call up the proper form. Any Ideas how I should structure the varEvent = rs.field statement. Thanks
Private Sub cmdStartEdit_Click()
On Error GoTo Err_cmdStartEdit_Click
Dim rs As dao.Recordset, intl As Integer
Dim varEvent As Variant
Dim stDocName As String
Dim sSql As String
Dim FV As Integer
sSql = "Select top 1 [QC_Results_Table].[formversion] FROM [QC_Results_Table] where [QC_Results_Table].[Case] = " & Me.TXTFindCase.Value
Set rs = CurrentDb.OpenRecordset(sSql)
varEvent = rs.Fields1
If varEvent = 5 Then
stDocName = "V5_QC_Results_Table1"
DoCmd.OpenForm stDocName, , , "QC_Results_Table.case=" & Me.[TXTFindCase].Value
ElseIf varEvent = 4 Then
stDocName = "V4_Supplemental_Review_No_SAR_Filing"
DoCmd.OpenForm stDocName, , , "QC_Results_Table.case=" & Me.[TXTFindCase].Value
ElseIf varEvent = 3 Then
stDocName = "V3_Supplemental_Investigation_SAR_Filing"
DoCmd.OpenForm stDocName, , , "QC_Results_Table.case=" & Me.[TXTFindCase].Value
ElseIf varEvent = 2 Then
stDocName = "V2_New_Investigation_No_SAR_Filing"
DoCmd.OpenForm stDocName, , , "QC_Results_Table.case=" & Me.[TXTFindCase].Value
ElseIf varEvent = 1 Then
stDocName = "V1_New_Investiagtion_Sar_Filing"
DoCmd.OpenForm stDocName, , , "QC_Results_Table.case=" & Me.[TXTFindCase].Value
Else
MsgBox "Enter Another Case Number ", vbInformation, "Warning"
End If
rs.Close
Set rs = Nothing
Exit_cmdStartEdit_Click:
Exit Sub
Private Sub cmdStartEdit_Click()
On Error GoTo Err_cmdStartEdit_Click
Dim rs As dao.Recordset, intl As Integer
Dim varEvent As Variant
Dim stDocName As String
Dim sSql As String
Dim FV As Integer
sSql = "Select top 1 [QC_Results_Table].[formversion] FROM [QC_Results_Table] where [QC_Results_Table].[Case] = " & Me.TXTFindCase.Value
Set rs = CurrentDb.OpenRecordset(sSql)
varEvent = rs.Fields1
If varEvent = 5 Then
stDocName = "V5_QC_Results_Table1"
DoCmd.OpenForm stDocName, , , "QC_Results_Table.case=" & Me.[TXTFindCase].Value
ElseIf varEvent = 4 Then
stDocName = "V4_Supplemental_Review_No_SAR_Filing"
DoCmd.OpenForm stDocName, , , "QC_Results_Table.case=" & Me.[TXTFindCase].Value
ElseIf varEvent = 3 Then
stDocName = "V3_Supplemental_Investigation_SAR_Filing"
DoCmd.OpenForm stDocName, , , "QC_Results_Table.case=" & Me.[TXTFindCase].Value
ElseIf varEvent = 2 Then
stDocName = "V2_New_Investigation_No_SAR_Filing"
DoCmd.OpenForm stDocName, , , "QC_Results_Table.case=" & Me.[TXTFindCase].Value
ElseIf varEvent = 1 Then
stDocName = "V1_New_Investiagtion_Sar_Filing"
DoCmd.OpenForm stDocName, , , "QC_Results_Table.case=" & Me.[TXTFindCase].Value
Else
MsgBox "Enter Another Case Number ", vbInformation, "Warning"
End If
rs.Close
Set rs = Nothing
Exit_cmdStartEdit_Click:
Exit Sub