What type of control to display data?

[m_elect]

I have these tables:
jobs,
complex,
managementCompany,
managers.

I built a form for each table. In the form for jobs I have a combo box for the complex field that's based on a query to bring in the complex, manager, managementCompany and address. I created text boxes for manager, managementCompany and address and they each refer to a column from the complex combo. This works great and I don't repeat the data in the jobs table. I want to include the phoneNumber field for the managementcompany so I created a text box for it. Since phoneNumber is not stored in the complex table, I can't use the complex query. I'm not sure what to do to display it.

I wrote a query that should be close:

select phoneNumber
from complex_table as a, managementCompanies_tables as b
where a.managementCompany = b.managementCompany

Thanks.

 

[GinaWhipp]

You should use DLookup() in a TEXT BOX...

If your ID field is numeric...

=DLookup("FieldFromTableOrQuery", "YourTableOrQuery", "[FieldFromTableOrQuery]=" & Me![FieldFromForm])

If ID field is text...

=DLookup("FieldFromTableOrQuery", "YourTable", "[FieldFromTableOrQuery]='" & Me![FieldFromForm] & "'")

 

[m_elect]

I must not be using it incorrerctly, here's what I used:

=DLookup("phonenumber", "managementCompanies_table", "[managementcompany]='" & Me![managecoview] & "'")


managecoview is a text box on the jobs form that displays a column from the query that fills the combo box for complex.

phonenumber only exists in managementcompanies_tables.

 

[missinglinq]

Assuming that [managecoview] is defined as Text rather than as a Number, your syntax appears to be correct.

Are the other Textboxes being populated when the way selection is made from the Combobox?

Are all of your names in the DLookup spelled correctly?

Assuming the names are correct, you might substitute the Combobox column you're using to populate the [managecoview] Textbox for the Textbox in the DLookup function.

Linq ;0)>

 

[m_elect]

Assuming that [managecoview] is defined as Text rather than as a Number, your syntax appears to be correct.

managecoview is a textbox with controlsource =Complex.column(2)

Are the other Textboxes being populated when the way selection is made from the Combobox?

There are 6 fields that get populated from the combobox

Are all of your names in the DLookup spelled correctly?

Assuming the names are correct, you might substitute the Combobox column you're using to populate the [managecoview] Textbox for the Textbox in the DLookup function.

Linq ;0)>

I'll have to think about this. I really need the combobox to be used for the complex.

I guess the question is will the dlookup work when you the value you need is in a third table. The field I need on the jobs table is not in the complex table. I need to link the complex from the jobs table back to the managementcompany in the complex table and then use managementcompany to get the phonenumber from the managementcompanies_table.

 

[GinaWhipp]

What field is the BOUND column in the Combo Box? And does it match managementcompany?

 

[m_elect]

What field is the BOUND column in the Combo Box? And does it match managementcompany?

The bound column is complex. Would it be a better method if I built relationships and created a query?

 

[GinaWhipp]

Are you saying it is BOUND? If that is what you are saying, what is it BOUND to? In other words, is it BOUND to a TEXT or NUMERIC field and does that field match the field in the table *managementcompany*?

 

[m_elect]

Are you saying it is BOUND? If that is what you are saying, what is it BOUND to? In other words, is it BOUND to a TEXT or NUMERIC field and does that field match the field in the table *managementcompany*?

I think we're off the tracks, Gina. I made a few changes to make this clearer. Here are my tables and fields:

ManagementCompanies_table:
ManagementCompany (primary key)
PhoneNumber

Complex_table:
complex_abbr (primary key)
complex
address_line1
managmentcompany (linked to ManagementCompanies_table.ManagementCompany)

Jobs_table:
jobNumber (primary key)
name
complexAbbreviation (linked to complex_table.complex_abbr)
startDate
finishDate

In the jobs table, complexAbbreviation is a combobox that contains all of the complex_abbr from the complex_table. Other than the complex_abbr, the combobox also gets managementCompany from the complex_table and displays it in the jobs form in a text box. (the code is =ComplexAbbreviation.column(2)).

So, how can I display the corresponding ManagementCompanies_table.phonenumber for the complexAbbreviation that has been selected on the jobs form?

 

[GinaWhipp]

What is the RecordSource of the form? Need that to write the DLookup() for the UNBOUND Text Box...

 

[m_elect]

What is the RecordSource of the form? Need that to write the DLookup() for the UNBOUND Text Box...

The record source of the jobs form is the jobs_table. I don't think a single dlookup is going to work because phonenumber is in managementCompanies_table. I wrote a nested dlookup:

=Dlookup("phonenumber","managementCompanies_table", "[managementcompany]='" &DLookup("managementcompany", "complex_table", "[complex_abbr]='" & Me![complex_abbreviation] & "'")

but it doesn't work.

 

[GinaWhipp]

No, that won't work... Can you add the Complex_table to the Record Source?

 

[m_elect]

No, that won't work... Can you add the Complex_table to the Record Source?

I don't know how. I tried to drag the complex_table onto the jobs form but that doesn't work. I made another form of the complex_table with fields complex_abbr, managementcompany. What do I do link complex_abbr of the subform to complexabbreviation of the jobs form?

 

[GinaWhipp]

Create a query with ththe tables and then use the query as the RecordSource of the form.