Trouble with using a form to update a table

[Access_Novice]

The attached Word file shows screen shots of what I am dealing with. I have two tables called Roster and Balance that are related. The first record in the Roster table has the name "Ali Arnold."



The Balance table is blank.

I also have a form called Balance which is bound to the Balance table. The Balance form has only two fields, first name and last name. I entered Ali for the first name, and Arnold for the last name. And I get an error message saying "You cannot add or change a record because a related record is required in table 'Roster.'

So I am entering values in the form which are definitely in the Roster table.

By adding "Ali Arnold" I am adding something for which there is already a related record in the Roster table. I don't understand why I am getting this error.Access Question.zip

[June7]

Because you shouldn't save names in Balance, save the gymnastID. gymnastID is the primary key so should be saved as foreign key in Balance. Do not repeat names in Balance, that is duplication of data.

Also, really shouldn't keep 'balance' data anyway. An account balance should be calculated based on the net aggregate sums of all transaction records (obligations - payments = balance). http://allenbrowne.com/AppInventory.html

[Access_Novice]

Thank you June7. I understand what you mean by foreign key and duplication of data. The reason why I had names appearing in the balance table in addition to the roster table, is because I want the user to enter payment information into the balance form and with that enter the gymnast's name. I wanted them to enter the gymnast's name because that would be easier than entering the gymnast ID. Is there anyway around this?

[June7]

Use combobox to select gymnast by name but save the ID.

Combobox properties:

RowSource: SELECT gymnastID, LastName & ", " & FirstName As GymnastName FROM Roster;

BoundColumn: 1

ColumnCount: 2

ColumnWidths: 0";2"

[Access_Novice]

Got it. Thanks June7.