changing a recordset through a form

[Guest]

Hello,
I encoutered the following problem when trying to let the user change a
recordset (or/and add new records to the recrdset) using a form:
I want the form to show all the records of a certain table on the same time
(i.e. I do not want the user to be able to see only one record at a time),
and to give the user the ability to change the existing records and add a new
record if wanted. Up to here everything is working fine, but the problem is
this:
each record in the table I want to change has a certain property taken from
anoter table.
For example: In the original table (the one I want the user to change) there
is a field called 'roomId'.
There is another table which is called roomsT in which there are the
following fields: roomId, roomName, buildingId. meaning, every room is a part
of a building, and this information is written in the roomT table.
There is a third table called buildingsT containing the following fields:
buildingId, buildingName.
I want to show in the form each and every record from the original table,
plus the appropriate building for this record. moreover, I want to give the
user the possibility to change any field in the original table, including the
field 'roomId'. If the user changes the 'roomId' of a record, I want the
buildingName on the form to change automatically to the appropriate building
(but only the buildingName of the record which was changed. all other
buldingNames on the form, of other records, should remain unchanged). What I
don't want (and can't find a way to avoid) is that when the user changes the
'roomId' of an existing record, the buildingName will not change in the
biuldingsT table.
For example: If the original table has a record in which the roomId was
'25', and the building to which this room belongs has a buildingId '44', and
a building name 'a'. when the user changes the roomId in the original table
to be, let's say, 20, and the building to which this room belongs to has a
buildingId '40' and buildingName 'b', I do not want the buildingName of
buidingId '40' to be changed to 'a' (which happens right now).
Moreover, if the user adds a new record to the original table, a new record
is currently added to the buildingsT table (given a new automatic Id, and the
correct buildingName). And of course this is unwanted, since the buildingsT
table should remain unchanged after adding a new record to the original table.
Can anyone please help?
thank you...

 

[Jeff Boyce]

Is your form based directly on the table, or on a query which joins the
underlying tables you describe?

 

[Guest]

It is based on a query which joins the necessary fields from each table.

 

[Jeff Boyce]

If this were happening to me, I would look at the fields that are included
in the query -- I'd check to make sure that the RoomID field that the users
can change is the one that comes from the main table.

I don't understand, however, how the tblBuilding would have its data
changed. From your description, it sounds like you have:

?YourTable
...
RoomID

related one-to-many to:

tblRoom
RoomID
RoomName
BuildingID

related one-to-many to:

tblBuilding
BuildingID
...

If this is accurate, changing RoomID in YourTable won't affect a row in
tblBuilding. But as I mentioned at the top, maybe it isn't YourTable's
RoomID that your query/form is changing...

 

[Guest]

Thanks.
The problem turned out to be my code. I was actually forcing the buldings
Table to change through my code, in order to show the correct building for
each row in the original table instead of just writing 'requery'...
anyway, you helped me very much, only by making me understand that the
problem was with something I was doing, and not an access problem...
Thanks again,
dshemesh