Problems with Combo Boxes Again

[djclntn]

Just when I thpught to have something down when I try it again to doesn't work



In one of my Forms I want to set Combo Boxes for the 'City' & for the 'ZipCode', but I'm coming up with duplicates in my Combo Box. Like if I have the city of Philadelphia twice (in 2-Records) it shows up twice when I pull down my Combo Box -- whereas it ought to only apear once (in the Combo Box list)

I follow exactly what someone here told me to do which worked in other Forms, but not this time...?

After I go through the Combo Box Wizard (first or the city) I
- In the Control Source I have, City
- In the Row Source I have, SELECT [Dealerships].[DealershipID], [Dealerships].[City] FROM [Dealerships]; which doesn't look right. I tried just putting, SELECT [Dealership],[City] FROM [Dealership] but it's not working for some reason...?
- In the Row Sourse Type I have, Table/Query

It's so frustration when you think that you understand in doing something & when it doesn't work you can't figure out why/what's different. Any help would be apprechiated!

[June7]

When you use a field as source for combo or list box that has multiple occurrences of values, use the DISTINCT keyword.

SELECT DISTINCT ZipCode FROM Dealership;

What you might should do is have a table of City, State, Zip records. Then all you really have to do is select the zip and the city/state info can be retrieved by join on the zipcode.

[djclntn]

<SELECT DISTINCT ZipCode FROM Dealership;> didn't quite work for me.

I created a ZipCode Table with the fields, 'City' St' & 'ZipCode' In my Dealership Table I took out the ''City' Field & kept the 'ZipCode' Field. When you write, <select the zip and the city/state info can be retrieved by join on the zipcode.> might you be more specific? Do you mean in the Relationship window or in a query? I attempted to do in a query, but got no results

Database mission: This database/project is a used car wholesalers who sell (or dealers buys vehicles from) & the wholesales gets a commision of what the dealership sells the vehicle for -- that's the idea I have in mind thus far. I completely cut out all of the Customers & SalesRps' data with this database.

[June7]

Two ways to pull the related location info onto form.

1. Join tables in form's RecordSource. Jointype 'show all records from [table name] and only records from Dealerships that are equal'. The related info will be available for disply in textboxes. Don't allow edit of the city/state info. Set textboxes as Locked Yes and TabStop No.

2. Multi-column combobox. RowSource query that retrieves all location fields. Textboxes on form refer to columns of combobox to show related city/state info.

[Bob Fitz]

djclntn

OK, as promised in another thread (https://www.accessforums.net/showthr...576#post108576) I've had a read through this one but I'm not sure how I can help here. If you have a specific question, please post it, but I'm sure that June7 will be able to help you more than I because his knowledge of Access is much greater than mine. Indeed, he already seems to have a solution for you

[djclntn]

June,

Not that it ought to matter, but I always thought you to be a female. However, from Bob's last post on this thread Bob eluded to you as a, "he."

Anyhow, I'm still greatly struggling with setting up Combo Boxes in some other Forms. There's no limit on how many Combo Boes you can have in a Form is there? Reverting back to my single Dealership database which I still maintaining I want to go back into the Customers Form & the Employer Form & change the 'City' &'ZipCode' fields into Combo Boxes, but it's not letting me. Now I hav 'City' & 'Zipcode' data stored in a table called, Delaware County Zip Codes (which doesn't need to be related to any other table in the Relationship Window, right?). Taking the Customer Form when I go through the Combo Box Wizard for the 'City' I choose both the city & ZipCode.

In the Control Sourse I have, City
In the Record Source it has, SELECT [Delaware Co Zip Codes].[City], [Delaware Co Zip Codes].[ZipCode] FROM [Delaware Co Zip Codes];
Inthe Record Source Type have, Table/Query

When I open the Customer Form in the 'City' field every record has, "#Name?" Now I'm able to drop down the list which shows all of the cities & zipcodes but it doesn't allow me to choose any of the cities. Might you have some idea what is the problem?

June, not to complicate or overwhelm you, but I have an isssue/question regarding queries. I don't know if I ought to start another thread or mybe you can help me after/once we figure out this Combo Box issue.

As alway, thanks!

[June7]

Forms do have limit on how many total controls it can hold in any assortment.

Whether or not the zip codes table should have relationship depends how you are using it.

Some cities can have more than one zip code. So are you using the zip code field as a primary key? Or are you saving both the zip and city into tables?

Do you want to provide latest version of db for analysis?

If the query question is unrelated to the zip code table issue, could start another thread.

[djclntn]

It's not letting me uploade the file. I'm working on it

[djclntn]

June,

In the Customers & Employers FORMS I want the 'City' & 'zipcode' to be in combo boxes

[djclntn]

I can'tupload the latest version...I don't know why

[djclntn]

June, I'm unable to send you the latest version of the dbs. I don't know where to go from here

[June7]

I do see a file attached to the second post following mine. Is that not the latest? What happens when you try? Alternative, upload to fileshare site such as box.com and post link to the file.

[djclntn]

I'm shutting down for the night; can I get back with you tomorrow...

[June7]

My suggestion is that there is no need to have city and state saved in the Customers and Employees tables. Save the zipcode and can always retrieve the city and state by joining tables on the primary key fields, which could be the zipcode itself since zipcode must be unique in the zipcode table. Or use an autonumber field as pk/fk then the zipcode would not even be saved in Customers and Employees. Regardless, really only need one combobox in order to select the city/state/zip record. Label caption could be "Select City/State/Zip:". Then the RowSource could be:

SELECT Zip, City & ", " & State & " " & Zip As CSZ FROM ZipCode ORDER BY City, State, Zip;

Other combobox properties:
ControlSource: Zip
BoundColumn: 2
ColumnCount: 2
ColumnWidths: 0";2"
AutoExpand: Yes

As users type city, the combobox will match.

[djclntn]

I's not letting me Deltete city & zip from the cusstomers table & I've deleted every Relationship I could find?