Help with displaying field B and C, based on field A

[brianlas]

I have created a table which contains info about facilities; sample lines are:
Course ID Golf Course Chapter Facility
1 ABC X3 Salinas
2 XYZ Y4 San Jose
etc.

I have a form with the Golf Course, Chapter and Facility fields. What I want
is for someone to enter the Golf Course name, and based on the name,
automatically display (update) the Chapter and Facility fields on the form
I.e., pull them from a table and update them. Ideally, I would also like to
make the Chapter and Facility fields "Read-only". Please forgive me if this
is a stupid question, but I've been racking my brain over this for a bit too
long. Thanks for anyone's help!

 

[Wayne Morgan]

Try making the control for the Golf Course field a combo box. This will let
the user type the name or select it from the drop down list. Add the other
columns to the combo box. Place 2 textboxes on the form and set their
control source to the appropriate column in the combo box. They will then
display the Chapter and Facility. Since they will be calculated controls,
they will be read only.

Setup:
Combo Box
Row Source: SELECT [Course ID], [Golf Course], Chapter, Facility FROM
tblTableName ORDER BY [Golf Course];
Bound Column: 1
Column Count: 4
Column Widths: 0";0.5";0";0" (adjust the 2nd one as needed to display the
full names)
LimitToList: Yes
Control Source: The field in the form's Record Source that you want the
Bound Column
(the ID column) value stored in when you make a
selection.

First Textbox
Row Source: =cboComboBox.Column(2)

Second Textbox
Row Source: =cboComboBox.Column(3)

The Column property is zero based, so 2 is the 3rd column and 3 is the 4th
column.

 

[brianlas]

Wayne,

Thanks so much!!! Your excellent description enabled me to get it right on the
first try!! I can't thank you enough for this! Do you have any
recommmendations on how I can become "more like" an Access MVP -- courses,
books, etc.? I have Que/Jennings "Using Microsoft Office Access 2003". Thanks
again! You made my day!

Brian

Try making the control for the Golf Course field a combo box. This will let
the user type the name or select it from the drop down list. Add the other
columns to the combo box. Place 2 textboxes on the form and set their
control source to the appropriate column in the combo box. They will then
display the Chapter and Facility. Since they will be calculated controls,
they will be read only.

Setup:
Combo Box
Row Source: SELECT [Course ID], [Golf Course], Chapter, Facility FROM
tblTableName ORDER BY [Golf Course];
Bound Column: 1
Column Count: 4
Column Widths: 0";0.5";0";0" (adjust the 2nd one as needed to display the
full names)
LimitToList: Yes
Control Source: The field in the form's Record Source that you want the
Bound Column
(the ID column) value stored in when you make a
selection.

First Textbox
Row Source: =cboComboBox.Column(2)

Second Textbox
Row Source: =cboComboBox.Column(3)

The Column property is zero based, so 2 is the 3rd column and 3 is the 4th
column.

--
Wayne Morgan
MS Access MVP

I have created a table which contains info about facilities; sample lines
are:
Course ID Golf Course Chapter Facility
1 ABC X3 Salinas
2 XYZ Y4 San Jose
etc.

I have a form with the Golf Course, Chapter and Facility fields. What I
want
is for someone to enter the Golf Course name, and based on the name,
automatically display (update) the Chapter and Facility fields on the form
I.e., pull them from a table and update them. Ideally, I would also like
to
make the Chapter and Facility fields "Read-only". Please forgive me if
this
is a stupid question, but I've been racking my brain over this for a bit
too
long. Thanks for anyone's help!

 

[Wayne Morgan]

You're welcome.

Some other good books you may want to look at:

Any version of "Access (year) Developer's Handbook", I believe 2002 is the
newest one. They may also be listed as "... Desktop Developer's Handbook"
and "... Enterprise Developer's Handbook".

"Microsoft Office Access 2003 Inside/Out"

You will find information on these books and others at this site,
http://www.viescas.com/Info/books.htm.

 

[Sandy]

Hi Wayne,

I'm having the same problem that this guy was having. You did a really good
job of explaining what to do but I'm still having trouble with one part of
it. To keep it simple, I'm going to use the same Field Names used in the
previous dialogue. I was able to create the ComboBox for Golf Course
successfully. However, I cannot get the other fields to display. I typed:
=cboComboBox.Column(2), =cboComboBox.Column(3), etc..., respectively, into
the Control Source (they didn't have a Row Source) of the other text boxes.
When I test it though I get a message that =cboComboBox.Column(2) is not
found. What am I doing wrong ?

Thanks !
Sandy

Try making the control for the Golf Course field a combo box. This will let
the user type the name or select it from the drop down list. Add the other
columns to the combo box. Place 2 textboxes on the form and set their
control source to the appropriate column in the combo box. They will then
display the Chapter and Facility. Since they will be calculated controls,
they will be read only.

Setup:
Combo Box
Row Source: SELECT [Course ID], [Golf Course], Chapter, Facility FROM
tblTableName ORDER BY [Golf Course];
Bound Column: 1
Column Count: 4
Column Widths: 0";0.5";0";0" (adjust the 2nd one as needed to display the
full names)
LimitToList: Yes
Control Source: The field in the form's Record Source that you want the
Bound Column
(the ID column) value stored in when you make a
selection.

First Textbox
Row Source: =cboComboBox.Column(2)

Second Textbox
Row Source: =cboComboBox.Column(3)

The Column property is zero based, so 2 is the 3rd column and 3 is the 4th
column.

--
Wayne Morgan
MS Access MVP

I have created a table which contains info about facilities; sample lines
are:
Course ID Golf Course Chapter Facility
1 ABC X3 Salinas
2 XYZ Y4 San Jose
etc.

I have a form with the Golf Course, Chapter and Facility fields. What I
want
is for someone to enter the Golf Course name, and based on the name,
automatically display (update) the Chapter and Facility fields on the form
I.e., pull them from a table and update them. Ideally, I would also like
to
make the Chapter and Facility fields "Read-only". Please forgive me if
this
is a stupid question, but I've been racking my brain over this for a bit
too
long. Thanks for anyone's help!

 

[Brian Bastl]

Hi Sandy,
I have absolutely no idea which problem THIS OTHER GUY was having. What is
the rowsource for your combobox? The error message you are receiving would
suggest that rowsource is only pulling 1 or 2 values, not 3 or more.
Brian

Hi Wayne,

I'm having the same problem that this guy was having. You did a really good
job of explaining what to do but I'm still having trouble with one part of
it. To keep it simple, I'm going to use the same Field Names used in the
previous dialogue. I was able to create the ComboBox for Golf Course
successfully. However, I cannot get the other fields to display. I typed:
=cboComboBox.Column(2), =cboComboBox.Column(3), etc..., respectively, into
the Control Source (they didn't have a Row Source) of the other text boxes.
When I test it though I get a message that =cboComboBox.Column(2) is not
found. What am I doing wrong ?

Thanks !
Sandy

Try making the control for the Golf Course field a combo box. This will let
the user type the name or select it from the drop down list. Add the other
columns to the combo box. Place 2 textboxes on the form and set their
control source to the appropriate column in the combo box. They will then
display the Chapter and Facility. Since they will be calculated controls,
they will be read only.

Setup:
Combo Box
Row Source: SELECT [Course ID], [Golf Course], Chapter, Facility FROM
tblTableName ORDER BY [Golf Course];
Bound Column: 1
Column Count: 4
Column Widths: 0";0.5";0";0" (adjust the 2nd one as needed to display the
full names)
LimitToList: Yes
Control Source: The field in the form's Record Source that you want the
Bound Column
(the ID column) value stored in when you make a
selection.

First Textbox
Row Source: =cboComboBox.Column(2)

Second Textbox
Row Source: =cboComboBox.Column(3)

The Column property is zero based, so 2 is the 3rd column and 3 is the 4th
column.

--
Wayne Morgan
MS Access MVP

I have created a table which contains info about facilities; sample lines
are:
Course ID Golf Course Chapter Facility
1 ABC X3 Salinas
2 XYZ Y4 San Jose
etc.

I have a form with the Golf Course, Chapter and Facility fields. What I
want
is for someone to enter the Golf Course name, and based on the name,
automatically display (update) the Chapter and Facility fields on the form
I.e., pull them from a table and update them. Ideally, I would also like
to
make the Chapter and Facility fields "Read-only". Please forgive me if
this
is a stupid question, but I've been racking my brain over this for a bit
too
long. Thanks for anyone's help!

 

[Sandy]

Hi Brian,

Thanks for your response. I apologize for the lack of information. I
thought that I had replied to an existing string of emails. The Row Source
for my Combo Box is SELECT Realtors.RealtorName, Realtors.RAgency,
Realtors.RPhone, Realtors.RExt, Realtors.RMobile, Realtors.REmail,
Realtors.RAddress, Realtors.RCity, Realtors.RState, Realtors.RZip FROM
Realtors; I'm a Loan Officer and I'm just starting to use Access for my
Contact Management. I'm trying to make it to where when I type my borrower's
realtor into the form, all of the other information on that realtor will
automatically populate. But I can't make it work. The Control Source for
the 2nd field in my form (Real Estate Agency, ie RAgency) is
=RealtorName.Column(2) I've also put this into the other fields' Control
Source (except with a difference Column number). But in form view when I
select a Realtor from the drop down box, nothing will populate in the other
fields.

I hope this makes sense,
Thanks again for your help !
Sandy

Hi Sandy,
I have absolutely no idea which problem THIS OTHER GUY was having. What is
the rowsource for your combobox? The error message you are receiving would
suggest that rowsource is only pulling 1 or 2 values, not 3 or more.
Brian

Hi Wayne,

I'm having the same problem that this guy was having. You did a really good
job of explaining what to do but I'm still having trouble with one part of
it. To keep it simple, I'm going to use the same Field Names used in the
previous dialogue. I was able to create the ComboBox for Golf Course
successfully. However, I cannot get the other fields to display. I typed:
=cboComboBox.Column(2), =cboComboBox.Column(3), etc..., respectively, into
the Control Source (they didn't have a Row Source) of the other text boxes.
When I test it though I get a message that =cboComboBox.Column(2) is not
found. What am I doing wrong ?

Thanks !
Sandy

Try making the control for the Golf Course field a combo box. This will let
the user type the name or select it from the drop down list. Add the other
columns to the combo box. Place 2 textboxes on the form and set their
control source to the appropriate column in the combo box. They will then
display the Chapter and Facility. Since they will be calculated controls,
they will be read only.

Setup:
Combo Box
Row Source: SELECT [Course ID], [Golf Course], Chapter, Facility FROM
tblTableName ORDER BY [Golf Course];
Bound Column: 1
Column Count: 4
Column Widths: 0";0.5";0";0" (adjust the 2nd one as needed to display the
full names)
LimitToList: Yes
Control Source: The field in the form's Record Source that you want the
Bound Column
(the ID column) value stored in when you make a
selection.

First Textbox
Row Source: =cboComboBox.Column(2)

Second Textbox
Row Source: =cboComboBox.Column(3)

The Column property is zero based, so 2 is the 3rd column and 3 is the 4th
column.

--
Wayne Morgan
MS Access MVP


I have created a table which contains info about facilities; sample lines
are:
Course ID Golf Course Chapter Facility
1 ABC X3 Salinas
2 XYZ Y4 San Jose
etc.

I have a form with the Golf Course, Chapter and Facility fields. What I
want
is for someone to enter the Golf Course name, and based on the name,
automatically display (update) the Chapter and Facility fields on the form
I.e., pull them from a table and update them. Ideally, I would also like
to
make the Chapter and Facility fields "Read-only". Please forgive me if
this
is a stupid question, but I've been racking my brain over this for a bit
too
long. Thanks for anyone's help!

 

[Brian Bastl]

Hi Sandy,

first off, columns are zero-based, meaning that the first column would be
referenced as Column(0) and the second column as Column(1), and so on.

Secondly, do you have primary key defined in your Realtors table...
something like RealtorID? I'd bring that into the rowsource and set that as
your bound column.

Combo Row Source =
SELECT Realtors.RealtorID, Realtors.RealtorName, Realtors.RAgency,
Realtors.RPhone, Realtors.RExt, Realtors.RMobile, Realtors.REmail,
Realtors.RAddress, Realtors.RCity, Realtors.RState, Realtors.RZip FROM
Realtors ORDER BY Realtors.RealtorName;

Bound Column = 1 'RealtorID
Column Count = 11
Column Widths = 0";2";2";0";0";0";0";0";0";0";0"; 'the combo will only
display the realtor's name and Agency.

And lastly, you don't need to have anything for the control source of the
text boxes. I'd "push" the values into the corresponding text boxes in the
AfterUpdate event of your combobox.

Example:
Private Sub YourCombo_AfterUpdate()
Me.txtRealtorName = Me.YourCombo.Column(1)
Me.txtRealtorAgency = Me.YourCombo.Column(2)
Me.txtRealtorPhone = Me.YourCombo.Column(3)
etc.
End Sub

HTH,
Brian

 

[Sandy]

Hi Brian,

Thanks so much ! From copying and pasting the data that you told me to use,
I was able to get it to work. I have to admit though that I don't have a
clue what all of it means. What do all the numbers in Column Width mean,
i.e, 0";2";2"'0"; etc... ? Also, I'm now trying to do the exact same
thing with a table I've created for Medical Schools. Many of my borrowers
are medical residents & fellows so I have a field in my form to list their
medical school as well as that medical school's city. I would like to be
able to input the medical school from a drop-down and the city will
autopopulate. Well, I've input everything exactly like I did the Realtor
Combo Box (replacing table/field names, of course) but it won't work. Here's
what I'm doing. In the Medical School Combo Box I have as Row Source SELECT
MedicalSchools.MedicalSchoolID, MedicalSchools.MedicalSchool,
MedicalSchools.MedSchoolCity FROM MedicalSchools ORDER BY
MedicalSchools.MedicalSchool; I have MedicalSchool ID listed as the
Control Source, 0";2";0" in the Column Widths, 1 in the Bound Column, and 3
in the Column Count. When I go to form view and click on Medical School
field, I get the message "The record source
'~sq_cBorrowers~sq_cMedicalSchool' specified on this form or report does not
exist." Can you tell me from this information what I'm doing wrong ? I'm
so sorry that after you walked me through step-by-step how to do this, I'm
still having trouble.

Thanks once again!
Sandy

 

[Brian Bastl]

Hi Sandy,

Did you copy and paste the rowsource sql from the query designer into your
message? If not then open the form in Design view, select your MedSchool
combo, and bring up its property sheet. Click the (...) for the rowsource to
bring up the query designer. Then select SQL view. Copy and paste that into
your reply, so that I can see what your query is.

Is MedSchool_Combobox on the same form where you have your
Realtor_Combobox?

Also, in your Medical Schools table, do you have a field which relates to
the borrowers table? Something along the lines of:

table name: MedicalSchool
Fields:
MedicalSchoolID 'primary key
MedicalSchool
MedSchoolCity
BorrowerID foreign key linking to Borrower's table

Let me know.
Brian

 

[Sandy]

Hi Brian,

Here's what the SQL View says:

ELECT MedicalSchools.MedicalSchoolID AS Expr1, MedicalSchools.MedicalSchool
AS Expr2, MedicalSchools.MedSchoolCity AS Expr3
FROM MedicalSchools
ORDER BY MedicalSchools.MedicalSchool;

Also, yes, the MedicalSchool Combo Box is on the same form as the Realtor
ComboBox.

No, I do not have a field in my Medical Schools table that links it to the
Borrowers table.

Thanks again for your help, Brian !
Sandy

 

[Brian Bastl]

Hi Sandy,

are you certain you have a table named "MedicalSchools"? Perhaps you should
check the spelling, because your SQL says that it doesn't recognize that
particular table name. In the query grid for your combobox, delete anything
in the grid, and then go to View|Show Table... and select the MedicalSchools
table from the list. Then drag and drop the relevant fields into the grid.
Close the grid and save the changes. See if this will do it for you. If not,
then go back into the SQL view of the query, and post it so I know what
you're dealing with.

Brian

 

[Sandy]

Hi Brian,

Well, I'm totally embarrassed to tell you this but I had a space between
Medical School in the table name. As soon as I renamed it, the Combo Box
worked ! You are so smart ! I feel very fortunate that you're the one who
replied to my post.

Thanks again for all of your help and I'm really sorry for the dumb
mistake....
Sandy

P.S. Could you tell me what the numbers in Column Widths mean, i.e.,
0";2";2";0"; etc.... ?

 

[Brian Bastl]

Hi Sandy,

certainly no need to feel embarassed. I certainly make my fair share!

As to your question about the column widths: on the properties page for a
combo or listbox, it lets you specify how you'd like your query results to
appear. The column widths are specified in inches (if you are in the USA,
and delimited by a semicolon. Using your query, the results would output as:

{MedicalSchoolID, MedicalSchool, MedSchoolCity}
==> 1 | Stanford | Palo Alto

Normally, you'd only want to show the user the fields which make sense to
them. Indexed columns generally have no meaning to the user, but the index
itself is MOST important to your database. So, you'd hide the index (first
column in this case) with its width set = to 0"; second column to something
greater than 0, etc.

With the following settings:
Column Widths = 0"; 2"; 2";
Bound Column = 1
Column Count = 3
your user would only see Column 2 and Column 3 ==> Stanford | Palo Alto.

HTH,
Brian

 

[Brian Bastl]

I should add that although the column count = 3 on the properties page, in
the VB editor the particular columns would be reference as Column(0) for the
first column, Column(1) for the second, and so on...

Brian

 

[Sandy]

Hi again,

Thanks for the clarification on Column Widths. I just have a question. In
my Realtor Combo Box, the last 8 Column Widths are all set to 0". Only
Columns 1 and 2 have a value greater than 0 (2"). However, these 8 Columns
that are set to 0" are displayed on my form. I thought that 0" meant that it
wouldn't be displayed.

Thanks again !!
Sandy

 

[Brian Bastl]

Hi Sandy,

You needed all of those columns in your rowsource to populate all of your
text boxes , which I'd assume you mean when you say that they're displayed,
but you didn't need to see them in the combobox itself. You just needed to
see the Realtors name and company, right? Therefore, I set all column widths
(other than name and company) to 0". If you wanted to see them all in the
combobox, you could set their widths to greater than 0, but still only a few
would show at one time. to see the rest, you'd have to scroll horizontally.
That's the reason for the text boxes: to present the data in a coherent
format.

One more thing:
I tried just using Column Widths = 0";2";2" (deleted all the rest of the
0"s), and it still worked, so I just learned something new.

Hope that makes sense,
Brian

 

[Sandy]

Hi Brian,

I totally understand now. Thanks for the clarification and also for all of
your great help on ComboBoxes !!

Sandy

 

[Brian Bastl]

Glad to help.

Brian

 

[Claire Rohan]

Hi Brain,
You seem to know lots about this so Im hoping you might be able to help me
too - I have created a combox in my main Trade Details form which is used to
get values for a number of text boxes ie the combo box for Client also
selects the commission rate and interest rate for that client from the Client
table.
I want to be able to fill out the rest of the details in the Trade Details
form and then when the form is completed to lock all the fields including the
values of the commission rate and interest rate. It will be the case that at
a later stage I will change the rates in the Client table but I want the
rates in the Trade Details form to stay as they were when I completed the
form. Is this possible?