Using a join table to autofill data on a form

[Jim_H]

Not sure what you are asking. What am I doing wrong with the ZipCode? In any case, as I said in my original post, I'm familiar with the basic principles, but I haven't done anything with building a database in over 20 years and I'm breaking off the rust.

[madpiet]

Zip code is basically a 5-digit number... the mapping of one ZipCode to one or more (City, State) is not perfect, good. So what do you gain by using an altenate key for it, because I don't see it.

[June7]

As already mentioned, can provide db for analysis. Follow instructions at bottom of my post.

MadPiet, save ID PK in case a zip code is used for multiple community names.

ZipID	ZipCode	City	State
1	94608	Emeryville	CA
2	94608	Oakland	CA

Dave's sample db deals with that situation.

[Welshgasman]

Not sure what you are asking. What am I doing wrong with the ZipCode? In any case, as I said in my original post, I'm familiar with the basic principles, but I haven't done anything with building a database in over 20 years and I'm breaking off the rust.

Firstly, when there have been a few posts between your and what you are replying to, it is better to quote that post, as I have done here, despite it being the next post.
I presume you are responding to @madpiet's post #11?

I believe he meant there was no need for the ID, you could just as easy used the zipcode itself, as it is unique anyway?
However if the zipcodes were likely to change, there could be the need for a surrogate key.

[madpiet]

No, I don't want to store the city and state names in tbl_Persons, I just want the tbl_CityZip ID to be stored. But I do want the city and state names to be displayed on the Contact form so that when the user views a record in a readable format, the city name and state name are displayed as text. That's the problem I'm having right now, though, is figuring out how to have that happen.

How do I have the zip combo box bring in the other data? And how do I setup the AfterUpdate event display the text of the city and state, but store the data IDs?

You'd just have a couple of unbound textboxes on your form - one for City, and another for State, and each one would have a controlsource of =cboZipCode.Column(1) or =cboZipCode.Column(2) (because 0 is the first one... ) Then users could see the city and state displayed.

[Jim_H]

Hi
Can you upload the database with no confidential data so we can see what is happening?

See attached. I added a note field on the Contact form to indicate what the zip code and city should actually be.

FBE Manager Reduced.zip

[Jim_H]

Zip code is basically a 5-digit number... the mapping of one ZipCode to one or more (City, State) is not perfect, good. So what do you gain by using an altenate key for it, because I don't see it.

Thank you. Most every thing I read said that the best practice for storing a zip code (and phone numbers) is to store it as a string (primarily due to the potential of leading zeroes). I did that and then tried using it as the primary key, but when I had problems trying to get the data to show up in the form the way I wanted, I thought it may have to do with the data type, so I added an integer primary key.

[Welshgasman]

It is only showing what you have constructed?

[Welshgasman]

I must admit, I have no clue what you have done to get this.
Got it (I think). You had the bound columns as 0 ?

Anyway I change the embedded sql to a query for comparing.
You can remove the extra columns from the combo.

[Jim_H]

I must admit, I have no clue what you have done to get this.
Got it (I think). You had the bound columns as 0 ?

Anyway I change the embedded sql to a query for comparing.
You can remove the extra columns from the combo.

Yes, I had the bound column as 0 - I thought that's what it should be (that combo box field was referencing the join table for zip, city and state, and the primary key of that table is column 0).

The database I uploaded was a copy of the main database and only had a few records to show what the problem was. Does the SQL just compare, or does it fix the problem? If it fixes the problem, how would I incorporate it into the full database? It looks like it is only comparing a single key.

[June7]

Combobox (or listbox) columns can be referenced in expressions by their Index which begins with zero. However, count the columns starting with 1 and that is what the BoundColumn property expects.

[Welshgasman]

You can just set the bound column to 1 as that should store your ZipCityStateID. Second column should be what you want to see in the combo.
That is how I always set it up. First column, the stored value, second what is shown at a minimum, and then other data required, for wahever reason.

After that, just add what you need. In this case, just the town.
Column count should be 3, with widths of 0;2;2 The last 2 is whether you want to see the town as well when selecting. I just used that to confimr what was coming in. If you do not want to see that set that to 0 width as well.
Now your textbox should reference column 2.

My query is just your embedded sql for the combo, that I copied. That was I could see all the data away from the form.

I must admit I was flummoxed with it at first. I have never set a bound combo to 0, and if that is actually the cause, it has a nasty side effect.
I did easily amend your data but just selecting the previous entry in the combo and everything worked as it should.

[Jim_H]

@June7 and @madpiet - thank you both for our time and help with this!

...
After that, just add what you need. In this case, just the town.
Column count should be 3, with widths of 0;2;2 The last 2 is whether you want to see the town as well when selecting. I just used that to confimr what was coming in. If you do not want to see that set that to 0 width as well.
Now your textbox should reference column 2.
...
I must admit I was flummoxed with it at first. I have never set a bound combo to 0, and if that is actually the cause, it has a nasty side effect...

@Welshgasman - thank you as well! This bit here really helps me understand what I need to be doing with the combo box for what I'm trying to do. Binding it to 0 does indeed appear to be the cause - I created a new form to experiment with, and sure enough, with the combo box bound to 0 I got the problem results, but as soon as I changed it to 1 I got the expected results.

[Welshgasman]

I forgot to mention, I also removed the initial control name from your control source for the textbox. You only need =combocontrol(2) not =text1=combocontrol(2)

[Jim_H]

I forgot to mention, I also removed the initial control name from your control source for the textbox. You only need =combocontrol(2) not =text1=combocontrol(2)

Thanks for the tip!