Lookup column

[drvo]

Hi

I am new here and I come with one question.



I have two tables, i.e. two forms. In one table are companies (company name, comany town, company id...) and in second are persons (ID, name, company-town, companyID...). I also have querry where I make new expression "CompanyName-CompanyTown".
Now, in persons table I have lookup column connected with mentioned querry. In that way I connect person with company ("company-town" is unique expression). Now, after in Persons form I choose Company-town from drop-down list, I want to save ID that belongs to that company in special field companyID on persons form. Can anyone help with this?

I hope I was understandable.

Tnx for help!

[John_G]

Hi -

First, your tables are not properly normalized. Your Persons table should contain only the company ID, not the company name, company town, etc.

In your Persons form, your dropdown list (combo box) will have Company_ID, CompanyName, CompanyTown as its row source (you can use the query for that). Make the Company_ID invisible in the combo box by setting its column width to 0".
Set the Control Source property of the combo box control on the form to the Company_ID field in the Persons table.

So now your dropdown list will show you the company name and town, but will store the Company_ID in the table.

[alansidman]

Look here for db design, ie. normalization

http://www.deeptraining.com/litwin/d...aseDesign.aspx

[drvo]

Thank you both for response!

I am aware that unfortunately normalization of my db is not done well. As I have a lot of tables, forms and querries, I am trying to correct things and not to start from the beggining.

I changed a few things, but I am still not getting what I want. Namely, in Contacts table, in field named Company I set lookup properties as follows: row source: querry which sets companies in ascending order; bound column:1 ; column count: 3 (Company_ID, Company_Name, Company_City). I also set first column width to zero. Now, when user choses one company from the list, only company name is shown. What I want is that when db user choses from the list, the company ID (first column) to be saved, but company name and company city to be shown. Is it possible to do it that way?
Also, in company form I have subform that is again related to contacts table and I want to filter it by company ID. Can it be done, since the Company_ID in contacts table is "hidden"? Currently I have the following code, since I am filtering it by "Company_Name-Company_City" expression.

Private Sub Form_Current()
Dim subFilterContacts As String

If Not IsNull(Me.ID) Then
subFilterContacts = subFilterContacts & "([Company] = """ & Company_Name & "-" & Company_City & """)"
End If

Me.subfrmContacts.Form.Filter = subFilterContacts
Me.subfrmContacts.Form.FilterOn = True

End Sub

I would appreciate if somebody could direct me.

[alansidman]

What I want is that when db user choses from the list, the company ID (first column) to be saved, but company name and company city to be shown. Is it possible to do it that way?

To be shown Where? In Table--No. In Form or Query? Yes. If in Query, then join your main table with the lookup table and have it display the name from the lookup table. You already have it displayed in the form.

[drvo]

I joined main querry with lookup querry, but problem is still there.
Problem is that I don't have displayed Company_Name AND Company_City in the form. In the combo box, in drop-down list, I can see both (column width of first column is set to zero), but when I choose from drop-down list, all I can see in control on form is Company_Name. Is it possible to see both in one control on a form?
Also, in Contacts table (i.e. querry), I want Company_ID to be saved, but when I set ID column width to zero, Company_Name is rather saved, not Company_ID.

So, I have three columns in lookup, I want first to be saved in related table, and second and third to be displayed in control on a form. Is that possible?

lookup properties: Bound column:1 ; Column count:3 ; Column widths:0;3;3

[alansidman]

I am having some difficulty envisioning this. Please post a copy of your database so that we can see what is happening. Remove all confidential data (make it dummy data) and only have enough data so that we can see what is happening.

[drvo]

So, my file is in an attachment. It is totally simplified, but the main problem can be seen.

When you open frmContacts, you have several controls. Among others, there is control named "Company". When you open drop-down list, you can see Company_Name and Company_City (from qryCompaniesAbc), but when you choose one, all that is left in control is Company_Name. Is it possible to see Company_City as well (other column)?

Also, in frmCompanies you can see subfrmContacts. Although I see that in tblContacts, in "Company" field, Company_Name is saved (shown), I managed to filter this subform on ID_Companies, as it can be seen in the code behind the frmCompanies. So, this part of problem is solved.

[alansidman]

Because a control (textbox) is limited to one value of data, you need to have another control to display the location. See how I did it in the attached. Look at the properties in the after update event for the company combo box.

[drvo]

Good idea, this works fine with another control for location. BUT this event doesn't work as it should. Namely, location is generated in new control, but when I close and reopen the form, it can't be seen. Also, when I move to next record, company in that record also changes location. Therefore, I removed that event and in Control Source properties for new control I put [Company].[Column](2). Now it works properly.
Thank you for your help!