title: Combo-box values automatically update
body: <p>hopefully somebody can help</p>
  
  <p>The table structure is as follows:</p>
  
  <pre><code>tblCompany:   
  compID
  compName
  
  tblOffice:  
  offID,
  compID,   
  add1, add2, add3 etc...
  
  tblEmployee:   
  empID
  Name, telNo, etc...   
  offID
  </code></pre>
  
  <p>I have a form that contains contact details for employees, all works ok using after update.
  A cascading combo box, cmbComp, allows me to select a company, and inturn select the appropriate office, cboOff, and updates the corresponding tblEmployee.offID field correctly. Fields are automatically updated for the address also</p>
  
  <p>cmbComp: RowSource</p>
  
  <pre><code>SELECT DISTINCT tblOffice.compID, tblCompany.compID 
  FROM tblCompany 
  INNER JOIN AdjusterCompanyOffice 
  ON tblCompany.compID=tblOffice.compID 
  ORDER BY tblCompany.compName; 
  </code></pre>
  
  <p>cboOff: RowSource</p>
  
  <pre><code>SELECT tblCompany.offID, tblCompany.Address1, 
  tblCompany.Address2, tblCompany.Address3, tblCompany.Address4, 
  tblCompany.Address5 
  FROM tblCompany 
  ORDER BY tblCompany.Address1; 
  </code></pre>
  
  <p>The problem I am having is that when i load a new record how to retrieve the data and automatically load the cmbComp and text fields. </p>
  
  <p>The cboOff combo box loads correctly as the control source for this is the offID</p>
  
  <p>I imagine there must be a way of setting the value on opening the record? Not sure how though. I dont think I can set the controlsource cmbComp or text fields, or can I?</p>
  
  <p>Any help/point in the right direction appreciated, have been searching for a way to do this but cant get anywhere!</p>
  
  <p>-edit</p>
  
  <p>Ive tried adding the following for the control of a text field</p>
  
  <pre><code>=[Forms]![frmAdjPersonalDetails]![cboAdjOff].[Column](2)
  </code></pre>
  
  <p>This works at getting the values but causes an error with the after Update used to create the cascading combo box and update the text fields.</p>
  
  <pre><code>Private Sub cmbComp_AfterUpdate()
  Me.cboOff.RowSource = "SELECT ID, Address1, Address2, Address3, Address4, Address5        FROM" &amp; _
   " tblOffice WHERE CompID = " &amp; Me.cmbComp &amp; _
  " ORDER BY Address1"
   Me.cboAdjOff = Me.cboAdjOff.ItemData(0)
  Me.txtAdd2 = Me.cboOff.Column(2)
  Me.txtAdd3 = Me.cboOff.Column(3)
  Me.txtAdd4 = Me.cboOff.Column(4)
  Me.txtAdd5 = Me.cboOff.Column(5)
  End Sub
  </code></pre>
  
  <p>Not sure what tod do??</p>