Second request! Select two records from same table, autopopulate f

[Lisa]

I have a simple database for a home inspection business with three tables:
Clients, Jobs (one to many relationship of Clients to Jobs), and Real Estate
Agents.

I'm setting up the input form. All I now want to do is allow the user to
use a dropdown list to look up TWO separate Real Estate agents (one for the
Buyer and one for the Seller) from the same table and have their info
populate the fields on the form.

I can write the autolookup query for the form if using just one record from
the Agents table, but am having trouble with the joins in the query when it
comes to selecting two. I put two copies of the Agents table in the query,
but am still getting "ambiguous outer join" errors.

I need to see all the Agents at all times when using the combo box.

Any help would be much appreciated. Thanks.

 

[Beetle]

Do you have two separate fields in your Jobs table
(one for the BuyerAgentID and one for the SellerAgentID)?

If so you can use two separate combo boxes and set up a query
to use those combo boxes as criteria for those fields.

 

[Steve Sanford]

I would use two comb boxes - one for the seller agent and one for the buyer
agent. Get the combo box working for the seller agent (cboSeller), including
the code in the after update event to fill in the agent data, then copy the
combo box. Rename the combo box for the buyer agent (cboBuyer) and change the
code in the after update event to fill in the buyer agent controls.


HTH

 

[Lisa]

Thanks, but I'm afraid when it comes to the "code" part, I'm out of my league.

I use the wizard to set up the combo box. If I go into its properties
afterwards, and go to After Update, can I use the Expression builder as
opposed to coding it? If so, do I just pop in the Agents' table fields?

If I can get the one combo box working, I can duplicate it for the seller.

Thanks again.

 

[larry glover]

Thanks, but I'm afraid when it comes to the "code" part, I'm out of my
league.

I use the wizard to set up the combo box. If I go into its properties
afterwards, and go to After Update, can I use the Expression builder as
opposed to coding it? If so, do I just pop in the Agents' table fields?

If I can get the one combo box working, I can duplicate it for the seller.

Thanks again.

 

[Steve Sanford]

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