OK, let's find out a little more about your database
What is the name of the Real Estate Agents table?
What are the field names in that table?
What is the form name that has the controls (not fields) for the seller
agent info?
What is the names of the each of the controls?
Does the buyer agent data go on the same form?
What are the names of those controls?
What is the name of the combo box for the seller agent?
Is there any code for any event to auto populate the seller agent controls?
If yes, please post it.
For the combo box for the seller agent, what is the recordsource? Post the
SQL.
Is the combo box for the seller bound (to a field) or unbound? (If there is
a field name in the CONTROL SOURCE property, then the combo box is bound)
-----------
-----------
For example, lets call the Real Estate Agents table "tblAgents".
The fields in the table are:
Field Name Type Remarks
--------------------------------------------
lngAgent_PK Autonumber Primary Key
txtAgentID Text
txtLastName Text
txtFirstName Text
txtCompany Text name of Agent's Company
txtContactPhone Text
Now create a query. Click on QUERY, then NEW. Add "tblAgents". Add the
fields and a calculated field. It should look like this:
SELECT tblAgents.lngAgent_PK, tblAgents.txtAgentID, [txtLastName] & ", " &
[txtFirstName] & " - " & [txtCompany] AS FullName, tblAgents.txtLastName,
tblAgents.txtFirstName, tblAgents.txtCompany, tblAgents.txtContactPhone
FROM tblAgents
ORDER BY [txtLastName] & ", " & [txtFirstName];
Name it "qryAgents".
Create a form. Name the form "frmAgentTest". You want to see the Form
Header/Footer. (In design view of the form, goto the menu bar/ VIEW and click
on "Form Header/Footer")
Set the properties to:
tab "FORMAT"
------------
"DEFAULT VIEW" - Single Form
Add a combo box to the form header. Change the label to "Seller" (no quotes)
Open the properties for the combo box.
Set these properties:
tab "OTHER"
-----------
"NAME" - "cboSeller" (do not include the quotes)
tab "DATA"
----------
"CONTROL SOURCE" -
"ROW SOURCE" - "qryAgents"
"BOUND COLUMN" - 1
"LIMIT TO LIST" - YES
tab "FORMAT"
------------
"COLUMN COUNT" - 7
"column widths" - 0";0";2";0";0";0";0"
(You can change the 2" if the column needs to be wider.)
In the detail section of the form, add 5 text boxes. I would align them
vertically.
For each text box, change the "NAME" property to something descriptive.
Something like this:
"tbSellerAgentID" (tb = text box)
"tbSellerFirst"
"tbSellerLast"
"tbSellerCompany"
"tbSellerPhone"
Change the associated labels to something appropriate for the text box. If
the name of the text box is "tbAgentID", the label might be "Seller Agent ID".
To populate the controls, the code for the "After Update" event would look
like:
Private Sub cboSeller_AfterUpdate()
'column property is zero based
'first field in combo box query is column(0)
Me.tbSellerAgentID = Me.cboSeller.Column(1)
Me.tbSellerLast = Me.cboSeller.Column(3)
Me.tbSellerFirst = Me.cboSeller.Column(4)
Me.tbSellerCompany = Me.cboSeller.Column(5)
Me.tbSellerPhone = Me.cboSeller.Column(6)
End Sub
The buyer combo box/controls are just like the seller combo box/controls,
replacing "seller" with "buyer".
------------------
------------------
Remember, this is just an example. All controls are unbound. Do not store
the seller info in the Jobs table, just the "lngAgent_PK" from the table
tblAgents.
HTH