Re: changing a recordset through a form

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jeff Boyce (JeffBoyce_IF_at_msn.com-DISCARD_HYPHEN_TO_END)
Date: 12/24/04 

Date: Fri, 24 Dec 2004 05:02:25 -0800

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... 

-- 
Good luck
Jeff Boyce
<Access MVP>
"dshemesh" <dshemesh@discussions.microsoft.com> wrote in message
news:D630712E-10B6-48A9-8C1D-025751DA4895@microsoft.com...
> It is based on a query which joins the necessary fields from each table.
>
> "Jeff Boyce" wrote:
>
> > Is your form based directly on the table, or on a query which joins the
> > underlying tables you describe?
> >
> > -- 
> > More info, please ...
> >
> > Jeff Boyce
> > <Access MVP>
> >
> > "dshemesh" <dshemesh@discussions.microsoft.com> wrote in message
> > news:214F391E-27B0-4C9B-A3E2-D5FEDAC646B0@microsoft.com...
> > > 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...
> > > -- 
> > > dshemesh
> >
> >

Relevant Pages

  • Re: changing a recordset through a form
    ... > I don't understand, however, how the tblBuilding would have its data ... > RoomID ... > BuildingID ... >> It is based on a query which joins the necessary fields from each table. ...
    (microsoft.public.access.forms)
  • Re: Query Help Please - Consecutive Dates
    ... > CustomerID RoomID ArriveDateID DepartDateID ... into Query Analyzer. ... DROP TABLE bookedrooms ...
    (comp.databases.ms-sqlserver)
  • Re: Booking System - Vacant room report
    ... (SELECT DISTINCT RoomID ... If a room is booked for any one night in the range in the sub query, ... generate a report based on the top 30 records of any table but having ...
    (comp.databases.ms-access)
  • Re: Booking System - Vacant room report
    ... The temp table was filled by some query, ... select * from Customers ... RoomID and certain dates, then for a given date range, how do I create ...
    (comp.databases.ms-access)
  • Re: Query using two columns
    ... >I now want to try an construct a query that will show a row for every ... CREATE TABLE Numbers (Number int not null primary key) ... DECLARE @MaxNumber int ... WHERE RoomID = 'Big Room' ...
    (microsoft.public.sqlserver.programming)