Combo Wont fill in correct Data ???

[Dave Elliott]

SELECT DISTINCTROW Customers.CustID, Customers.Customers,
Customers.[Customer ID], Customers.Address, Customers.City,
Customers.Region, Customers.Phone, Customers.[Default Markup %],
Customers.[Postal Code], Customers.Mailed, Customers.[Customer Rate],
Customers.PO, Customers.SeniorDisc, Customers.[Pay Status] FROM Customers
ORDER BY Customers.Customers;
=NameB.Column(4) & ", " & NameB.Column(5) & " " & NameB.Column(8)

Table customer info comes from is Customers table
data displays correctly in customers form and via the customers table
itself, but not from my form TimeCards where I
do a lookup from the combo. The columns are in order and from the sql
statement it runs and shows the correct city
but not via the form TimeCards after the lookup ??? Every other column
displays correctly!

I have the right column as well

 

[John Vinson]

SELECT DISTINCTROW Customers.CustID, Customers.Customers,
Customers.[Customer ID], Customers.Address, Customers.City,
Customers.Region, Customers.Phone, Customers.[Default Markup %],
Customers.[Postal Code], Customers.Mailed, Customers.[Customer Rate],
Customers.PO, Customers.SeniorDisc, Customers.[Pay Status] FROM Customers
ORDER BY Customers.Customers;
=NameB.Column(4) & ", " & NameB.Column(5) & " " & NameB.Column(8)

Table customer info comes from is Customers table
data displays correctly in customers form and via the customers table
itself, but not from my form TimeCards where I
do a lookup from the combo. The columns are in order and from the sql
statement it runs and shows the correct city
but not via the form TimeCards after the lookup ??? Every other column
displays correctly!

I have the right column as well

The Column property is zero based, so the expression you cite would
concatenate the City, Region, and Postal Code. Is that what you want?
What are you in fact getting?

John W. Vinson[MVP]

 

[John Vinson]

Yes this is what i want.
What I am getting is the ZipID instead.
my table customers has city,region,postal code in it.
city is a lookup via the table tblzipcode with the zipid,city,state,zip
my customers form has the same, city, region, postal code with city as a
combo box looking up via
the tblzipcode.

So you're yet another victim of Microsoft's misdesigned, misleading,
and infuriating Lookup Wizard!

Your table DOES NOT CONTAIN the City name. It contains the ZipID. That
simple fact is concealed from your view by the Lookup misfeature!

Base your combo on a Query including tblzipcode.

You're surely not storing city and region in your Customers table?
That would be unnecessary, unwise, and redundant; just store the
postal code in the Customer table, and use a Query joining the
Customers table to tblzipcode to find the city and region names.

John W. Vinson[MVP]

 

[Dave Elliott]

Yes, both tables contain the name city
postal code and zip do not match
state and region do not match
Should i use the lookup for both the customers table for the field city
using the table tblzipcode
and also a lookup on my customers form to do the same?

 

[John Vinson]

Yes, both tables contain the name city
postal code and zip do not match
state and region do not match
Should i use the lookup for both the customers table for the field city
using the table tblzipcode
and also a lookup on my customers form to do the same?

I would N E V E R use a Lookup Field in *any* table.

I would frequently use Combo Boxes on forms, to display one value
while storing another.

Some of my collegues aren't quite so vehement, and a few even use
table lookup fields occasionally.

See http://www.mvps.org/access/lookupfields.htm for a critique.

You're using a relational database. Use it relationally! For a Report
use a Query *JOINING* your customers table to tblZipcode. Display the
city from tblZipcode, display the customer information from Customers.
It is not necessary nor is it beneficial to have a (redundant) City
field in the Customers table (unless you have cases where one zipcode
covers multiple cities, and even there the post office has a
"preferred city").

If you just want to display other fields from the combo box on the
form, set the Control Source property of textboxes to

=cboZipcode.Column(n)

where cboZipcode is the name of the combo box, and (n) is the zero
based index of the desired field in the combo's rowsource query - i.e.
(2) will be the *third* field in that query. This will display the
actual field in that query; if the table contains a lookup field,
you'll see the ID that is actually stored in the table, not the
artificial "lookup".


John W. Vinson[MVP]