Equivalent Code for Txtbx

[Guest]

I am trying to link the city name to a form and I am getting the CityID # now
from the table... I know on a cbobox the following works

SELECT DISTINCTROW tblCity.* FROM tblCity ORDER BY tblCity.City;

But I don't know how to do the same thing in a text box... and does the code
go in the Control source?

Sorry, I don't know how to use the expression builder... (yet!) and I can't
find an example to follow.

Thanks!

 

[Douglas J. Steele]

Join the table that contains the city name to the rest of your query, so
that city name is available in the form's recordsource.

 

[Guest]

Im sorry I don't understand your answer.
I have a supplier tbl originally City was part of it. I am just learning and
I tried to follow normalization... because I have 230 records and some city
names are repeated. Also, hours of opperation. So I tried to do what I read
about in articles on normalization and made a tbl for City and one for Hours.

I made a City tbl it has a relationship CityID to the Suppliers Table CityID
which is a # it is lookup on supplier Tbl

SELECT DISTINCTROW tblCity.* FROM tblCity;

I want to see words not numbers ... and I do in my table and in my combo box
on my forms which are based on the supplier table and a qry based on supplier
table
all have CityID on them.

row source on cbobox on my supplier form is
SELECT DISTINCTROW tblCity.* FROM tblCity ORDER BY tblCity.City;

but anywhere it is a text box to cityID it now shows #s...

I hope that clarify my problem... ??
Thanks!
lmv

 

[Douglas J. Steele]

If I understand correctly, your Supplier table has CityID in it, but you
want to know what City Name is associated with each supplier, not simply the
number.

Create a query that joins your Supplier table to your City table, and use
that query as the recordsource for your form, not whatever you're currently
using (presumably either the Supplier table, or a query based only on it).
Your query would be something like:

SELECT tblSupplier.*, tblCity.CityName
FROM tblSuppler INNER JOIN tblCity
ON tblSupplier.CityID = tblCity.CityID

Now, you have the name of the city available to you on your form.

 

[Guest]

Thanks! That worked!
I did

SELECT Supplier.*, tblCity.City
FROM tblCity INNER JOIN Supplier ON tblCity.CityID = Supplier.CityID;

Now... is there a way to add the Hours tbl info to this also so the hours
still show up? I tried but then it only gave me 29 records that HAD hours
(there are about 300 without hour info also) because I didn't know what to
put here is what I tried...

SELECT Supplier.*, tblCity.City, tblHours.Hours
FROM tblHours INNER JOIN (tblCity INNER JOIN Supplier ON tblCity.CityID =
Supplier.CityID) ON tblHours.HoursID = Supplier.HoursID;

Thanks... (by the way thanks for the "rapid response"!!)

lmv

 

[Douglas J. Steele]

When you're joining tables on a particular field, and one of the tables
doesn't have values for that field on all the rows, you need to do a Left
Join (or a Right Join: it depends on which order you list the two tables,
and which one doesn't have values in all cases)

There is a slight problem, though. A Left Join (or Right Join) may be nested
inside an Inner Join, but an Inner Join may not be nested inside a Left Join
(nor Right Join)

That means I think you need the following:

SELECT Supplier.*, tblCity.City, tblHours.Hours
FROM Supplier INNER JOIN (tblCity LEFT JOIN tblHours ON
tblHours.HoursID = Supplier.HoursID) ON tblCity.CityID = Supplier.CityID

 

[Guest]

I tried that but I get an error on JOIN and it highlights the word supplier
after SELECT...?
I tried changing LEFT to RIGHT that didn't work either...

Where can I read about the process I am doing here, with the left/right join
info?
Both City and Hours tables only have ID and INFO no other fields.
If this is based on "where" fields are in my table (??) the HOURS
information is to the right of the CITY field.

Also, Does it matter in RELATIONSHIPS where the TABLE "box" is next to the
TABLE "box" it is attached to? (sorry I don't know the correct terms)

I am trying to learn WHY I am doing what I am doing as well as just having
the answer.

Thanks!

 

[Douglas J. Steele]

The physical appearance of the tables and relationships don't really matter.
What's important is that you've defined the relationships properly. In the
Query Builder, highlight the line for the relationship, and right-click.
Select Join Properties from the context menu that appears. The default
should be the top choice: "Only include rows where the joined fields from
both tables are equal". You need to choose the appropriate one from the
other two choices (You're looking for "Include ALL records from 'Supplier'
and only those records from 'tblHours' where the joined fields are equal")

 

[Guest]

Thank You!! Now I will try to learn WHY I did what you had me do... whew!
this is complicated!
lmv

 

[sawitskij]

I am trying to link the city name to a form and I am getting the CityID # now
from the table... I know on a cbobox the following works

SELECT DISTINCTROW tblCity.* FROM tblCity ORDER BY tblCity.City;

But I don't know how to do the same thing in a text box... and does the code
go in the Control source?

Sorry, I don't know how to use the expression builder... (yet!) and I can't
find an example to follow.

Thanks!

 

[BruceM]

I'll jump in here since nobody else has to say that a text box does not have
a row source, so there is really no place for the equivalent code. List
boxes and combo boxes are for making selections. Text boxes are intended
for a somewhat different use. By the way, what do you mean "I am trying to
link the city name to a form"? Does tblCity have two fields, one for the
city name and the other for CityID? Do you intend to store the CityID? If
so, is there some reason why you are doing so rather than just storing the
city name? In either case you are storing a single field. In what table
are you storing this information?