If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Fill in user information automatically
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.. |
#2
|
|||
|
|||
Fill in user information automatically
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. -- Steve Schapel, Microsoft Access MVP BG44 wrote: 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.. |
#3
|
|||
|
|||
Fill in user information automatically
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 "Steve Schapel" wrote: 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. -- Steve Schapel, Microsoft Access MVP BG44 wrote: 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.. |
#4
|
|||
|
|||
Fill in user information automatically
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. -- Clif Still learning Access 2003 "BG44" wrote in message ... 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 "Steve Schapel" wrote: 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. -- Steve Schapel, Microsoft Access MVP BG44 wrote: 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.. |
#5
|
|||
|
|||
Fill in user information automatically
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.datamanagementsolu...biz/lookup.htm -- Steve Schapel, Microsoft Access MVP BG44 wrote: 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 |
#6
|
|||
|
|||
Fill in user information automatically
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" wrote: 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.datamanagementsolu...biz/lookup.htm -- Steve Schapel, Microsoft Access MVP BG44 wrote: 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 |
#7
|
|||
|
|||
Fill in user information automatically
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. -- Steve Schapel, Microsoft Access MVP BG44 wrote: 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? |
Thread Tools | |
Display Modes | |
|
|