Recordset not updateable.

[stevekos07]

Hi. This seems like such an elementary question it is almost embarrassing, but I need to get it right in my mind.

I sometimes need to add a new field into client's database, but I don't want to keep on just adding fields to an existing table. So I want to create a table with a new data field (e.g. tblClientGender) with two fields:

ClientID (foreign key from tblClients, and the primary key for this new table).
Gender (New field).

But when I add this new table into an existing query I can't update the Gender field because obviously the ClientID is not present for each record, so it is not an updateable table.

How to I make a newly created table editable when incorporated into an existing query?

I hope I am making myself clear.

 

[ashleedawg]

If I understand correctly...

In tblClientGender, is ClientID set to AutoNumber? It shouldn't be. The ID is created in the other table.

[B][U]tblClient[/U][/B]
ClientID         AutoNumber (primary key, long integer, increment)
ClientName    Short Text

[U][B]tblClientGender[/B][/U]
ClientID         Number (primary key, long integer)
Gender          ShortText

[U][B]Query1[/B][/U]
SELECT tblClient.ClientName, tblClientGender.Gender
FROM tblClientGender INNER JOIN tblClient ON tblClientGender.ClientID = tblClient.ClientID;

Recordset is updatable.

 

[stevekos07]

In tblClients, ClientID is the primary key and is Autonumber. In tblClientGender, ClientID is the primary key and is set to Number.

The table is editable, but I need to automatically provide the ClientID number in the tblClientGender table with the ClientID that is in the other table.

Perhaps I can achieve it by hiding the tblClientGender>ClientID field in the form, and making the default value the tblClients>ClientID as an OnLoad event?

I'll try it and let you know how it goes.