Fill in user information automatically

[BG44]

In the UserId ( primary key) box , I would like to start typing an ID and
have it appear in the UserId box (like a combo box?); after choosing the
correct ID, I would like the name, address, etc. to populate atomatically. If
there is no previous entry for my User ID, I will need to type the
information in name, address etc. All of this information is stored in one
table named Producer Information - this has a foreign key for accession # -
as many accession #'s could belong to one User ID. Thank you..

 

[Steve Schapel]

BG,

I don't think I have enough information about your database to advise
explicitly. But I am pretty sure you need to pause here, and revise
your table structure. For a start, it is not correct to be entering the
"name, address etc." for each user over again - this data should be
stored one time in one table only. And then, regarding your "accession
#" (by the way, it is not a good idea to use a # as part of the name of
a field), if "many accession #'s could belong to one User ID" then we
would expect this to be represented by a User ID as a foreign key in the
Accessions table, not the the other way around... well assuming it is
not the case that at the same time there can be many User IDs associated
with one Accession.

 

[BG44]

My database holds information from a dairy farm survey (# of cows, equipment
check info, vet, on and on). Each farmer has a unique ID that is their
identifying factor. Each farmer may have receive one or more technician
visits per year, depending on their milk quality. Each time we visit, we
assign an accession # to the visit, to distinguish it from previous entries.
The database I created has a main form plus 4 tabs containing subforms. Each
subform automatically gets filled with the Unique ID and Accession #. The
accession number is required.

The first page contains is for identifying the farmer: name, addess, etc.
When I put in their Unique ID, I am hoping to see the fields fill with the
correct information for name, etc. But I may need to enter new farmer data
for farms not previously visited so that has to be an option. Thank you.
Belinda

 

[Clif McIrvin]

Belinda, I think we could give you better help if we had a bit more
information about your design.

What is the table structure behind this form you are describing? From what I
see, you should have at least three tables:

Dairies for information about each dairy operation that occurs only once --
PK is farmer unique ID.

Vets for name, address, contact info etc for each vet (assuming that you
track this information.) -- PK will be a unique vet ID.

Visits for survey information that is gathered for each survey -- PK is
AccessionID, with a foreign key (FK) linking it back to the dairy, in this
case the farmer unique ID; and another FK (VetID) linking it to the Vets
table. (Visits would be a child table to the dairies table.)

You might want to consider setting up a child table for Vets where the
parent table contains data about the veterinary practice, and the child
table contains information about the individual vets who make up the
practice; etc.

When you have a table structure as I have described, you can set
relationships between the tables using the various primary and foreign keys.
With defined relationships and bound controls on your form Access will
automatically populate the controls for the child table when you select a
record in the parent table.

With a better knowledge of your table structure Steve or others in this
group can give you better assistance.

To review: I'm guessing that the answer to your question involves either
relationships or bound controls, or both.

 

[Steve Schapel]

Belinda.

Sounds like a very interesting application to be working with.

From your description, I would assume you have a table for Visits, and
the Accession # is the Primary Key field for this table. And in that
case, yes, you would have the Unique ID as a Foreign Key to identify the
farm that the Visit record is associated with. But that should be the
*only* farm(er) identifying data in the Visit table, as the rest of it
is easily accessible via reference to the Farm table or whatever it's
called. Understand? Now it could be that you want to *display* some of
the associated farm(er) data on the Visits form, which is perfectly
understandable, and very common scenario. In that case, there are
various approaches, which this article may help to clarify for you:
http://accesstips.datamanagementsolutions.biz/lookup.htm

 

[BG44]

My mistake now is obvious. I need to make the farmer information separate and
not the master form. I need to make the herd information (date of visit, type
of cow, how many cows, daily milk wgts., etc.) the master form and my 3 other
tabs (herd health, milking procedures and milking systems)(which are separate
tables) children so they connect with the ID and Accession # entered in the
herd info form.

So the data entry person uses the farmer information form to add farms or
make changes so that info is kept up to date but not repeated with each
accession.

Is there a way to change my already created form with subforms to make the
subform herd info the master without doing everything all over?

Is it possible to make a little screen to pop up on entry of the Unique ID
that will show they are working with the correct farmer?
Sort of like: enter 123 for UID and a message says: John Jones, 557 Right
Lane, Ithaca, NY - is this the correct farm?

Thanks very much for all your help.

 

[Steve Schapel]

Belinda,

It is difficult to answer your questions specifically, as I don't have
the full details of what you are working with.

In general, the first step is to make sure you have your tables properly
designed. Then forms come after that. I would imagine that you will
probably need to do a bit of work on your forms. Not sure that you
would need to "do everything all over", but probably a fair bit of juggling.

So the herd health, milking procedures, and milking systems, all are in
a many-to-one relationship with the 'herd information' table? If so,
then the Accession # would be the linking field, and you would not have
an ID field in there (that is, if you are referring to the farm ID).

I would be happy to explore this with you if you want. Just on the face
of what you have said so far, I kinda suspect there is a problem with
your data model (table design/structure). Just post back with a list of
the fields in each of the tables, with an indication of how they are
related to each other.

Having said that, the answer to your specific question is Yes. You
could have a VBA procedure on the After Update event of the UID control
on your form, that looks up the relevant Farm information, and then
displays it in a MsgBox.

Another approach to safeguard getting the correct farm, would be to have
the UID entered via a multi-column combobox, so the user can see the
name and address in the combobox's drop-down list at the time they are
entering the UID.