Issue Regarding linked combo boxes 1

[WantoN]

Hi guys,
After reading the faq regarding this, I'm having some issues with linking the first two drop boxes on the form I'm creating.

The form takes info from two tables:
Company:- companyType, companyArea, PostalCode, Company Name

Contact:- Salutation, Forename

I have two drop down list boxes, the first I want to list the CompanyType, and the second I want to list the CompanyArea.

I have put the following in the Row Source:

cboCompanyType:- SELECT Company.CompanyID, Company.CompanyType FROM Company ORDER BY Company.CompanyType;

cboCompanyArea:- SELECT Company.CompanyArea, Company.Type, FROM Company WHERE (((Company.CompanyID)=[Forms]![ContactLookup]![cboCompanyType])) ORDER BY Company.CompanyArea;
SELECT Company.CompanyArea, Company.CompanyID, Company.Name FROM Company WHERE (((Company.CompanyID)=[Forms]![ContactLookup]![cboCompanyType])) ORDER BY Company.CompanyArea;

I get an erro when clicking the second box, and I'm not sure why. I'm sure it's a simple issue so any help would be appreciated!

Also, a number of companies fall under the same area or type; how do I limit the number of each type displayed to 1, instead of having multiple types of the same name listed for each company?

Thanks guys,
Antoni

 

[WantoN]

Edit: Sorry pasted the code for the second box twice!

 

[PHV]

I get an erro when clicking the second box
Any chance you could post the whole error message ?
how do I limit the number of each type displayed to 1
Have a look at SELECT DISTINCT

You may try this in the AfterUpdate event procedure of cboCompanyType:
Me!cboCompanyArea.RowSource = "SELECT DISTINCT CompanyArea, CompanyID, [Name] FROM Company WHERE CompanyID='" & Me!cboCompanyType & "' ORDER BY CompanyArea"
If CompanyID is defined as numeric then get rid of the single quotes.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WantoN]

Update, I've resolved the error message, but the box cboCompanyArea isn't displaying any data, it's empty.

Rowsource for the boxes is as follows:
cboCompanyType: - SELECT Company.CompanyID, Company.CompanyType FROM Company ORDER BY Company.CompanyType;

cboCompanyArea: - SELECT Company.SalesArea, Company.CompanyID FROM Company WHERE (((Company.SalesArea)=[Forms]![ContactLookup]![cboCompanyType])) ORDER BY Company.SalesArea;

Form Code is as follows: -

Private Sub cboCompanyType_AfterUpdate()
cboCompanyArea.Requery
End Sub

Private Sub cboCompanyArea_GotFocus()
cboCompanyArea.Requery
End Sub

I'd like both CompanyType and CompanyArea to be distinct, but I'm not quite sure how to proceed. Thanks for help so far PHV.

Antoni

 

[PHV]

Well, I'm a little confused...
Perhaps this ?
cboCompanyType:
RowSource: SELECT DISTINCT CompanyType FROM Company ORDER BY CompanyType
ColumnCount: 1
AfterUpdate event procedure:
Me!cboCompanyArea.RowSource = "SELECT SalesArea, CompanyID FROM Company WHERE CompanyType='" & Me!cboCompanyType & "' ORDER BY SalesArea"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WantoN]

When that's done, both CompanyType and CompanyArea are blank... Should I retain the form code?

I'm a little confused myself. I also get an error code about not being able to find the macro named 'Me!cboCompanyArea'

By the way, ultimately there will be four boxes, but I assume that once I've resolved this I can just apply how it's done to the rest of the boxes.

I'm using access2k by the way. Sorry about this PHV, you've been great so far, thanks.

Antoni

 

[Eupher]

WantoN,

I think perhaps you put PHV's code:

Me!cboCompanyArea.RowSource = "SELECT SalesArea, CompanyID FROM Company WHERE CompanyType='" & Me!cboCompanyType & "' ORDER BY SalesArea"

...in the wrong place. Did you put it in the AFterUpdate event procedure of cboCompanyType?

Ken S.

 

[WantoN]

Ok kev mate. To clarify, I went to the properties of the list box cboCompanyType and placed it in Events>AfterUpdate.

Was this the wrong thing to do?

Also, the SELECT DISTINCT doesn't seem to have any affect on the combo box :S

Thanks again,
Antoni

 

[Eupher]

Hi, Antoni,

You need to place the cursor in the Events->After Update field, then click on the little button with the 3 ellipsis points that appears to the right of the field, then select "Code Builder". A VBA window should open, with the cursor positioned within an event procedure named:

[tt]Private Sub cboCompanyType_AfterUpdate[/tt]

That's where you want to insert the code, right where the cursor is positioned within that sub.

I'd like both CompanyType and CompanyArea to be distinct

You're getting duplicate values in cboCompanyType even when using the DISTINCT keyword? Did you remove the CompanyID field from the query grid?

Ken S.

 

[WantoN]

You have to appreciate I really know very little about access

Basically last night I had the linked combo boxes working (well two of them) but when the third correctly displayed the postcodes, I clicked on it and nothing was selected in the box :/

When I removed CompanyID from the RowSource, the field became blank, I'm rather confused. To clarify, here are my codes as they stand at the minute:

cboCompanyType : - SELECT DISTINCT Company.CompanyType FROM Company ORDER BY Company.CompanyType;

Bound Column: 2, Column Count: 2

cboCompanyArea : - SELECT Company.CompanyID, Company.SalesArea FROM Company WHERE (((Company.CompanyType)=[Forms]![ContactLookup]![cboCompanyType])) ORDER BY Company.SalesArea;

Bound Column: 2, Column Count: 2

cboCompanyPostcode : - SELECT Company.CompanyID, Company.PostalCode FROM Company WHERE (((Company.SalesArea)=[Forms]![ContactLookup]![cboCompanyArea])) ORDER BY Company.PostalCode;

Bound Column: 2, Column Count: 3

In the forum code I have :
Private Sub cboCompanyType_AfterUpdate()
cboCompanyArea.Requery
End Sub

Private Sub cboCompanyArea_AfterUpdate()
cboCompanyPostCode.Requery
End Sub

Basically I want all the three combos boxes to display distinct values only. And to allow me to select and narrow down the data.

I thought I had it cracked but I'm finding it difficult.

Thanks Guys,
Antoni

 

[PHV]

I want all the three combos boxes to display distinct values only
cboCompanyType : - SELECT DISTINCT CompanyType FROM Company ORDER BY CompanyType;
Bound Column: [highlight]1[/highlight], Column Count: [highlight]1[/highlight]

cboCompanyArea : - SELECT DISTINCT SalesArea FROM Company WHERE CompanyType=[Forms]![ContactLookup]![cboCompanyType] ORDER BY SalesArea;
Bound Column: [highlight]1[/highlight], Column Count: [highlight]1[/highlight]

cboCompanyPostcode : - SELECT DISTINCT PostalCode FROM Company WHERE CompanyType=[Forms]![ContactLookup]![cboCompanyType] AND SalesArea)=[Forms]![ContactLookup]![cboCompanyArea] ORDER BY PostalCode;
Bound Column: [highlight]1[/highlight], Column Count: [highlight]1[/highlight]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WantoN]

Sorry PH mate, but I've entered it exactly as stated, and no joy.

All three boxes display no information, the first displays a drop down with an arrow, but all info is blank, the second two have nothing.

When I click the third drop down (postcode) I'm given the error message "Extra ) in Query expression CompanyType=[Forms]![ContactLookup]![cboCompanyType] AND SalesArea)=[Forms]![ContactLookup]![cboCompanyArea]"

Sorry about this fellas Thanks again for so far.

Antoni

 

[WantoN]

Hmmm, I seem to have rectified the error message regarding the expression, by removing the ")" after SalesArea. However I'm still really at a loss as to why the information isnt appearing as expected in the boxes.

There's evidently something minor that I've over looked, all additional help is really appreciated.

 

[WantoN]

I've managed to resolve the problem!

It was actually a case of the first column widths being set to 0cm, and as such they were invisible.

I've made some small adjustments to the code to get everything work, and now I've achieved everything I wanted.

Much thanks go to the guys who've helped me so much.

Antoni