"Object variable or With block variable not set"

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

"Object variable or With block variable not set"

Author

Topic

RagingSpirit
Starting Member

14 Posts

Posted - 2004-08-18 : 10:36:00

I get the above error message when I click on a listbox that's inside a form. Here's the code that causes the problem:


Private Sub Search_Area_AfterUpdate()
    Dim rs As Object
    
Set rs = Me.Recordset.Clone
    rs.FindFirst "[Applicant ID] = " & Str(Me![Search2])
    Me.Bookmark = rs.Bookmark
End Sub

Search_Area is the list box.

Key_Word_Input is a text box on the form for inputting the search criteria.

Search2 is another text box (insivible) that's located on the form.

Applicant ID is the first column in the list box (there's a total of 5 columns).

Only entries that have the criteria typed into Key_Word_Input in one of their fields should show up in the list box.

Here's the code for the the input box:


Private Sub Key_Word_Input_Change()
Dim vSearchString As String

    vSearchString = Key_Word_Input.Text
    Search2.Value = vSearchString
    Me.Search_Area.Requery

End Sub

Here's the code for the "Reset" button.


Private Sub ClearIt_Click()
On Error GoTo Err_ClearIt

Me.Key_Word_Input = ""
    Me.Search2 = ""
        Me.Search_Area.Requery
            Me.Search_Area.SetFocus

Exit_ClearIt_Click:
    Exit Sub

Err_ClearIt:
    MsgBox Err.Description
    Resume Exit_ClearIt_Click


End Sub

Note that the structure was lifted from a sample database found on the web. Prior to this I tried to simply insert a subform into the main form and then have it filtered according to the words typed in. This didn't work and after quite a few hours of futile work I tried the listbox approach. It works perfectly in the sample database but not for me. I am really lost.

Not only does it crash when I click on the listbox but it also doesn't filter anything.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-08-22 : 19:13:31

Try changing your DIM statement to read as
Dim rs as New Object

-----------------------------------------------------
Words of Wisdom from AjarnMark, owner of Infoneering

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-22 : 19:23:03

You're better off doing the instantiation after the declaration - MS have admitted that doing both in the same statement affects performance. So:

Dim rs as Recordset
Set rs = New Recordset

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources