Combo Box to populate selected information in a form (Web Database)

[Moghees]

Hi



I am looking into a way to create a form with multiple combo boxes and depending on what is selected in the first combo box certains values will appear in the second combo box, for examaple if if have a list of employees and want to see which department they work for.

So the first combo box would have department and I would choose HR and then in the second combo box i would get a list of all the personells working in the HR department

I have started of by creating 2 tables. one with a list of departments and a second with personells.

I am trying this in Access 2010 web database linked to sharepoint 2010


thanks in advance for any help people provide

[10 Gauge]

You want what is called a "Cascading Combo Box", do a quick google for that term and you will find a plethora of information on how to execute it. Alternatively you can do a forum search for the same term and come up with a lot of threads on how to execute this.

[Moghees]

thank you will look into it straight away

[Moghees]

Im following one of the tutorial provided

" http://www.fontstuff.com/access/acctut10.htm#download"

and managed to get the department list to work with the following



SELECT DISTINCT tblPerson.Director FROM tblPerson ORDER BY tblPerson.Director;

this brings the information up from the table,

but i dont know how to input the following code for the after update in Access 2010



Private Sub cboCountry_AfterUpdate()
cboCity.RowSource = "Select tblAll.City " & _
"FROM tblAll " & _
"WHERE tblAll.Country = '" & cboCountry.Value & "' " & _
"ORDER BY tblAll.City;"
End Sub

[10 Gauge]

Select your primary combo box, on the property sheet click on the "Event" tab. Then for "After Update" click the "..." at the end and if it asks you what builder you want click on Code Builder. This will take you to the VBA program and let you enter in your AfterUpdate event.

[Moghees]

sorry to be a pain ive tried and i get the Macro Tools Design view and it is automatically selected as Single Step and does not let me click on the "Convert Macros to visual Basics.

I think it maybe cause its a web database

[10 Gauge]

Ah, that could be. In which case it's far beyond my area of expertise (very narrow to begin with, haha). Maybe one of the MVP's will float through here and get ya taken care of. Good luck!

[Moghees]

I have now two tables tblDepartment with Depart as the primary key and another table Person with People as the primary key and Depart as a foreign key linked to Department.


I have a subform with a 2 combo box Department and Person combo box.

For the Department combo box the rowsource is: SELECT tblDepartment.Director FROM tblDepartment;
and it AfterUpdate event is:
requery Control Name Person


For the Department combo box rowsource the following query
Person from tblPeople
Depart from tblDepartment and criteria
[Forms]![formPeople].[Director]


I am wanting the Person combo to only show values based on the selection made in the Department Box.

[orange]

Have you seen the video tutorial at DataPig? It deals with 2 combo boxes.
It is not in Acc2010.
May be worth watching.
http://www.datapigtechnologies.com/f...combobox2.html

[Moghees]

Ive seen a similar video and managed to get this work but when I move the form and make it a subform and web access it stops working and doesnt syncronise with Sharepoint ...


Thanks for the video thou

[orange]

I have no experience with sharepoint or 2010, but as per your post #6, the web database would seem to be the unknown issue. Cascading combo boxes have been tested time and again, so the web database is where I'd suggest looking.

[Moghees]

i can get the combo boxes to work but when i syncronise i get the following error if anyone can help



Invalid reference 'forms.frmtable.cmbDirector' in expression.

[10 Gauge]

Try replacing the "."'s with "!"'s?