Having Trouble with rs Recordset

[Nixonde1]

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

 

[Norie]

Try this.

rs.MoveFirst
varEvent = rs.Fields(0)

 

[Nixonde1]

Thanks Norie, That worked!!!!!