Run Time Error In VBA Code

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • twink
    New Member
    • Jul 2007
    • 6

    #1

    Run Time Error In VBA Code

    am trying to access certain rows in a table through a combo box.Basically what I am trying to do is when I select "Delete Data" from the combo box and click the go button it will run a macro. 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.OpenR ecordset(sql)





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

    DoCmd.RunMacro ("macDeleteROLL 0300Data")
    'cmdDelete.Enab led = False

    Dim rolldt As String
    Dim rsroll As Recordset

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

    txtRoll.SetFocu s
    txtRoll.Text = rolldt

    rsroll.Close

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

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

    --------------------------------------------------------------------------------
  • fplesco
    New Member
    • Jul 2007
    • 82

    #2
    Originally posted by twink
    am trying to access certain rows in a table through a combo box.Basically what I am trying to do is when I select "Delete Data" from the combo box and click the go button it will run a macro. 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.OpenR ecordset(sql)

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

    DoCmd.RunMacro ("macDeleteROLL 0300Data")
    'cmdDelete.Enab led = False

    Dim rolldt As String
    Dim rsroll As Recordset

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

    txtRoll.SetFocu s
    txtRoll.Text = rolldt

    rsroll.Close

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

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

    --------------------------------------------------------------------------------
    Hi there -

    "Run-time error '3265' Item not found in this collection" means that you are referencing a FIELD which is not returned in your query.

    As I examined your query, you only selected one (1) FIELD in every query. First, the field is "tblListBox.Lis t" and on your second query, the "MaxOfDATE" .

    Notice that your code contains rs.Fields(1), rs.Fields(2), and rs.Fields(3). This explains why. There should only be one field that you are referencing here, that is rs.Fields(0) or rs!List or rs!MaxOfDate

    Hope this will help you.

    Good day!

    Comment

    Working...