Combo box dependant on another combo box

[nianko]

Hi

I have a combo box from which I want to choose a list of options, which then will cause my second combo box to offer a list of options which are dependant on the first combo box.

It's not unsual I know but I don't understand why it doesn't work.

I have 2 tables (contacts and investors) and a form with the 2 cbos.

I chose first the "investor" (= company name) and then I want to filter the contacts in that particular company in my second cbo.

SQL for the first cbo:
SELECT tblINVESTORS.Investor_ID, tblINVESTORS.Name FROM tblINVESTORS ORDER BY [Name];


SQL for the 2nd cbo:
SELECT tblCONTATCS.Contact_ID, tblCONTACTS.Company, tblCONTACTS.Firstname, tblCONTACTS.Surname FROM tblCONTACTS WHERE (((tblCONTACTS.Company)=Forms!frmRECORD_NEW_INTERE STS!cbo_InvestorRecord)) ORDER BY [Surname], [Firstname];

I have an refresh query for the first cbo:

Private Sub cboManufacturer_AfterUpdate()
cboModel.Requery
End Sub

The problem: when I open the form a pop up asks "Enter Parameter Value
tblCONATCTS.Contact_ID..."


What is missing? I am sure it's quite basic but Access is not my cup of tea.

nianko

 

[MarkK]

Spelling?

tblCONATCTS

 

[nianko]

Sorry the error message is tblCONTACTS.Contact_ID

 

[MarkK]

A query prompts for a parameter value when it can't find one of the fields in the SQL. Make sure the tblContacts contains the field Contact_ID and that none of the field names are misspelled.

 

[nianko]

I'm so carelessss! Indeed I mispelled sth (and I doublechecked it though!).
Now I have another issue, When I click on the cbo_ContactRecord,

(remember I have 4 columns, Contact_ID, Company (a reference nb), Firstname and Surname). How could I have Just the Firstname and Surname to appear in the list?
I tried to change the width of the 2 ID columns to 0cm, but nothing happens?!
Also, when I select a ligne, of course only one field appears,. Is there a way to have boththe FIstname and surname appear in the combo box?

Thanks for taking time to consider my beginner-level questions!

nianko

 

[MarkK]

For a combo you may also need to change the ColumnCount property. For lists and combos the properties that are commonly used and worth understanding the effects of ...

[B]Format Tab[/B]
- ColumnCount
- ColumnWidths
- ListRows
- ListWidth

[B]Data Tab[/B]
- RowSource
- RowSourceType
- BoundColumn
- LimitToList

You can combine fields so two or more appear in the combo by modifying the SQL like the following ...

SELECT PersonID, FirstName & " " & LastName As Fullname FROM tPerson

In this case both the FirstLame and LastName will be displayed in a single field.
You are asking excellent questions if you want to learn to use a combo box
Cheers,