Row Source of cbo

[Guest]

Background info:
For each building we service, there is a table with supplies available to
that buiding.

I have a form with a text box on the top of it where you enter which
building you will be ordering supplies for.

On this form there is a subform where you enter the products you'd like to
get for that building. On that subform the product Number is a cbo box. I'd
like to set the row source property of that cbo box based on the 5 digit
number entered into the text box at the top of the parent form. That row
source will be the table that corresponds to the 5 digit number.

 

[Douglas J. Steele]

Do yourself a favour. Fix your database design. You shouldn't have one table
for each building, and you definitely should never have data stored in table
names (which is what you've got if the name of the table is the name of the
building).

You should have 3 tables: one with a row for each building, one with a row
for each supply item, and one that resolves the many-to-many relationship
between the other two tables (each building have multiple supply items
associated with each, each supply item can be associated with multiple
buildings). The 3rd table would have, as its primary key, the combination of
the primary key from the building table and the primary key from the
supplies table.

To populate your combo box, create a query that joins the supplies table to
the intersection table, and limit it to those rows that apply to the given
building.

 

[Guest]

To populate your combo box, create a query that joins the supplies table to
the intersection table, and limit it to those rows that apply to the given
building..... HOW?

 

[Douglas J. Steele]

What part don't you know how to do?

To join two tables together in a query, the easiest way is to start the
graphical query builder, select the two tables, ensure that there are lines
joining the related fields (if there aren't, drag them from one table to the
other), then select which fields you want to display.

To limit the rows, put a condition under whichever field represents the
BuildingId. Point to the field on the form that contains the value,
referring to it as Forms!FormName!ControlName (replace FormName and
ControlName with the actual names)

 

[Guest]

Yeah, uh... I'm really sorry to have bothered you with my ignorance. I
thought that's what this site was for. Help when you don't know it all.

 

[Marshall Barton]

WHAT??

Doug gave youa very specific explanation of the cause of
your problem (unnormalized table design). He then explained
how you can create a query that works around your problem
for the time being (it will haunt you further in the
future). Then you say that he was not helpful???

If you were expecting someone to do your work for you or
maybe a tutorial on how to use Access, you were expecting
too much from this communications medium, that's what books
and instructors are for. You might be able to use these
newsgroup more effectively if you reviewed the newsgroup
netiquette FAQ at http://www.mvps.org/access/netiquette.htm

 

[Larry Linson]

Yeah, uh... I'm really sorry to have
bothered you with my ignorance.

Your ignorance about Access is not a bother. Ignorance about using the
newsgroups can be, see the link below for some suggestions, and also see:

I thought that's what this site was for.
Help when you don't know it all.

Nobody's picking on you. Don't get all pouty. We just need to know what it
is that you need help with.

Newsgroup responses aren't suitable for a complete tutorial, and the
volunteers who answer questions here (unpaid, in case you didn't realize)
don't have time to either guess what particular area you need help with, or
try to cover every possible area that might be involved with a non-specific
question.

For good suggestions on effective use of newsgroups, see the FAQ at
http://www.mvps.org/access/netiquette.htm.

Larry Linson
Microsoft Access MVP

 

[Guest]

Which is where I go the following:

Be polite.
a. Always give people the benefit of the doubt.
b. If you can't make heads or tails of the question, a polite
request for clarification will get better results than
belittling the poster.

I didn't mean to ruffle feathers, I simply meant to let him know he didn't
have to be so harsh with the answer.

I am sure that anybody that's worked with Access can understand how
sometimes the simple processes simply escape your mind. It's quicker and
less frustrating to ask them here than to try to rememeber the correct
answer. I've been to classes and I have a few books. I did try there first.

WHAT??

Doug gave youa very specific explanation of the cause of
your problem (unnormalized table design). He then explained
how you can create a query that works around your problem
for the time being (it will haunt you further in the
future). Then you say that he was not helpful???

If you were expecting someone to do your work for you or
maybe a tutorial on how to use Access, you were expecting
too much from this communications medium, that's what books
and instructors are for. You might be able to use these
newsgroup more effectively if you reviewed the newsgroup
netiquette FAQ at http://www.mvps.org/access/netiquette.htm
--
Marsh
MVP [MS Access]

Yeah, uh... I'm really sorry to have bothered you with my ignorance. I
thought that's what this site was for. Help when you don't know it all.

 

[Douglas J. Steele]

Sorry you felt that was derogatory. I was sincerely asking which part you
needed help with, then gave short answers to both parts, assuming that if
you needed longer answers, you'd ask back.

 

[Guest]

I set up tables for each building because I thought one table with repeating
Cost Center Numbers (names) in one column would be a bit confusing. The
tables that I had set up were set up not with actual names but...tbl19399.
The cost center number (name)will never change or be re-used for another
building so I thought it would be okay.

"To limit the rows, put a condition under whichever field represents the
BuildingId. Point to the field on the form that contains the
value,referring to it as Forms!FormName!ControlName (replace FormName and
ControlName with the actual names)"

I called myself doing that and the combo box only lists the first product
for the CC#, not all the products available to that building.

Sorry about the previous confusion and misunderstanding.

J

 

[Douglas J. Steele]

So have you converted to the 3 table approach I recommended, or are you
still using 1 table per building?

If the 3 table approach, what did you call the 3 tables, what fields are in
each, and what's the SQL for the query you have to join the tables (to get
the SQL, open the query, then select SQL View from the View menu. Copy
what's there, and paste it into your reply)

 

[Guest]

Went to the three table approach.
1. tblCCUsage (Fields: CCNum and ProdNum)
2. tblProducts (Fields: ProdNum, ProdDesc, UnitPrice)
3. tblCCNum (Fields: CCNum, PhyAdd, MgmtComp, Contact)

SELECT tblCCUsage.CCNum
FROM tblCCUsage INNER JOIN tblProducts ON
tblCCUsage.ProdNum=tblProducts.ProdNum
WITH OWNERACCESS OPTION;

 

[Douglas J. Steele]

I'm not quite following your setup

Presumably you've got a combo box that's based on tblCCNum. Let's assume
it's called cboCCNum.

To see what products are associated with that Cost Center Number, you need a
query like:

SELECT tblProducts.ProdNum,
tblProducts.ProdDesc,
tblProducts.UnitPrice
FROM tblCCUsage INNER JOIN tblProducts
ON tblCCUsage.ProdNum=tblProducts.ProdNum
WHERE tblCCUsage.CCNum = Forms!NameOfForm!cboCCNum

 

[Guest]

oohhhhhh. now that makes sense...
Thank you for all your help with this.