cbo to filter results from a query

[nianko]

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

[ryan1313]

Even though it is showing the borrower, the bound column in your combo box is the deal id. So in your query, you are trying to find a borrower name that = a number.

Add the field Deal_ID from the Borrower table and save it.

The code that you run will error with no records. You can either add error handling to your code or you can place this in the afterupdate event for your combo box:

 
Dim strSQL As String
    strSQL = "SELECT * FROM qryInterest_Lookup WHERE [DEAL_ID] = " & Me.CboInterestByBorrowers
    Me.Form.RecordSource = strSQL
    Me.Form.Requery

[nianko]

Hi,

Thx for your answer. What you say make sense, but even if I add the dield Deal_ID to my query, it still doesn't work. I double checked everything, but my combo box isn;t linked to my query!
Also, I cannot find th eparent/child property for the subform. I had a look at a tutorial to know where to find that (on the creation of the subform), but my wizard did not displayed the same options...

This database is a nightmare

[ryan1313]

You will also need to take the reference to the combo box out of the query criteria to make it work.

I am uploading your example with this working.

As for the parent/child property of the subform, when you are in design mode, highlight the subform and under the data tab, you will see link master fields and link child fields.

Also, if you use the built in wizard to add your subform, it will help you chose a relationship between the parent and child form.

Edit: had to delete the new record form to meet forum's size requirement

[nianko]

Hi,

Your solution is quite logical and I adapted it to my original database. It works!.

For the subform solution, it's fine, I wasn't clicking the right way (on the black squre at the left top instead of the hand).

For the last solution, with the [Forms]![frmLookUpByBorrowers]![cboInterestByBorrowers], I still cannot use it, so i'll study a bit more.

Thx a lot

[ryan1313]

Try changing the bang (!) between [frmLookUpByBorrowers]![cboInterestByBorrowers] to a period.

so [Forms]![frmLookUpByBorrowers].[cboInterestByBorrowers]