Help!!! Update Query to update fields in table??? (1 Viewer)

[southwestgooner]

Hi All,
I have a form that creates new records without any issues whatsoever. The form contains four fields that have linked ID's that won't update in the source player table!
I have a tables for Category with a autonumber for CategoryID with a list of Categories selected from a Combo Box in the form.

Basically the same applies to the Season, Gender and Squad tables.

I also have a Players table that contains the fields in the attached screenshot. There are relationships between the Category, Season, Gender and SquadIDs in the Players table to the four ID's in the Category, Season, Gender and Squad tables.

The issue I would like to resolve is the four ID columns remain empty when adding new records via the form.

Is it possible to create four update queries to up date the four ID columns in the Players table???
I can then run the SQL/VB code from an Update button on the form.
Or is there an easier way to populate this data in these four fields in the Players table from the selections picked in the form?

Many thanks in anticipation of some help to resolve this issue that is really bugging me as I think it's possible but all attempts to date have failed!!

Regards,
Steve aka southwestgooner

 

[plog]

I have a tables for Category with a autonumber for CategoryID with a list of Categories selected from a Combo Box in the form.

You're doing it wrong. That combo box should be bound to CategoryID in tbl_Players, not the Category field. In fact, the Category field should not be in tbl_Players at all. That combo box can show the Category name to users, but should use the ID field to save to the table. Here's another thread that will tell you how to do that:

show name in a combo box but store id

hello, i have a table [coordinator] and table [program] linked together through table [coordinator_program] which contain primary keys of both.. i have a form with combo box that let the user choose the coordinator of program but it shows the id because the record source is coordinator id from...

www.access-programmers.co.uk

Those other fields should work the same way--bind the combo box to the ID field, show the actual name and save the ID to tbl_Players and remove all those non-ID fields.

Also, what happens next year? Do you just add a new record for the same players? Seems your tbl_Players table isn't really a table for players but a table for assignment to squads (e.g. tbl_PlayerSquads).

 

[Pat Hartman]

The form contains four fields that have linked ID's that won't update in the source player table!

You have not defined the relationships correctly and have misunderstood the concept of cascading updates.

If you post the BE, we can fix the schema. OR post a picture and people will offer suggestions but you'll need to follow them correctly.