Need help with Category/Product type option box

[danbo]

This DB is for a small phone company. What I would like to do is be able to check the cable pair numbers going to houses based on the road that the house is.

Cable pair 1 feeds service to a house at 123 main street
cable pair 2 feeds service to a house at 455 main street
Cable pair 3 feeds service to a house at 789 main street
Cable pair 4 feeds service to a house at 222 south street
Cable pair 5 feeds service to a house at 888 south street

My "customer" table is currently like:

ID LName, Fname, House#, Street, Pair#, comments, etc.....
23 smith Bill 123 Main 1 blue house
24 doe John 455 Main 2 Green house

In a form, I would like to select the street name(combo box?)
after the street name is selected I would like the possible addresses from that street to be displayed. For instance:
I select Main street from a combo box
the next box would show:
123
455
789
When I select one of those listed addresses the final result would display.
#4

Main Street
123
Cable Pair.

Any thoughts would be appreciated. thanks in advance.

[June7]

Sounds like you need cascading (dependent) comboboxes. See this tutorial http://datapigtechnologies.com/flash...combobox2.html

Or maybe one combobox that concatenates street/house/pair as one value. Maybe additional column for the pair alone. Depends what you want to do with the data after it is identified.

Set combobox to AutoExpand Yes. As user types street the box will fill with matching value.

BTW, use of special characters (#, $, etc), spaces, punctuation (underscore is exception) is not advised. Better would be HouseNum and PairNum.

[danbo]

thanks for the link and the tip. I'll take a look at it all Thanks.
One thing I'm realy shooting for is being able to find a range of cable numbers for a certain area. the idea being that a technician can see what cable range a new customers house would be on. For instance, a new house between 123 main street and 129 main street would be cable pairs 1-299. I may be getting more involved than i need to but it seems like this project is just asking for more and more as I find more "creature comforts" from what we use now.
Thanks again.

[June7]

Ooops! I edited my post while you were posting. Take another look at it.

[danbo]

Sounds like you need cascading (dependent) comboboxes. See this tutorial http://datapigtechnologies.com/flash...combobox2.html

Or maybe one combobox that concatenates street/house/pair as one value. Maybe additional column for the pair alone. Depends what you want to do with the data after it is identified.

Set combobox to AutoExpand Yes. As user types street the box will fill with matching value.

BTW, use of special characters (#, $, etc), spaces, punctuation (underscore is exception) is not advised. Better would be HouseNum and PairNum.

It seems like every example that I have found on this topic has classifications for the original box.
example:
Categories =

Beverages
Food
Desert
Adult Drinks
Apetizers

then each of those have their own product.

Food
Hamburger
Fries
Hot Dog

Beverages
Soda
Milk
Coffee
etc.

I can see how this works, but what about when you have multiples of the same "category" such as in my situation where the category section would have many of the same name due to having multiple customers.

It almost seems like I would have to have a query that lists all of the street names by themselves
Main
Front
Locust
Hickory
Elm

and then another with the options of each of those streets,
123
456
789
978
654
321

Is there a simpler way to get this list? I would hate to have to list each road and each corrospending address separatly.
PS. Im very very new to access.
Thanks for your patience!

[June7]

To have combobox RowSource return a single row for each value, use the DISTINCT qualifier:

SELECT DISTINCT Street FROM customer ORDER BY Street;

You can type DISTINCT into the query SQL statement in the query SQL view.
Or set UniqueValues to Yes in the query Property Sheet.

[danbo]

To have combobox RowSource return a single row for each value, use the DISTINCT qualifier:

SELECT DISTINCT Street FROM customer ORDER BY Street;

You can type DISTINCT into the query SQL statement in the query SQL view.
Or set UniqueValues to Yes in the query Property Sheet.

I'll look into how to do this. Thanks for the tip!
Is this something that goes on the query or on the control? sorry for the ignorant question but again, I'm very new to access or any language other than English, and I'm not too good at that one.

[June7]

The SELECT statement goes in the RowSource property of combobox.

[danbo]

The SELECT statement goes in the RowSource property of combobox.

IT'S WORKING!! Now just to get the other combo boxes to communicate with it and I will be done with this project in time to get my year-end "pat-on-the back" from the boss!!

[danbo]

The SELECT statement goes in the RowSource property of combobox.

June, you're a superstar! I got it all working the way I wanted it to thanks to your help. I have one more question for you.
the current design is:

Fist combo = Street
Second = Address
Third = Cable Pair

If I select the street, the address and then it displays the correct cable pair. however, when I re-select street all of the other information remains as selected the first time around. If I click in the box the correct options are listed because of a requery I place afterUpdate. My question is, can a person do a onclick, or enter event that will clear the information from cmbaddress and cmbpair when you enter or click into cmbstreet?

Thanks again for your help. You're terrrrrrrific!

[June7]

Yes. The trick is getting the code into the right event(s). My practice is to set the second and third comboboxes Visible property to No so that user cannot jump over to those when form first opens until first is completed. Then in the AfterUpdate event of each combobox set the Visible property of next box to yes to make it available.

Are you using the entered values to filter/search for a record or to open another form? If so, put code in whatever event runs that process to reset the visibility and set comboboxes to null. Otherwise, can put this in the last combobox AfterUpdate event.

Just double checking - these are unbound comboboxes, right?