CODEWITHSUNDEEP
vbams-accesscompiler-errors
Chris M
Chris M

Reputation: 1

Compile error: variable not defined in MS Access creating a secondary selection list

I'm trying to build a drop down list to select multiple options as a secondary option to query multiple sites within MS Access and continue to get the compile error: variable not defined, but I do not see the issue. It is hanging up on line 1 with the PSub function to pull the multiple site list and highlighting line 10: i = rs(0).

Private Sub BLDlist_Click()
    Dim db As Database
    Set db = CurrentDb
    Dim rs As Recordset
    DoCmd.RunSQL ("Delete * from MultipleSite")
    Set rs = db.OpenRecordset("Bldglist")
    rs.MoveFirst
    Do Until rs.EOF
        i = rs(0)
        Me.cbobuilding.Value = i
        Dim cbdg As Long
        
        rs.MoveNext
        
        Call btnchange_Click
        Call Select_All_Click
        Call Command78_Click
        DoCmd.OpenQuery ("appMultipleSite")
    Loop
    DoCmd.OpenTable ("MultipleSite")
End Sub

This should open a table to allow me to select multiple sites at one time, instead of one site which is the standard setup.

Upvotes: -1

Views: 42

Answers (1)

Gustav
Gustav

Reputation: 55981

First, always include in the top of any module:

Option Explicit

That will show you, that i hasn't been declared.

Also, do indent code lines to make the code readable and close the recordset when done. So:

Private Sub BLDlist_Click()

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i  As <the data type of field rs(0)>

    DoCmd.RunSQL ("Delete * from MultipleSite")
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Bldglist")
    rs.MoveFirst
    Do Until rs.EOF
        i = rs(0).Value
        Me!cbobuilding.Value = i
        rs.MoveNext

        Call btnchange_Click
        Call Select_All_Click
        Call Command78_Click
        DoCmd.OpenQuery ("appMultipleSite")
    Loop
    rs.Close

    DoCmd.OpenTable ("MultipleSite")

End Sub

Upvotes: 3

Related Questions