How to display Contact per each Company I select?

[Natan]

Hi there.

My question is-
I have Companies Table & Contacts Table

In Contacts Table, there is Company field which is linked to Companies Table.

I want to make a form in which I can choose the company name and then choose a person name. But I need the Contact list to filter by the Company name I selected before.

Hope you understood me.
I'm new in Access and in here as-well.



Thanks ahead! Seems to be great forum from what I saw...

[pbaldy]

Welcome to the site. Sounds like you want cascading combos:

Baldy-Cascading Combos

[Natan]

I did this thing:

Private Sub cboCategory_AfterUpdate()
' Update the row source of the cboStatus combo box
' when the user makes a selection in the cboCategory
' combo box.
Me.cboStatus.RowSource = "SELECT tblStatuses.StatusID FROM" & _
" qryStatusesCategories WHERE tblCategories.CategoryName = " & _
Me.cboCategory & _
" ORDER BY tblStatuses.StatusID"

Me.cboStatus = Me.cboStatus.ItemData(0)
End Sub

It does no show any option...

BTW, I make multiple values on StatusID.
And there are multiple Categories for each status.
So I've made a query to show each status per category.
Hope you understood.

Thanks ahead.

[pbaldy]

Looks okay, assuming CategoryName is a numeric field, which it doesn't sound like. Can you post the db?

[Natan]

Looks okay, assuming CategoryName is a numeric field, which it doesn't sound like. Can you post the db?

No, CategoryName is a text value.
I'll post the db as soon as I get home.

Is that the problem? that it's not a numeric value?

[pbaldy]

Try

Me.cboStatus.RowSource = "SELECT tblStatuses.StatusID FROM" & _
" qryStatusesCategories WHERE tblCategories.CategoryName = '" & _
Me.cboCategory & _
"' ORDER BY tblStatuses.StatusID"

Note the single quotes surrounding the value.

[Natan]

Hi,

I tried it and still - nothing shows up.

I've uploaded my DB to a server, because it's larger than 500kb, here's the link:

www.martech.co.il/STDB2012.rar

Please ignore text in other language.

Look in "tblCategories", "tblStatuses", "qryStatusesCategories" & "frmWorks".

Thanks Ahead!!!

[Natan]

Bump. please...

I can not progress

[pbaldy]

I was hoping someone would jump in. I don't like downloading from unknown sites, and I don't feel like downloading software to handle the rar format. If you can zip a sample and upload it here, I'll take a look.

[Natan]

Here, ZIP format.

Thanks ahead dude.

[pbaldy]

You have the wrong field in the criteria, so you're comparing apples to oranges. Try

  Me.cboStatusName.RowSource = "SELECT StatusID, StatusName, CategoryName FROM" & _
                             " qryStatusesCategories WHERE StatusID = " & _
                               Me.cboCategoryName & _
                             " ORDER BY tblStatuses.StatusID"

That leaves you with a display issue that I think you can work out.

[Natan]

Thanks man, you helped a lot!

by-the-way,

1. You got a bit wrong in your code:

" qryStatusesCategories WHERE StatusID = " & _
to
" qryStatusesCategories WHERE CategoryID = " & _

2. I allowed multiple values and it show an error. (Run-time error, cannot perform this operation)
Then I go to debugger and it marks this row:


Me.cboStatusName.RowSource = "SELECT StatusID, StatusName, CategoryName FROM" & _
" qryStatusesCategories WHERE CategoryID = " & _
Me.cboCategoryName & _
" ORDER BY tblStatuses.StatusID"

Me.cboStatusName = Me.cboStatusName.ItemData(0)

So I removed it, tried to filter again and it worked, but when I select multiple values it shows an error, look at the picture I added.

Thanks ahead again!!!

[Natan]

Bump please.

[pbaldy]

Not sure; I don't like or use the new multi-value field.

[Natan]

Not sure; I don't like or use the new multi-value field.

Is there a way to make it, lets say like comment thing so I'll know in what date each status was updated? If yes, how do I do that?