Anyone experienced this before? (3 Viewers)

[Gasman]

Member on another forum was doing the zip/city/state issue and had a combo for the zipcode data and wanting to show the city in a textbox for the combo value.

They reported that the combo was offering one more record than it should be.
Finally we managed to see an example db and sure enough, that was happening?

Turns out this appeared to be caused by having a bound field = 0.
The member tested this out again on a new db and was able to repeat the issue?

 

[theDBguy]

They reported that the combo was offering one more record than it should be.

What exactly does that mean? I just tried this and although I thought I would get an error when I set the bound column to 0 but I didn't, the combo seems to work normally - displaying exactly what I selected and the textbox for the other column also displayed what it's supposed to. I can't see the problem.

 

[Gasman]

The actual id value showed the next record.
So if we had
78383 town1
78384 Town2

78383 would show town2.

I can upload the dB if you want?
No actual data there except zipcode, cities and states.
It *appears* to all work normally, but does not show the data for the actual zip code, but the next records value.

 

[Gasman]

Here you go, this is the original sample db.
The member added notes as to what should be shown.
Also attached is my version. I replaced the embedded sql for the combo with a query so I could compare. I just copied that sql and made a query.

If you go throught the convoluted connections, you should see the actual data should be the previous record. Very, very weird, to me at least.

Edit: I just remembered I had to remove the first control reference from the txtbox value as they had =control1=control2.column(1)

 

[theDBguy]

Hi @Gasman

Is the problem happening all the time or only on certain zip codes? The first thing I noticed on the original db file was that there are 1447 records in tbl_Zip and there are 1465 records in tbl_ZpCityState, which means there are 16 duplicate zip code values in the combobox. Since the combo does not include the ZipCityState_ID column, maybe it's possible the problem is caused by duplicate values in the "bound" (first) column.

PS. BTW, does changing the bound column to 1 fixed the problem? I tried but can't tell the difference at the moment.

 

[June7]

Reported in other thread that changing to 1 fixed problem.
I finally examined the db and can see the issue.
And I was able to replicate in my db.

 

[Gasman]

My discoveries were that setting bound to 1 and removing that errant =Text1=combo.column(1) fixed the issue.
From what i can recall (as my memory is rubbish these days, the zipcitystateID was 1178 and so should show what is shown in notes. However it happens to show the next record? That is why I created a query from the embedded sql for the combo to compare at the same time. 1178 *should* show what the notes say it should. Yet, it was showing the next record in the combo, in the original db.?

 

[Gasman]

Reported in other thread that changing to 1 fixed problem.

Yes, but I was intrigued about such a circumstance.
Noone in their right mind would have a bound column of 0, even for an unbound control, but the side effect got my curiosity.
If the O/P did not know that data was incorrect, then everything would have seen OK?

I also had to remove the invalid (or seems not) =[text1]=combocolumn(1)

 

[moke123]

Reported in other thread that changing to 1 fixed problem.

That's because the bound column is 1 based, unlike the columns which are 0 based.

 

[Gasman]

Yes, I think the o/p got confused between column index and column count.
However the side effect could be missed?

 

[Gasman]

Revisiting this, I also noticed that I brought the zipcitystateid in as first field in the combo, as that was what the o/p was storing in the person record?

It was very late last night when I got to the bottom of it ( or thought I did), but who knows.

 

[Pat Hartman]

1. Combos REQUIRE a unique ID. Otherwise, they will act as this one does and return whatever matching record is convenient.
2. If this is for the US - a zip code may refer to more than 1 city so you can't use a zip code combo to populate the city.

 

[DHookom]

I believe using a column value of 0 returns the index position of the selected value. So, if the first value was selected, it would return 1. Selecting the tenth item in the list would return a value of 10. This is how Access behaved 25 years ago when a developer working for me made the mistake.

 

[Pat Hartman]

This is how Access behaved 25 years ago when a developer working for me made the mistake

are we still talking about a list with a non-unique ID?

 

[DHookom]

This was a comment about how using the bound column to 0 as per the first three sentences in my reply. I hadn’t read this possibility in previous replies.

 

[Gasman]

1. Combos REQUIRE a unique ID. Otherwise, they will act as this one does and return whatever matching record is convenient.
2. If this is for the US - a zip code may refer to more than 1 city so you can't use a zip code combo to populate the city.

AFAICS the value was unique. However o/p was using zipcode as first field in combo, which just happened to be the same as zipcitystateid, which was what they were storing for a person.

 

[theDBguy]

AFAICS the value was unique. However o/p was using zipcode as first field in combo, which just happened to be the same as zipcitystateid, which was what they were storing for a person.

The original file I downloaded earlier didn't appear to have unique values. This becomes more apparent by adding an ORDER BY clause in the Row Source of the combo.

 

[Pat Hartman]

In addition to the non-uniqueness, Zip codes can span MORE THAN ONE CITY so you can never use zip to pick city.

 

[Gasman]

The original file I downloaded earlier didn't appear to have unique values. This becomes more apparent by adding an ORDER BY clause in the Row Source of the combo.

I just looked at the first record and followed the links. So zipcitystate of 1178 was also zipid of 1178.