Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

A dropdown defines the contect of a list box

Status
Not open for further replies.

Kysteratwork

Technical User
Jan 3, 2005
45
LU
Hi,

I am trying to find a template for an idea:

Situation: I have a contacts database tblcontacts and a companies database, tblcompanies. When I enter a new name in the contact database (frmContacts), I would like to allocate a company (and the address and web site, etc details that goes with it) to this contact.

I would like to create a pop-up form, from which I can do a search for the company. The form could have two main items, a dropdown and a listbox. I should be able to chose a company from a dropdown and the since a company can be present in various cities, I would like to see all the street names and cities where the company may be located in the listbox. Like this I could double-click on the row with the street name, which then populates the company field (and the respective address) in the frmContacts. It could also be done with two dropdowns, I guess.

So, the result of one dropdown determines the list of values in another dropdown (or listbox).

My programming skills are quite limited. Would you maybe know of a template I could adapt to this purpose??

Thank you in advance for your thoughts!

Regards,
Kysteratwork
 
Hi
Have a look at this faq702-4289, it should help.
 
Ah, yes, that helps.

However, the company names and addresses are in the same table tblCompanies (and contain cmpstreet and cmpcity).

I would the have to change this part
Code:
CboModel
RowSource: SELECT tblModels.lngModelId, tblModels.strModelName, tblModels.lngManufacturerId
FROM tblModels
WHERE (((tblModels.lngManufacturerId)=[Forms]![Form2]![cboManufacturer]))
ORDER BY tblModels.strModelName;
to reflect that. Could you help me with that?

Chris
 
Hi
Just fill in your fields. Let us assume that tblCompanies has a primary key called CompanyID, then you get:
Code:
RowSource: SELECT tblCompanies.CompanyID, tblCompanies.cmpstreet, tblCompanies.cmpcity
FROM tblCompanies 
WHERE (((tblCompanies.CompanyID)=[Forms]![Form2]![CompanyID]))
ORDER BY tblCompanies.cmpstreet;
OR You can combine street and city:
Code:
RowSource: SELECT tblCompanies.CompanyID, 
[tblCompanies.cmpstreet] & ", " & [tblCompanies.cmpcity] AS Location FROM tblCompanies 
WHERE (((tblCompanies.CompanyID)=[Forms]![Form2]![CompanyID]))
ORDER BY tblCompanies.Location;

You will need to set the properties for Column Count and Column Widths slightly differently for each option.
Order By is not necessary, but it is useful.

I think the Northwind sample also has some stuff on this.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top