Pulling data from a table into a form...automatically

[joshapalooza]

I am new to scripting (and to Access), so don't be bashful if you feel you have to explain things to me on an elementary level. I have managed to create a table and its subsequent form title "Personnel Info". I have also created a table and form titled "Recall" for address and telephone contact information. What I would like to be able to do is use information from the Personnel Info form and have it automatically pull data into the Recall form by using the Primary Key. So, for example, when I open the Recall (or whatever other form I can think to make) I want a box to pop up asking me which Marine I'm inputting data for and then once I make my selection, have it auto-populate information (such as rank, last name, first name, middle initial, etc.) into the appropriate fields. I hope I've given everyone enough information to go off of. By the way, when you reply, could you do me the favor of explaining what some of this script means so I can get an idea on what I'm doing? Thanks in advance!

[Jim Doherty]

I am new to scripting (and to Access), so don't be bashful if you feel you have to explain things to me on an elementary level. I have managed to create a table and its subsequent form title "Personnel Info". I have also created a table and form titled "Recall" for address and telephone contact information. What I would like to be able to do is use information from the Personnel Info form and have it automatically pull data into the Recall form by using the Primary Key. So, for example, when I open the Recall (or whatever other form I can think to make) I want a box to pop up asking me which Marine I'm inputting data for and then once I make my selection, have it auto-populate information (such as rank, last name, first name, middle initial, etc.) into the appropriate fields. I hope I've given everyone enough information to go off of. By the way, when you reply, could you do me the favor of explaining what some of this script means so I can get an idea on what I'm doing? Thanks in advance!

Hi Josh and welcome to the scripts!

The way you do this is to keep your tables normalised so that they store only the data they need to store. You don't grab physical data from one table, copy it and place it in another table. This in principle creates potential redundancy and breaks the relational model

To make sense of this you use 'queries' that link the relevant tables together using the primary key from one table (or some other key that provides the capability of 'pulling the related data') and referencing the foreign key in the related table.

I think practically speaking rather than me type something that could in essence get the size of a rather large 'barn door' explaining the theories of it all....let me say this... if you were to post your table structure ( or maybe even attach a small empty database of tables with names already done and that you are familiar with already, I could quite easily give you something back in the form or a 'revised' attachment that you could benefit from and which others who might also be 'new' and reading might also benefit. I could throw in some useable generic functions, queries etc.... your masterpiece if you like would then become available to download within this thread to learn from. and for which others might wish to contribute to to. Its a matter for you. and yes I would comment the code and relevant logical steps taken.

I rather like the concept of the generic, open sourced, first wikipedia type ever improving mdb file it might catch on :))

Regards

Jim :)

[joshapalooza]

Hi Josh and welcome to the scripts!

The way you do this is to keep your tables normalised so that they store only the data they need to store. You don't grab physical data from one table, copy it and place it in another table. This in principle creates potential redundancy and breaks the relational model

To make sense of this you use 'queries' that link the relevant tables together using the primary key from one table (or some other key that provides the capability of 'pulling the related data') and referencing the foreign key in the related table.

I think practically speaking rather than me type something that could in essence get the size of a rather large 'barn door' explaining the theories of it all....let me say this... if you were to post your table structure ( or maybe even attach a small empty database of tables with names already done and that you are familiar with already, I could quite easily give you something back in the form or a 'revised' attachment that you could benefit from and which others who might also be 'new' and reading might also benefit. I could throw in some useable generic functions, queries etc.... your masterpiece if you like would then become available to download within this thread to learn from. and for which others might wish to contribute to to. Its a matter for you. and yes I would comment the code and relevant logical steps taken.

I rather like the concept of the generic, open sourced, first wikipedia type ever improving mdb file it might catch on :))

Regards

Jim :)

Hi Jim and thanks for the reply! I am absolutely on board with the concept of reducing (if not eliminating) redundancy. I'm not sure exactly how I would go about posting my database, but essentially it revolves around one central table entitled Personnel Info. From this table, I want other tables - through their related forms - to draw specific information about a person. For instance, I made a table titled Recall which contains contact information. Instead of manually typing in rank, last name, first name, and middle initial, I want a window to pop up that asks whether we are adding a new Marine or editing information on an existing Marine, based on the information found in Personnel Info. Obviously two different actions would occur based on your selection, but I think you probably have the general idea of what I'm trying to accomplish. I was able to find a posting here that talked about how to Append a Query. The only problem I have with that is that you have to manually run the Append Query (unless you set up a startup macro) and even then it gives you error messages if you haven't entered any new information into your main database. I specifically would like to understand how to write the script for the pop up window and subsequent script for the two actions. Additionally, I would like to know how to be able to select a name from the pop up window and have it populate the pertinent information into the new form automatically. I'll add the structure of both of my tables so you can have a better understanding of what information I'm working with.

Personnel Info

1. SSN (Primary Key)
2. Rank
3. Last Name
4. First Name
5. MI
6. BT (Blood Type)
7. Height
8. Weight
9. Body Fat%
10. DOB (Date of Birth)
11. PEBD (Pay Entry Base Date)
12. AFADBD (Armed Forces Active Duty Base Date)
13. EAS (End of Active Service)
14. Marital Status
15. Meal Card #
16. Spouse Name
17. Children Name

Recall

1. SSN (Primary Key)
2. Rank
3. Last Name
4. First Name
5. MI
6. Street Address
7. City
8. State
9. Zip Code
10. Home Phone
11. Cell Phone
12. Alt Phone

I also have another table titled Ground Training that contains the following information.

1. SSN (Primary Key)
2. Last Name
3. PFT Date
4. PFT Score
5. PFT Class
6. Rifle Range Date
7. Rifle Score
8. Rifle Class
9. Pistol Range Date
10. Pistol Score
11. Pistol Class
12. BST Date
13. BST Score
14. Swim Date
15. Swim Qual
16. NBC Date
17. Helmet Size
18. Gas Mask Size

I have another table titled Rank that enables me to have a pull down tab on the Personnel Info Form rather than manually typing the rank in. I was toying with the idea of doing the same for marital status, that way I could just select one from a list. I'm not sure if I have to do that or not or if I could somehow figure out the combo box function. Also, is it necessary for me to repeat the names in the various tables or could I just use the primary key? I wasn't sure. Obviously, as I get more skilled at creating a database, I will add more information and more tables. I just need to understand how to actually create what I'm envisioning this database to do. Hope this is the information you were looking for. If not, square me away and I'll try to understand it enough to send you what you need to help me.

Sincerely,

Josh

[Jim Doherty]

Hi Jim and thanks for the reply! I am absolutely on board with the concept of reducing (if not eliminating) redundancy. I'm not sure exactly how I would go about posting my database, but essentially it revolves around one central table entitled Personnel Info. From this table, I want other tables - through their related forms - to draw specific information about a person. For instance, I made a table titled Recall which contains contact information. Instead of manually typing in rank, last name, first name, and middle initial, I want a window to pop up that asks whether we are adding a new Marine or editing information on an existing Marine, based on the information found in Personnel Info. Obviously two different actions would occur based on your selection, but I think you probably have the general idea of what I'm trying to accomplish. I was able to find a posting here that talked about how to Append a Query. The only problem I have with that is that you have to manually run the Append Query (unless you set up a startup macro) and even then it gives you error messages if you haven't entered any new information into your main database. I specifically would like to understand how to write the script for the pop up window and subsequent script for the two actions. Additionally, I would like to know how to be able to select a name from the pop up window and have it populate the pertinent information into the new form automatically. I'll add the structure of both of my tables so you can have a better understanding of what information I'm working with.

Personnel Info

1. SSN (Primary Key)
2. Rank
3. Last Name
4. First Name
5. MI
6. BT (Blood Type)
7. Height
8. Weight
9. Body Fat%
10. DOB (Date of Birth)
11. PEBD (Pay Entry Base Date)
12. AFADBD (Armed Forces Active Duty Base Date)
13. EAS (End of Active Service)
14. Marital Status
15. Meal Card #
16. Spouse Name
17. Children Name

Recall

1. SSN (Primary Key)
2. Rank
3. Last Name
4. First Name
5. MI
6. Street Address
7. City
8. State
9. Zip Code
10. Home Phone
11. Cell Phone
12. Alt Phone

I also have another table titled Ground Training that contains the following information.

1. SSN (Primary Key)
2. Last Name
3. PFT Date
4. PFT Score
5. PFT Class
6. Rifle Range Date
7. Rifle Score
8. Rifle Class
9. Pistol Range Date
10. Pistol Score
11. Pistol Class
12. BST Date
13. BST Score
14. Swim Date
15. Swim Qual
16. NBC Date
17. Helmet Size
18. Gas Mask Size

I have another table titled Rank that enables me to have a pull down tab on the Personnel Info Form rather than manually typing the rank in. I was toying with the idea of doing the same for marital status, that way I could just select one from a list. I'm not sure if I have to do that or not or if I could somehow figure out the combo box function. Also, is it necessary for me to repeat the names in the various tables or could I just use the primary key? I wasn't sure. Obviously, as I get more skilled at creating a database, I will add more information and more tables. I just need to understand how to actually create what I'm envisioning this database to do. Hope this is the information you were looking for. If not, square me away and I'll try to understand it enough to send you what you need to help me.

Sincerely,

Josh

OK Josh I get all of that.... now to attach a file all you do is post a reply at which point you then have a short time to go back in and edit any mistakes you make. Look on screen for the 'edit reply' bit you will see a manage attachments and a capability to upload a file.

If you upload your mdb file contained in a compressed zip folder and then attach the folder it saves me having to type all that out and create the tables.

Firstly off I can say this. Have a look at naming conventions in Access. Lechinsky/Roddick naming convention is a good standard (google that or search on here) also drop the spaces in fieldnames!! saves you having to later wrap them in square brackets when you reference them.

In the meantime, and this is not connected actually but does have 'certain' similarities, I am going to attach a file for you to look at and play with which is a demosearch thing I did one time that has various bits in it that might interest you, or at least spur you onto to thinking of various different ways of doing things if you look at the code (which you will eventually need to understand ok? not necessarily this example but code generally).

In the meantime I will press on and create tables for what I think your data model needs to be based on your post. (quickie example people can have more than one child so a separate table for that if you get me :)

Jim :)

[joshapalooza]

OK Josh I get all of that.... now to attach a file all you do is post a reply at which point you then have a short time to go back in and edit any mistakes you make. Look on screen for the 'edit reply' bit you will see a manage attachments and a capability to upload a file.

If you upload your mdb file contained in a compressed zip folder and then attach the folder it saves me having to type all that out and create the tables.

Firstly off I can say this. Have a look at naming conventions in Access. Lechinsky/Roddick naming convention is a good standard (google that or search on here) also drop the spaces in fieldnames!! saves you having to later wrap them in square brackets when you reference them.

In the meantime, and this is not connected actually but does have 'certain' similarities, I am going to attach a file for you to look at and play with which is a demosearch thing I did one time that has various bits in it that might interest you, or at least spur you onto to thinking of various different ways of doing things if you look at the code (which you will eventually need to understand ok? not necessarily this example but code generally).

In the meantime I will press on and create tables for what I think your data model needs to be based on your post. (quickie example people can have more than one child so a separate table for that if you get me :)

Jim :)

Thanks for the sample Jim. Here is a blank copy of my database as it stands now. Hope this isn't too basic for you.

[Jim Doherty]

Thanks for the sample Jim. Here is a blank copy of my database as it stands now. Hope this isn't too basic for you.

Check your PM,s Josh I,ve sent you one

Jim