Combo Box filter

[bunji]

I have two combo boxes, Company Name & Name. The data that is selected is used to run a query from a button. However i would like to set it that if the user (but not required) selects the company name then only the names of the people belonging to that company appear, but if no company is selected then all the names for every company appear.

Can anyone make a suggestion as to the best way to go about this.

Thanks

 

[KenHigg]

I think this is like a cascading combo box. You can do a search here in the forum and find some great examples...

 

[Tim L]

I presume that you have a single table listing the companies and their associated contacts? If not and you have one table for the companies and a second for the contacts then you will need to create a query that joins the information together.

Using either the single table or the join query as the source for your query put into the criteria for the company name

Like "*" & [Forms]![frmFormName]![cboCompanyName]

You will, no doubt, need to change frmFormName and cboCompanyName to your own form name and the name of the combo box.

HTH

Tim

 

[bunji]

Hi Tim, They are both in the same table, and on the row source of the name box i have the following:

SELECT [First Name] & " " & [Surname] AS Name
FROM tblCustomers
WHERE (((tblCustomers.[Company Name]) Like "*" & [Forms]![FrmSearchCriteria]![Company Name]));

But this gives me the same result. Where if a company name is selected i get all the names.

 

[jrjr]

Try this:

SELECT tblCustomers.First Name, tblCustomers.Surname
FROM tblCustomers
WHERE (((tblCustomers.Company Name=[Forms]![FrmSearchCriteria]![Company Name]Or [Forms]![FrmSearchCriteria]![Company Name] Is Null)=True));

 

[bunji]

This is the code i tried;

SELECT [First Name] & " " & [Surname] AS Name
FROM tblCustomers
WHERE ((([tblCustomers].[Company Name]=[Forms]![FrmSearchCriteria]![Company Name] Or [Forms]![FrmSearchCriteria]![Company Name] Is Null)=True));

But get the same results; no matter what company is selected all the names are still shown.

 

[Tim L]

Try this, using the 'Like' that I suggested earlier:

SELECT [First Name] & " " & [Surname] AS Name
FROM tblCustomers
WHERE ([tblCustomers].[Company Name] Like "*" & [Forms]![FrmSearchCriteria]![Company Name]);

Note also that by using the "*" & you will get results even if the company name combo is empty. In fact you will get all the records if that is the case; which means that your users can choose to select a company or not, if they do the list in the Name combo will be more limited, if not they get the lot; I'm sure that they'll soon figure out how to make their own lives easier.

I'm also concerned that you appear to have a combo named after a field, i.e: Company Name. It is good practice to give controls identifiable names, for example, for your company name combo I would have used cboCompanyName. When referenced elsewhere it is then obvious that the item being referred to is a combo box (from the cbo prefix).

Could I also just mention at this point that you should try and steer clear of having spaces in field names, which it appears that you have? Check out
http://www.mvps.org/access/general/gen0012.htm
and
http://support.microsoft.com/defaul...port/kb/articles/q173/7/38.asp&NoWebContent=1
for advice on naming conventions.

You also need to refresh the Name combo in the After Update event of the Company Name combo. If the Name combo is called cboName this can be done by putting the following line of code in the Company Name combo AfterUpdate event:

me.cboName.refresh

By correctly naming controls you can also prevent complications that sometimes arise from having a control named directly after the field, for example; you might find that the refresh command above won't work if the combo has the same name as the field, Access is like that sometimes.

HTH

Tim

 

[bunji]

Hi Tim, Thanks for your advice i will take that on board, its diffcult to start renaming stuff once ive started isnt it?

I have renamed my combo box though and tried to up run the following:

Private Sub CmboCompany_Name_AfterUpdate()

Me.CmboFirstLastName.Refresh

End Sub

but i get an error that says, method or data paramerter not found.??

 

[bunji]

I thought it might be due to the under score between Company_Name

Private Sub CmboCompany_Name_AfterUpdate()

Is now

Private Sub CmboCompanyName_AfterUpdate()

Still same error though

 

[stevehooley]

hi bunji,

maybe try .Requery rather than .Refresh for the combo box

 

[bunji]

No error flshes up against requery, so i believe its working, but still get all the names, not just the company's ones.

Just to make sure my code is:

SELECT [First Name] & " " & [Surname] AS Name FROM tblCustomers WHERE (tblCustomers.[Company Name] Like "*" & Forms!FrmSearchCriteria![Company Name]);

 

[bunji]

Apologies it does work, because i renamed the combo box i forgot to change the code!!

Thanks for your help everyone!