Help!! Confused (1 Viewer)

Ok. I dont know a whole lot about Access but have spent hours trying.

My issue...

I have many fields in one table. When I enter the data into the form, I want to be able to press a button and have a few of the fields auto populate into another field.

Example. I deal with school discipline. I have many students come in on a daily basis. When I enter the information into a form, it goes to the table. However when I assign a consequence... such as a detention... I want their name, and assigned detention date to leave the form and end up on a table that is reserved to track detentions. This is not for every instance which is why I cant just marry the two tables.

Basically I want a button on the form that sends three specific pieces of information to another form so that I can keep track of who exactly has detentions without having to retype it into a separate form. Can anyone get me in the right direction?

the main table is called students...the four fields are first name, last name, date assigned and date opened.

the second table is called detentions and I want the four fields from above to populate when I tell them to from the 'students' form.

I meant into another table not into another field

the main table is called students...the four fields are first name, last name, date assigned and date opened.

the second table is called detentions and I want the four fields from above to populate when I tell them to from the 'students' form.

Click to expand...

It shouldn't work the way you have explained. The correct way is to have a unique autonumber id field in the first table. Then, in the second table you put the unique autonumber id value. Instead of 4 'real' data fields, you simply put this one internal field and you have married your records to each other.

Now, whenever you want all this 'real' data to appear together, you create a query, linking them by the id fields and you can pull in fields from the first and second data and have it all appear like you want.

Next lesson, sub-forms. This is where you will learn how to use that correct structure with input forms.

Ok. I am going to try doing what you suggest and see where I get. I did try the subforms thing, but it only pulled in the other form (which was good) but i still had to retype the information that I wanted in that form.

we shall see...

ugh... my issue is too complicated!

When I assign a detention... I want to tell the form that I assigned a detention and I want the kids name, the issue date, and the opened date to automatically appear in another table.

after the kid serves the detention, I want to be able to go into that detention form and delete the kids that have served.

I do not want to submit all the data into one table and then query to organize the data into what I need. I want it on a separate table. I know there is a way to do this, but cant figure out how.... I believe it has to do with macros.


another great option... when I pull down the list box for the possible consequences, and select 'detention' , it would be great if it would automatically add a few pieces of information to another table. again, I am sure it can be done, but I cant figure it out.

It has to do with properly structuring your data and using primary and foreign keys.

http://www.databaseanswers.org/tutorial4_db_schema/tutorial_slide_5.htm

And it is all part of data normalization:

http://support.microsoft.com/kb/283878

Plog has it right. your problem has nothing to do with macros - and you can manage without macros anyways (apart from maybe autoexec) - it can all be done in VBA.