Doing my head in

[Clair]

Hi,

Am self-taught through tutorials about Microsoft Access. Just today got my
head about multi-table relationships and subforms.

In an example on the tutorial, in a Form, you could select a value from a
drop down box and the other boxes would automatically fill with the details
related to that value.

ie: Select: Chicken from drop down box and $2.00 appears in the 'Price' field.

I can see where the information comes from and even experimented with
building the whole "SELECT etc" expression and LookUps in the source box but
it doesn't do it for me. I basically copied all the elements exactly from
the example database. My relationships are correct as far as I can tell.

Anyone know how to do what I'm trying to do or at the very least, the term
for it so I can search on the internet? Would love to understand more about
how queries/forms/subforms/lookups all work together to make forms really
informative and automatic.

Thanks in advance.

 

[BruceM]

There are many tutorials, so without knowing to which one you are referring
it is not possible to answer directly. If you are trying to select Chicken
from a combo box (cboProduct) and have $2.00 appear in a text box
(txtPrice), one way to do that is to have the cboProduct row source include
the price. If there are two items, Product and Price, and you only want to
display the product, set the width of the second column to 0 (make sure the
combo box Column Count is 2). You can still reference the hidden column.
For instance, the Control Source of txtPrice could be:
=[cboProduct].Column(1)
Note that the columns are numbered starting from 0, so 1 is actually the
second column.
You could use the cboProduct After Update event to accomplish the same thing
(although the syntax is a bit different).
The meaning of "I...experimented with building the whole "SELECT etc"
expression and LookUps in the source box" is unclear. What is the "source
box"? How are you building the statement? In what way does it not work?
What is the SELECT statement that you tried to use?

The question about how queries etc. work together is too general to answer
effectively in this forum. The following links contain information that may
be helpful in understanding more about this subject. In the second link, go
to the Design heading under Tips for Casual Users.

http://allenbrowne.com/casu-01.html
http://allenbrowne.com/tips.html

Allen has assembled a good collection of links (on the right side of the web
pages). One of them is to the MVP site, which is well worth checking out:
http://www.mvps.org/access/