Subform showing specific records not updating main form 1

[Greg553]

Hello, This
This is my first post here, but as I was reading I have already learned a lot , great site.

Here is my issue. I have two tables,

player information and Weight Lifting information

Each table has a form. Player information form adds the athletes information to the player info table and works fine.

The weight lifting form has a sub form linked to the weight lifting table. There is a combo box on this form linked to the player info table so i can get the players names.

The main form has text boxes for adding lifts like bench press, squat bound to the weight table.
When i pick a player from the combo box The sub form updates showing just his records from the weight table just fine , but the main forms ( text boxes )data does not change to show the record that is in the sub form. it stays the same.
I have tried everything and just can't get it right.
Any help would be appreciated.

 

[dhookom]

Can you be more specific with your identification of tables and forms and subforms and record sources and row sources? You say you have two tables "player information" and "Weight Lifting information" but then use abbreviations and aliases. Is "weight table" the same as "Weight Lifting table"?

When you state "The main form has..." is this the weight lifting form? Does this form have a record source? Please don't use "main" form when there are possibly more than one main form.

Duane
Hook'D on Access
MS Access MVP

 

[Greg553]

Sorry about the confusion.
I'll explain it better.
this is what I have:
Table Player Information
Table Weight Lifting Information

Form Player Information ( Bound to Player information table)
Form Weight Lifting records ( bound to weight Lifting Information table)

The player information form and table work fine, as i input players names,weight,height,grade etc. They get there player ID from the Auto number as entered.

I than have a button that takes me to the Weight Lifting form to enter there weight lifting performance. There are 8 fields ( text Boxes), bench press, squat, dead lift etc to enter info on the form.
I have a combo box linked to the Player Information table to get the names entered, I' use the Player ID (P_ID) from that table and storing it in the Weight lifting records table with the lift results.
I can get the records for a player by choosing his name from the combo box below in a sub form( weight lifting records sub form), but the text boxes on the form do not update with that players information for the most recent record. it remains what ever was typed in from the last player.
I'm trying to get it to show his records so i can update them there and register in the table.

I hope that i explained it well enough.
If not please let me know
thanks

 

[dhookom]

Are the Record Sources for your [Weight Lifting records] form and the subform both the same? If so, why? I would expect a [Weight Lifting records] form to be unbound with a combo box to display the players. You could use the link Master/Child properties of the subform control to limit the subform to the player from the combo box.

Unless I am missing something important, I would have a main form based on the Player Information and a linked subform of the Weight Lifting records.

I also don't really agree with your table structure of fields like "bench press, squat, dead lift etc". I would probably have a LiftType field and a quantity field. If a player performed both Bench and Squat this would create two records in the normalized table.

Duane
Hook'D on Access
MS Access MVP

 

[Greg553]

I understand where you are going with this. What I have fields for all those lifts. What your saying is I should only have those two fields, now if a player does all the major core lifts that we track, he could have 8 different records. Would it be easy to query those for reports?

My next question would be, every time he breaks a record and I input it it would it over write the previous record? I would like to keep a history of there progression. I also use a date field and grade level.

Now how would you set up the entering of these lifts into the table. Use a pull down a choose a lift, or have text boxes for each of those lifts and just enter what you need to.

Thanks for all your help, there is so much to learn. I see how important it is to set up tables in a proper manor to make things easy

 

[Greg553]

Hello again,

I have another question. Making a table called Tbl_WeightRecords with fields of Lift type, Weight lifted, and date. Would you break of the lift type into possibly two fields like upper body lifts and lower body lifts?

Also how would i keep a history or archive the results as they move to the next year.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

I agree with PH. You need to understand how to create normalized tables. I would expect the "records" would simply be the Max of a history table.

Duane
Hook'D on Access
MS Access MVP

 

[Greg553]

Thanks, that is a very good article and did help me out a lot.
I understand what I need to do now.
Greg