accession number question

[Outbacker]

how do i enter an accession number into another table and have it pull up all
the info related to that accession number (such as a persons first and last
name, address, various forms etc. from that accession log) populate into
other tables and forms. I'd like for the info to populate a form
automatically. I'd also like the query to pull up all files that have that
accession number and when I click a form for the info to auto populate the
form. thanks in advance.

 

[Arvin Meyer MVP]

Hopefully you are aware that all you need to populate another form with is
the Primary Key (I assume that's what accession number refers to) the other
textboxes can be viewed with unbound data, since you only need to store it
once. Assuming that is what you want to do, add a combo box listing the Key,
the last name, and any other column you want. Hide the key by setting the
column width to zero since users do not need to actually see it. Now any
other field that you wish to display, you can by setting the controlsource
of a textbox to:

= Forms!FormName!ComboBoxName.Column(1)
= Forms!FormName!ComboBoxName.Column(2)
= Forms!FormName!ComboBoxName.Column(3)
etc.

Column(0) being the first column which will be bound to the accession
number.

 

[Ali8781]

I am looking to do the same thing.. to populate forms with certain info from
a parent form. Do I put the combo box in the orinigal form and then set the
control source in the other forms? Also hese forms have existed for a while
and already have info stored in them. Will I loose that information if I do
this? Thanks.

 

[John W. Vinson]

I am looking to do the same thing.. to populate forms with certain info from
a parent form. Do I put the combo box in the orinigal form and then set the
control source in the other forms? Also hese forms have existed for a while
and already have info stored in them. Will I loose that information if I do
this? Thanks.

Well... first:

No information is stored in your form unless you're doing something really
unusual.

Forms are JUST WINDOWS, tools to manage data. The data is stored in Tables,
and only in Tables.

If you have information in one form, you can display it at will on two (or
twelve!) forms simply by changing the recordsource of the form, or by changing
which record is being displayed. Doing so will prevent one or the other of the
forms from being editable (since Access can't decide which form should be
allowed to edit), but it can still be useful to do so.

If you want a second form to passively display data (in either a bound
control, i.e. actually stored in a table) or in an unbound control (actually
stored as a single record as a property of the form) on a first form, you can
set the Control Source of a textbox or other control on the second form to

=[Forms]![FirstFormName]![ControlName]

 

[Ali8781]

Thanks. However, still having an issue. I set the control source of the
second form as described, but its not showing the information. It just shows
#Name? What am I doing wrong?

I am looking to do the same thing.. to populate forms with certain info from
a parent form. Do I put the combo box in the orinigal form and then set the
control source in the other forms? Also hese forms have existed for a while
and already have info stored in them. Will I loose that information if I do
this? Thanks.

Well... first:

No information is stored in your form unless you're doing something really
unusual.

Forms are JUST WINDOWS, tools to manage data. The data is stored in Tables,
and only in Tables.

If you have information in one form, you can display it at will on two (or
twelve!) forms simply by changing the recordsource of the form, or by changing
which record is being displayed. Doing so will prevent one or the other of the
forms from being editable (since Access can't decide which form should be
allowed to edit), but it can still be useful to do so.

If you want a second form to passively display data (in either a bound
control, i.e. actually stored in a table) or in an unbound control (actually
stored as a single record as a property of the form) on a first form, you can
set the Control Source of a textbox or other control on the second form to

=[Forms]![FirstFormName]![ControlName]

 

[John W. Vinson]

Thanks. However, still having an issue. I set the control source of the
second form as described, but its not showing the information. It just shows
#Name? What am I doing wrong?

Well... it's a bit hard to say because I can't see what exactly you're doing.

What forms are open at the time? Any Subforms involved (the syntax is
different and a bit tricky)? What are the Recordsources of the two forms? What
is the actual Control Source property?

 

[Ali8781]

Only the second form is open. There are no subforms involved. However, all of
our forms draw their info from their own tables. An example of what I put
was the following:
=[Forms]![Accession Log Form]![First name]
Is there a way to do this with the tables instead of forms?

 

[John W. Vinson]

Only the second form is open. There are no subforms involved. However, all of
our forms draw their info from their own tables. An example of what I put
was the following:
=[Forms]![Accession Log Form]![First name]

So it will look on the [Accession Log Form]... which isn't open, so obviously
there is nothing there.

Is there a way to do this with the tables instead of forms?

DLookUp is one way; including the First name field in a combo box's rowsource
is another. It's not clear just what your forms and tables are, so it's hard
to be specific!