adding record to two tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have two tables tblSite and tblRoom.
The concept is that each record in tblRoom must link with a record in tblSite.

I have created two forms, one to add records to tblSite and one to add
records to tblRoom.

On the frmSiteAdd, there is a button that prompts the user to add a Room to
the site record if required at that time. On selecting yes from the yes/no
prompt, the frmRoom opens in Add mode. Everything works fine except that I
cannot get the default Site record (also as a field on tblRoom linked to
tblSite) to display, instead the Site box is blank and awaiting population.

I have created the property default on the Site field of the frmRoom form
but it does not appear to work.

Can anyone help with this?
 
Hi,

I'm having difficulty understanding what you're trying to do. Please post
the structures of all relevant tables and a generic description of your
database's purpose, and I'll see if I can help.

Sprinks
 
The table Site has the following structure:

ID - AutoNumber (Primary Key)
Site - Text

The table Room has the following structure:

ID - AutoNumber (Primary Key)
Site - Text
Room - Text

Each record in table Room must relate to a record in table Site, i.e. a
named room must exist on a particular site.

I need a form that adds a record to the Site table and prompts the user at
the time of update to add a Room to that site record, which will be stored in
the Room table.
It may be at the time of creating a site record that there will be no data
for a room record hence the option.
 
From what you've posted, it looks like you have a one-to-many relationship
between Site and Room--one site may have many rooms. The way to implement
this relationship is with a main form based on Site and a continuous subform
based on Room, linked by the Site ID.

Firstly, however, you should change your table structure so Room.Site is an
Integer field, matching the data type of Site.ID. It will thus become a
foreign key to Site, which is what is appropriate. For further information,
look for information in your references or at Google for "database
normalization".

"Linking" of a subform to the main form on which it exists means setting the
LinkMasterFields and LinkChildFields to the matching fields in the main form
and subform's RecordSource, respectively--in this case, Site.ID and Room.Site.

Hope that helps.
Sprinks
 
I have actioned this and it still does not give me the results that I expect.

The forms work fine, this is the resulting records that I now get:

Site table:
ID: 1
SiteName: Minehead

Room table:
ID: 1
Site: 0
RoomName: IT Services

A relationship exists between Site.ID and Room.Site and the forms are linked
with these fields.

However, I need Room.Site to be populated with the Site.ID value when adding
records to the table Room via the form. Currently it is not being populated
and remaining with a zero value as its data type is Number (to link in with
Site.ID as AutoNumber)
 
Back
Top