Microsoft Access - Can I make a form's combobox query a lookup based of a another combobox's value?

Asked By Greg on 31-Aug-12 11:50 AM
I have two combo boxes.  The first combo box is a drop down off a set of values (customer codes) in which the user must select one that's provided.  Is it possible for the second combo box to lookup/query the name associated with that customer number??

example:  SELECT table.Customer_Name FROM table WHERE combobox1.text = table.Customer_Name;
 
I can make the second combo box query a second drop down based off customer names in the same table, but it seems redundant since the previously selected customer number is specific to the name in the same table.

Any thoughts would be much appreciated, thanks!
Rohan Dave replied to Greg on 31-Aug-12 02:52 PM
You can write a code in SelectedIndexChanged event of your first combobox and fire "Select" query to pull name and bind it on second combobox.

Your code looks like below...

combobox1_SelectedIndexChange( )
{
  objAdpt = new OledbDataAdapter("Select EmpID, EmpName From Employee Where Code = " + combobox1.SelectedValue);
  objDt = new DataTable();
  objAdpt.Fill(objDt);
  combobox1.DataSource = objDt;
  combobox1.DataTextField = "EmpID";
  combobox1.DataValueField ="EmpName";
}

try by above code..
Pat Hartman replied to Greg on 09-Sep-12 11:09 PM
You don't need any code at all.  Make the second control a textbox rather than a combo and set its locked property to true.  Change the RecordSource of the form to join to the lookup table.  When you choose a customer from the combo, the name will populate automagically.