Combo-box values automatically update

Question

Hi all, hopefully somebody can help

The table structure is as follows:

tblCompany:   
compID
compName

tblOffice:  
offID,
compID,   
add1, add2, add3 etc...

tblEmployee:   
empID
Name, telNo, etc...   
offID

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

cmbComp: RowSource

SELECT DISTINCT tblOffice.compID, tblCompany.compID 
FROM tblCompany 
INNER JOIN AdjusterCompanyOffice 
ON tblCompany.compID=tblOffice.compID 
ORDER BY tblCompany.compName; 

cboOff: RowSource

SELECT tblCompany.offID, tblCompany.Address1, 
tblCompany.Address2, tblCompany.Address3, tblCompany.Address4, 
tblCompany.Address5 
FROM tblCompany 
ORDER BY tblCompany.Address1; 

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.

The cboOff combo box loads correctly as the control source for this is the offID

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?

Any help/point in the right direction appreciated, have been searching for a way to do this but cant get anywhere!

-edit

Ive tried adding the following for the control of a text field

=[Forms]![frmAdjPersonalDetails]![cboAdjOff].[Column](2)

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.

Private Sub cmbComp_AfterUpdate()
Me.cboOff.RowSource = "SELECT ID, Address1, Address2, Address3, Address4, Address5        FROM" & _
 " tblOffice WHERE CompID = " & Me.cmbComp & _
" 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

Not sure what tod do??

Answer 1

If I understand you correctly, you are looking for the "Requery" statement. This will "refresh" the data in your combobox or form. you can put a

 Me.Requery 

In the relevant Event handler. Probably "AfterUpdate" if I remember correctly.

Answer 2

try

row source in the combo box =

"SELECT DISTINCT table.field FROM table;"

finds all unique values in table, alphebetizes them, then makes them into a long drop down menu.