By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

How to insert a value from table 1 to table 2 based on an ID field?

P: n/a
Hello Gurus,

this probably is real simple for you but for me it's a bummer.

I have one table in which I have basic data collected of schools. I
have another data that is detailed info on the stuff they teach in the
school. I have two forms for entering the data. The first, basic info
table is already filled in.

I would like my second entry form to show the name of the school as
soon as the School ID field is filled in. This would make for fewer
mistakes in the entry phase. I don't need to enter the name of the
school into the table, just to show it.

The questions:

1) What kind of control do I need for this?
2) What kind of statement (if any) do I need to write into the
properties of the control?

Any info greatly appreciated.

Thanks!

Grumman

Sep 14 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
gr********@hotmail.com wrote:
Hello Gurus,

this probably is real simple for you but for me it's a bummer.

I have one table in which I have basic data collected of schools. I
have another data that is detailed info on the stuff they teach in the
school. I have two forms for entering the data. The first, basic info
table is already filled in.

I would like my second entry form to show the name of the school as
soon as the School ID field is filled in. This would make for fewer
mistakes in the entry phase. I don't need to enter the name of the
school into the table, just to show it.

The questions:

1) What kind of control do I need for this?
2) What kind of statement (if any) do I need to write into the
properties of the control?

Any info greatly appreciated.
Unless you need to see both the standard default behaviour of a ComboBox would
store the ID and display the name. If you want to see both just add a TextBox
with a ControlSource of...

=ComboBoxName.Column(n)

....where n is the column number in the ComboBox Rowsource that you want to
display (start at zero).

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Sep 14 '06 #2

P: n/a

Rick Brandt kirjoitti:
Unless you need to see both the standard default behaviour of a ComboBox would
store the ID and display the name. If you want to see both just add a TextBox
with a ControlSource of...
=ComboBoxName.Column(n)
...where n is the column number in the ComboBox Rowsource that you want to
display (start at zero).
Okay, Rick, this took me further already. Now I have a combobox which
displays the list of schools. However, I would wish to have a
non-editable display of the school name, just as a safeguard against
people entering the wrong number for the school. Is this doable with a
text box?

Sorry for forcing you to spell things out a l-e-t-t-e-r at a time, but
I am a bit slow with Access... Thanks!

Sep 14 '06 #3

P: n/a
<gr********@hotmail.comwrote in message
news:11**********************@d34g2000cwd.googlegr oups.com...
>
Rick Brandt kirjoitti:
>Unless you need to see both the standard default behaviour of a ComboBox
would
store the ID and display the name. If you want to see both just add a
TextBox
with a ControlSource of...
=ComboBoxName.Column(n)
...where n is the column number in the ComboBox Rowsource that you want to
display (start at zero).

Okay, Rick, this took me further already. Now I have a combobox which
displays the list of schools. However, I would wish to have a
non-editable display of the school name, just as a safeguard against
people entering the wrong number for the school. Is this doable with a
text box?

Sorry for forcing you to spell things out a l-e-t-t-e-r at a time, but
I am a bit slow with Access... Thanks!
My previous response explains how to do that. Your ComboBox should have two
columns, one for the ID (the bound column) and one for the name. You can either
display both columns in the drop-list or just one. If you display both the
first one not hidden is what you will see when the list is not dropped. Do you
want to see the ID then or the Name?

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Sep 14 '06 #4

P: n/a

Rick Brandt kirjoitti:
My previous response explains how to do that. Your ComboBox should have two
columns, one for the ID (the bound column) and one for the name. You can either
display both columns in the drop-list or just one. If you display both the
first one not hidden is what you will see when the list is not dropped. Do you
want to see the ID then or the Name?
I would love to have a label that would show just the name of the
school, whose ID the person just entered in the School ID box. I am
just not that familiar with the moving parts of Access and that's why
it's taking me so long to grasp the idea.

I toyed with a system where I would have a hidden combo box, and then a
text label would read the row whose ID is School ID from the combo and
display the text of that row. Then again, I also have dreams of world
domination.

SO, maybe you can try one more time to make me understand?

Thanks already!

Sep 14 '06 #5

P: n/a
<gr********@hotmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
>
Rick Brandt kirjoitti:
>My previous response explains how to do that. Your ComboBox should have two
columns, one for the ID (the bound column) and one for the name. You can
either
display both columns in the drop-list or just one. If you display both the
first one not hidden is what you will see when the list is not dropped. Do
you
want to see the ID then or the Name?

I would love to have a label that would show just the name of the
school, whose ID the person just entered in the School ID box. I am
just not that familiar with the moving parts of Access and that's why
it's taking me so long to grasp the idea.

I toyed with a system where I would have a hidden combo box, and then a
text label would read the row whose ID is School ID from the combo and
display the text of that row. Then again, I also have dreams of world
domination.

SO, maybe you can try one more time to make me understand?
Okay, from the top :-)

If you only need to store the ID while displaying only the name then you can set
up your ComboBox so that it has two columns. The first column has a width of
zero and contains the ID. The second column has a non-zero width so that is
what the user sees both in the drop down list and after they make a selection.
If you use the wizard to add a ComboBox this would actually be the default
configuration. For this your ComboBox would need the following properties...

ColumnCount = 2
Bound Column = 1
ColumnWidth 0.0";1" (second is any non-zero value)
RowSource (as select statement or query that returns both ID and Name)
If you want to see both the ID and the name on the form then you can make both
columns visible in the ComboBox by giving them both a non-zero width property.
If you do that you will see both values in the drop down list, but only the
left-most column will show in the box after you make a selection. If you want
to also show the second column's value you can place a TextBox on the form right
next to the ComboBox and give it a ControlSource expression of...

=ComboBoxName.Column(1)

Column numbers start withy zero so (1) is actually the second column.

Since the TextBox is bound to an expression it will not be editable (no need to
use a label). It will automatically "track" whatever changes you make to the ID
though.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Sep 14 '06 #6

P: n/a
How about if you create a form that has the school name, ID, address maybe
(just a few text boxes at the top). Then lock everybox on the form so they
cannot be edited.
Then create a subform (use the wizard) on that form to be your input form
for the course and methods data. That leaves your school info clearly
visible but untouchable while it links cleanly (stores the ID field) to your
second table and no one has to type or mistype any ids. You would have to
have your data in the first table in some logical order so people could
scroll through it easily.
<gr********@hotmail.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
Hello Gurus,

this probably is real simple for you but for me it's a bummer.

I have one table in which I have basic data collected of schools. I
have another data that is detailed info on the stuff they teach in the
school. I have two forms for entering the data. The first, basic info
table is already filled in.

I would like my second entry form to show the name of the school as
soon as the School ID field is filled in. This would make for fewer
mistakes in the entry phase. I don't need to enter the name of the
school into the table, just to show it.

The questions:

1) What kind of control do I need for this?
2) What kind of statement (if any) do I need to write into the
properties of the control?

Any info greatly appreciated.

Thanks!

Grumman

Sep 15 '06 #7

P: n/a
Thank you one and all for these important hints! I an always reminded
of how lucky we are these days when experts are willing to put in their
effort to help the newbies on the Web.

Best regards,

-G

Sep 16 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.