cbo to filter results from a query

nianko

Registered User.
Local time
Today, 04:00
Joined
Aug 17, 2010
Messages
21
Dear Forum,

I am quite agile with other ms applications but, a beginner in Access. My question is quite simple and I read a lot of solutions but none of them is working. I have a table "interests" in which I record interests from investors on some deals (I also have a table "deals" and "investors").
tbl interests is linked to tblClients and tblInvestors
I also have a query to look for interests from CLient, with the criteria like"*"+[what is the name of the deal?]+"*" and it works.But it's not user friendly. I want my user to use a cbo, because he/she doesn't know the name of all the deals (=borrower).

I would like to build a form with an unbound combo box (whose values are taken from the "deals" table), and when I select a name in that combo box I want the interests related to that particular deal.

I tried 3 solutions, none of them works:
1) I borrowed some code from another discussion:

Private Sub cboInterestByBorrowers_AfterUpdate()
DoCmd.ShowAllRecords
Me!txtBorrower.SetFocus
DoCmd.FindRecord Me!CboInterestByBorrowers

Me!CboInterestByBorrowers.Value = ""
End Sub

where cboInterestByBorrowers is my combo box

2) in my query, I put in criteria:

[Forms]![frmLookUpByBorrowers]![cboInterestByBorrowers]

3) the easiest solution I thought: I created a subform, but I cannot link it with my form. It's crazy, when I click on property I cannot link them, the property field is missing.

For reference I include my database (I changed all the names and I removed all the details).

Can someone have a look at it and explain why it doesn't work. It's driving me crazy :mad:

Thx
 

Attachments

In your query qryInterest_LookUp add the field Deal_ID from the table tblDeals, move the criteria you have for borrowers to Deal_ID.

In your current database when the user selects the Borrower the value sent to the query is the deal_id and not the name of the borrower so this results in no data displayed.
 
Thx a lot,

Would you have any idea to filter both by "deal", and on another field ("investors") i.e. with 2 comboboxes?

Cheers
 
I have attached an updated version. In the new drop down you will need to list your Investorswho are also in the Interests table. Otherwise you will receive an error message as there are no matching records in the Interests table.
 

Attachments

Hi,

Yes indeed it works but only if there exist an occurrence with the delested investor and selected deal.
It would be more "beautiful" to have my 2 combo boxes lists updated depending on what is selected in the other.

For instance if there are 3 interests for deal A, one with investor B, one with C and one with D, and if I select "A" in the first combo box; then I would like to have only "B", "C" and "D" in my Investors Cbo.
Do you think it is feasible? I should change my row source for the combo box and base it on a query instead?

Many thx for your help

nianko
 

Users who are viewing this thread

Back
Top Bottom