"Object variable or With block variable not set" (Run-time erro '91')

[Access_Person]

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:

Edit:The set rs = ..... line causes the error.

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.

Also note that I have attachd a screenshot of the form for easier visualization. The combo box for selecting the field is a relic from my first attempt, as is the "Search" button: right now it's supposed to filter as you type in the text.

 

[ecniv]

The listbox doesn't have a recordset - it has a rowsource (I think) so it fails to clone something that doesn't exist.


Vince

 

[Access_Person]

The listbox doesn't have a recordset - it has a rowsource (I think) so it fails to clone something that doesn't exist.


Vince

That's exactly the thing, the sample database that I got this from has the exact same command and yet it works like a charm.

Let me try finding a link....

Wow, I just spent a good 20 minutes going through yesterday's history but couldn't find it.

Attached is the sample database.

 

[smercer]

Access_Person: the coding looks similar to what the wizard creates for a combo box. I had a similar problem when I tried to re-name the combo box and the name in the coding and all references to it.

I have not tried this yet but it is worth a try: in the database window click on "Tools" > "Options" > "View" tab > check mark for "system objects"

In the tables in the database window you will notice more objects that were not there before. These tables hold info about all the objects you have created, relationships etc. if you did the same for the database that the code came from you may notice the reference to it there.

Also be verrry careful if you decide to play with these system tables as it could render your database useless, so I recommend to backup before trying anything.

Does this help? Please respond. Thanks for helping me too!!!

 

[Access_Person]

Like you suggested I made a copy of the DB where the code came from and attempted to play around.... However all of the nw tables are unreadable (boxes, strange symbols, etc) to the naked eye.

I did open "MSysRelationships" but this table was empty. Does this indicate a lack of relationships? I also opened the relationships window and pressed "Show all" but none appeared.

Note:

I have attached a slightly altered version of the original database so that you can have the source of the code that's not working. This is a slightly altered version so the issue is different: I don't get the error message but the fltering doesn't do anything at all. I'll be happy if either one of the problems is solved.

frmTest is mine and frmCompanies is the working sample code.

 

[smercer]

Have you tried to import the form and table etc that is needed and will it work then?

 

[Access_Person]

I tried that but it didn't work.... better yet when I deleted the form and pasted the old form (the old sample one) back into the database the form kept showing the data from my actual database. It's really messed up (yes, I did reboot excel between the attempts).

But don't worry about that, that's an issue for another day.

 

[Oy Vey]

In the VB editor, click on Tools | References. Make sure that the appropriate Data Access library is referenced. I looked at your sample and saw ADO 2.1 was referenced; did you change this or is that the original setting?

In case you're wondering, something similar happened to me at the beginning of my career, while teaching myself and a co-worker to do this stuff.

 

[Access_Person]

I didn't change that. It's the original setting. Why?