Dynamic variable holder with counter

Question

I have two txt boxes and two combo boxes on a form. There is also a subform linked to the temptable that I want to have rebuilt/filter each time one of the controls is changed (using after update on each control to trigger the following sub)

I receive Run-time error '91: Object variable or with block variable not set on line Items(i) = Thing

I am not sure using " (i) " works with MS Access 365 or I am dimensioning incorrectly?

Thank you.

    Private Sub Lookupstuff()
    Dim i As Integer
    Dim Items(1 To 4) As Object

        sql = "DELETE * FROM tblTemp"
        CurrentDb.Execute sql
        i = 0
    FilterArray = Array(Me.txtNew, Me.cmbS, Me.cmbP, Me.txtSl)
    For Each Thing In FilterArray
        If Not IsNull(Thing) Then
            i = i + 1
            Items(i) = Thing <--Error is here. Items(i) is empty.
        End If
    Next

    If i = 0 Then
        Forms!frmNew.Requery
        Forms!frmNew.Refresh
    End If
    If i = 1 Then
        Filter = Items1
    End If
    If i = 2 Then
        Filter = Items1 & " AND " & Items2
    End If
    If i = 3 Then
        Filter = Items1 & " AND " & Items2 & " AND " & Items3
    End If
    If i = 4 Then
        Filter = Items1 & " AND " & Items2 & " AND " & Items3 & " AND " & Items4
    End If

    sql = "INSERT INTO tblTemp SELECT * FROM tblQ"
    If Not IsNull(Filter) Then
        sql = sql & " WHERE " & Filter
    End If
    CurrentDb.Execute sql
        Forms!frmNew.Requery
        Forms!frmNew.Refresh
    End Sub

Lee Mac · Accepted Answer · 2018-12-15T00:14:12.600

0

Since you are assigning a reference to object in the array, you must use Set, i.e.:

Set Items(i) = Thing

Also, presumably each reference to Items1, Items2 etc. should actually be Items(1), Items(2) in order to access the objects referenced at these indices of the array.

edited Dec 15 '18 at 00:14

answered Dec 14 '18 at 23:46

Lee Mac

15,615
6
32
80

Thank you! I tried using Set as you suggested and the code does run through without the original error. However, it does not assign to anything other than Items(i) seems to ignore it should be using items1 through items2. So I get a syntax error in the where clause as the sql just ends with WHERE and nothing after. ? – Ben.Name Dec 14 '18 at 23:58
Thank you again. adding the () did help get the sql to show the values in the watch window. for example: sql = "INSERT INTO tblTemp SELECT * FROM tblQ WHERE Column1 =1234" however, I am now getting a Run-time error '3464' Data type mismatch in criteria expression. ? That is the exact sql value being returned and I'm not sure why it throws the data type mismatch? – Ben.Name Dec 15 '18 at 00:40
Is `Column1` numerical? – Lee Mac Dec 15 '18 at 00:54
Column1 is actually is column10 and is short text. the other "Filters" used in this are also short text. there are some date columns and a yes/no , but none of them pertain to the sql ? – Ben.Name Dec 15 '18 at 01:03
If your fields are text then your filter values will need to be surrounded by single-quotes, e.g. `Column1 = '1234'` – Lee Mac Dec 15 '18 at 01:05
Works Great Now! Thank You !!! If i = 1 Then Filter = "Column1 = " & "'" & Items(1) & "'" End If If i = 2 Then Filter = "Column1 = " & "'" & Items(1) & "'" & " And " & "Column2 = " & "'" & Items(2) & "'" End If – Ben.Name Dec 15 '18 at 01:31

Dynamic variable holder with counter

1 Answers1