queries?

[Guest]

I am a newbie to access, but have written in filemaker for years
now...finally deciding to make the change. Nedless to say, its very
different!. I have one major problem to get past.
I have a form with information from a contact table on it. In addition to
this, I wish to have fields that I wish to display information from an office
table....such as office name, addy phone etc.
what is the best way to accomplish this?
I'm guessing queries...and if so, how do I do this?
ty
really a noob

 

[John W. Vinson]

On Thu, 17 May 2007 14:07:01 -0700, truly a noob <truly a

I am a newbie to access, but have written in filemaker for years
now...finally deciding to make the change. Nedless to say, its very
different!. I have one major problem to get past.
I have a form with information from a contact table on it. In addition to
this, I wish to have fields that I wish to display information from an office
table....such as office name, addy phone etc.
what is the best way to accomplish this?
I'm guessing queries...and if so, how do I do this?

Well, you may well need to "unlearn" things as well as to learn new things -
and that can be harder! FMP has its own ways that "things are always done" and
they may just be flat WRONG for Access.

The answer depends on how your tables are structured. If you have an OfficeID
stored in the Contact table, then you can put a Combo Box on the form bound to
the OfficeID field; this Combo can *store* the computer-meaningful but
user-hostile OfficeID, while *displaying* an office name. You can even include
the office address and phone in the Combo's RowSource query and have
additional textboxes on the form with control sources

=comboboxname.Column(n)

where n is the zero based index of the desired field - e.g. if the phone
number is the fourth field in the combo's query, you'ld use (3).

John W. Vinson [MVP]

 

[Guest]

thanks!
i actually got it to work for the most part.
heres the continuing saga...
your thoughts on my table setup is quite accurate....2 tables; contact and
office
heres exactly what i want to do tho
the drop down menu idea is perfect...but i really want the drop down menu to
show the office name , not the primary key...i have no need to show that
number in my database. the additional fields follow along well, no problems
there

is there any way to make this work by locking onto the primary key but the
drop down menu showing the office name instead?

 

[John W. Vinson]

is there any way to make this work by locking onto the primary key but the
drop down menu showing the office name instead?

Certainly. In fact that's exactly how the combo box wizard will set it up if
you use it!

A Combo Box has several relevant interrelated properties:

RowSource Type: this will almost always be "Query/Table", the source of the
data displayed in the combo for selection. In this case it might be a Query
like

SELECT OfficeID, OfficeName FROM Offices ORDER BY OfficeName;

The Control Source is the field into which the selection will be stored, or
which will control which value the combo displays. In this case OfficeID.

The ColumnCount determines how many (up to ten, maybe more, I don't recall)
columns are included in the combo. This would be 2 here.

The Bound Column determines which of the columns will be stored into the
Control Source field: 1, to store the OfficeID.

The ColumnWidths property is a series of numbers controlling the width (in
inches or centimeters depending on your settings) of the text area for each
column. If it's 0, then the column is available to be stored or retrieved for
other purposes, but it *won't* be visible to the user. So a ColumnWidths of

0;0.875

will display only the office name in a 7/8" wide box.

See if this makes the form work as you would like...

John W. Vinson [MVP]

 

[Guest]

tooo sweet....
the column width was the key i was missing...how to deal with having to look
at something that had to be there when I didnt want it!

quite the relearning curve here I see...I would not have figure this one out
on m own...
answers like these arent in my access "Bible" either and perhaps they should
be.
its an art and I appreciate you help in the matter!
thank you very much!

the ex noob...and now neophyte