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

[TheShabz]

Check out this link. I think it might clear things up for you.
http://www.baldyweb.com/CascadingCombo.htm

[nianko]

Hi, it's working thanks, but...
When I display in the cbo list both the Fisrtname and Surname of a contact, and when I select one, it only show the fisrtname. Because many people can have te same firstname, I would like to display both the Firstname and Surname (and also, how to remove the separation bar between the 2 columns).

I'm sure it's obvious for a seasoned user, but I tried to play around with the properties of the combo box and couldn't find anything...

Nianko

[maximus]

I see that the TheShabaz has suggested a link from Sir Paul's Site regarding Cascading Combo. Well you need to create a Calculative Field and Concatenate the two Fields in the RowSource Query:

Like FullName:[FirstName] & " " & [LastName]

Then arrange the columns or the Column Size to display only Column FullName.

[nianko]

Thanks alot, I will try that. I am sure it will work.

Also, I cannot ilnk to combo box in a form. I wonder if I made a mistake in my SQL statements.

My fisrt cbo is Portfolio and the 2nd one is Deal.

If I chose a portfolio I want to have only the associated in my deal cbo.
I already did taht for 2 cbos and it worked. But not this time.

First CBO code is:
SELECT tblportfolio.Portfolio_ID, tblportfolio.Porfolio FROM tblportfolio ORDER BY [Porfolio];

2nd CBO code is:
SELECT tblDEALS.Deal_ID, tblDEALS.Borrower, tblDEALS.Portfolio FROM tblDEALS WHERE (((tblDEALS.Portfolio)=Forms!frmLookUpByBorrowers! cbo_InterestByPortfolios)) ORDER BY [Borrower];

and the VBA that refresh:
Private Sub cboInterestByPortfolios_AfterUpdate()
cboInterestByBorrowers.Requery
End Sub

My 2 boxes are unbound.

Can you spot the issue?

[maximus]

can you upload a sample of your database and let me do the need full.

[nianko]

Hi,

I removed all confi information so it looks quite odd...

[maximus]

I am attaching the database please chect whether its according to what u wanted

[nianko]

The attached doc?

[maximus]

I have ziped the file check it I am reattaching it

[maximus]

downloa the attached file and unzip the db and check it.

[nianko]

Thanks a lot, it works!

[maximus]

Now plese mark the thread solved. I will really appreciate u doing it.

[nianko]

Sure, where is the button??

[maximus]

left hand Side top of your screen in Thread tools u will have the options. If u have any problems please follow this link and learn

https://www.accessforums.net/forum-s...lved-1828.html