Linking more than one access table to a form

[Guest]

Hello.

I have a form that we're planning on using, for work orders, for our
maintenance crews. On this form, I'm trying to automatically populate about
6 fields w/records that are already in Database(A) - these records aren't
intended to be edited, but are used as a reference for our crews (ie: Map #,
Manhole #, etc.). I'd like to be able to use a command button to search for
the UpStream MH# and then that will automatically populate the other 5
fields, based on the information in Database(A)

My form's control source is Database(B) - these are different fields than
what is in database(A) -these fields get updated.

My problem is that I am not sure how I can link and automatically populate
the information in database(A) within my current form.

Any ideas? I appreciate any help anyone can give. If I need to explain
further, let me know!

Thanks!

 

[Arvin Meyer [MVP]]

Think subform and you'll probably figure it out easily. For every record in
the main form, you'll have multiple records in the subform. You will need to
have a foreign key in the subform table that matches the primary key in the
main form table.

 

[Guest]

Have you tried to join them in a query and use the query as source?

 

[Guest]

Yes, I tried joining them in a query and then using the query as the control
souce. When I did this and went to view my form, it would be completely
blank. Perhaps I am doing something wrong...

As for a subform, I am not familiar with how those work. I understand how
to create them on the form (subform control), but not sure how they interact
w/the primary form.

The main purpose of this form is when the work crew gets a call for a sewer
backup, they can look at a map/address and see that (001LS) is the upstream
manhole that is effected and then they hit the "search mh#" button on the
form and type in the mh number (001LS) it automatically populates the
downstream manhole and 5 other fields from the "main" database. The other
information they enter - the person who called, address, what actions were
taken, etc. - is all added to another database. I want them to be able to
print out the form/work order to take with them that shows all of this
information, from both databases.

Thanks

 

[Arvin Meyer [MVP]]

Yes, I tried joining them in a query and then using the query as the
control
souce. When I did this and went to view my form, it would be completely
blank. Perhaps I am doing something wrong...

Unless the query is updateable, you will not be able to see or enter new
records. Make sure that it is, if you want to edit.

As for a subform, I am not familiar with how those work. I understand how
to create them on the form (subform control), but not sure how they
interact
w/the primary form.

From what I read below, a subform is your answer. You can see a sample of
how it works with the Northwind sample database that ships with Access. To
see it, look at Help >>> Sample Databases.

 

[Guest]

Please bare with me - I am attempting to use a subform as you suggested. I
am wanting to have a popup Subform_B that you can use to search Database_B
and get all the manhole info that is already in that database. But - the
kicker is that when you close Subform_B - it automatically populates
Mainform_A in identical fields like the one searched. Yes, this makes
repetitive data - but, that is what is wanted. Any ideas? I appreciate any
help!!!

 

[Arvin Meyer [MVP]]

I only bare in the shower, but I'll try to bear with you. <g>

A subform is an integral part of a main form, not a separate popup. Are you
trying to retrieve some data, then duplicate it? Do you want to be able to
make changes to the new record before you commit it to the database as a
duplicate (with new information)?

A word of warning. Retrieving some information from a record which will be
duplicated in a new record, along with some new information is OK. If it's
an exact duplicate though, all you really need is a new ID value and perhaps
a field or 2 to show a difference (like the date entered). In that case,
Table_B only needs to store the ID from table_A and the additional
information.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

hehe...Sorry about that - I blame not spelling "bare/bear" correctly on
having just gotten out of the most boring meeting ever and then trying to
immediately be coherent. Good news though - I believe that I have gotten the
main form/subform working as I wanted it to. Thanks for your input regarding
subforms - that is what I used.