MS Access: Object required Error with SQL statement

Question

I understand what is the meaning behind this error, but I simply dont know why is he underling my line of code, that is supposed to cause this error.

EXPLANATION: What I am trying to achieve is, when you select a Company from CompanyCombo box, it will automaticly "filter" the ContactCombo box to those contacts that are from above mentioned company. Companies and Contacts are two different tables which are connected through Query called Companies Query (the CompanyNr [this is also UniqueKey] from Companies is connected with CompanyNr from Contacts).

PART OF CODE:

Private Sub CompanyCombo_Change()
    Dim sql1 As String
    ' The below code causes the Objecr required error
    sql1 = "SELECT [Contacts].[ID], [Contacts].[Contact] FROM Contacts " & _
           "WHERE [Contacts].[CompanyNr] = " & CompanyNr.Value & " ORDER BY [Contact]; "

    ContactNrCombo.RowSource = sql1
    ContactNrCombo.Requery
End Sub

I have a ComboBox for Companies and Contacts, and I want to write next to them the appropriate number (CompanyNr for Companies and ContactNr for Contacts)

They are connected with query.

I am quite new to the VBA, so maybe I dont see something here what could be causing the error.

TABLES for CONTACTS and COMPANIES: Contact Table has: ID, CompanyNr, Contact Company Table has: CompanyNr, Company

CompanyNr are connnected via Relationships.

I really dont know what could be going wrong here. I created the ComboBox with the wizard that pops up when creating.

Please, anyone, help me out.

This question appears to be off-topic because the OP seems to be lacking the basic debugging skills and any help revolved around debugging this problem will be too broad. — user2140173
– user2140173, Commented Nov 13, 2014 at 15:35
The only object access I see is CompanyNr. Check that this is really the name of a control in your form. — grahamj42
– grahamj42, Commented Nov 13, 2014 at 15:45
@vba4all So what, should I copy my entire code here? Or post screenshots of the table? What do you expect - let me know, and I`ll post it here. I tried to provide as much info as I can. — David Kasabji
– David Kasabji, Commented Nov 13, 2014 at 15:46
@grahamj42 Yes that I also noticed, but the CompanyNr is set as Control Source for the ComboCox (I called it in my case CompanyCombo). That´s why i dont know what´s wrong.. — David Kasabji
– David Kasabji, Commented Nov 13, 2014 at 15:52

Patrick Honorez · Accepted Answer · 2014-11-13 15:54:58Z

1

Just a try, because the question does not provide enough info at the moment:
Shouldn't the WHERE line refer to CompanyCombo instead of CompanyNr ?

"WHERE [Contacts].[CompanyNr] = " & CompanyCombo.Value & " ORDER BY [Contact]; "

answered Nov 13, 2014 at 15:54

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Kasabji Over a year ago

Thanks @iDevlop, this seemed to fix my issue. I guess I confused something in that statement. Thanks again!

Collectives™ on Stack Overflow

MS Access: Object required Error with SQL statement

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related