Run Time error in VBA code when trying to access rows from a table

[twink]

I am trying to access certain rows in a table in a combo box. I get this error (Run-time error '3265' Item not found in this collection) Here is the code. I am new to VBA so please excuse me. Here is the code:


Private Sub GO_Click()
Set rec = New ADODB.Recordset
Dim rs As Recordset
Dim sql As String
Dim Combo43 As ComboBox


sql = "SELECT tblListBox.List FROM tblListBox;"
Set rs = CurrentDb.OpenRecordset(sql)





If Me.Combo43 = rs.Fields(0) Then

DoCmd.RunMacro ("macDeleteROLL0300Data")
'cmdDelete.Enabled = False

Dim rolldt As String
Dim rsroll As Recordset

Strsql = "SELECT Max(tblROLL0300.DATE) AS MaxOfDATE " & _
"FROM tblROLL0300;"
Set rsroll = CurrentDb.OpenRecordset(Strsql)
rolldt = rsroll!MaxOfDATE

txtRoll.SetFocus
txtRoll.Text = rolldt

rsroll.Close

ElseIf Me.Combo43 = rs.Fields(1) Then
DoCmd.RunMacro ("macDeleteCurrentCollAcctArch")

ElseIf Me.Combo43 = rs.Fields(3) Then
DoCmd.RunMacro ("macCollectedAccountARCHIVE")
End If

[MMcCarthy]

Not sure whats going on but try changing

If Me.Combo43 = rs.Fields(0) Then

to

If Me.Combo43 = rs!List Then

If that doesn't work can you tell us which line the code stops at?

[MMcCarthy]

Sorry I just had another look, the following code won't work as you have only returned one field (list) in your recordset

ElseIf Me.Combo43 = rs.Fields(1) Then
    DoCmd.RunMacro ("macDeleteCurrentCollAcctArch")
 
ElseIf Me.Combo43 = rs.Fields(3) Then
    DoCmd.RunMacro ("macCollectedAccountARCHIVE")
End If
 

What exactly is it you are trying to do?