Subforms and one-many-one relationships

  • Thread starter Thread starter lesley
  • Start date Start date
L

lesley

Please can someone help with a subform query.

I have a db with 3 main relations plus 2 linking relations. On top of
these I have 1 main form with 2 subforms. I have set up the
relationships without any problem and the subforms show the relevant
records for the main form but I have two problems:

1. I get an error message when I try to add any new records to the
subforms
2. I have set the subforms up with combo-boxes and chosen limit to
list but when I choose one of the names on the list it gets added to
the relation again

All help most appreciated.
Lesley
 
Please can someone help with a subform query.

I have a db with 3 main relations plus 2 linking relations. On top of
these I have 1 main form with 2 subforms. I have set up the
relationships without any problem and the subforms show the relevant
records for the main form but I have two problems:

1. I get an error message when I try to add any new records to the
subforms
2. I have set the subforms up with combo-boxes and chosen limit to
list but when I choose one of the names on the list it gets added to
the relation again
Click to expand...

Lesley,

1. what error message you get?

2. Is the combo bound? I guess yes, because the name gets saved. And I
guess you are confusing something. The limitToList property means, you
can only enter names in the control which are in the list, i.e. you
can only make a choice from the given list of names but not enter new
names. The property does NOT limit the records saved in a table, and
does NOT prevent you from entering duplicate data in a table.

To solve this, please give more details about the tables, the combo,
what is displayed in the combo, what do you want to achieve.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de
 
Hi Emilia

Thank you very much for replying to my message. I have an IT degree
and a theoretical knowledge of relational databases but struggle with
Access sometimes.


My tables are:

Officers
OfficersID (PK)
OfficersName

OfficersAllocatedto
OfficerAlloc(PK)
SWBPID
OfficersID

Group
SWBPID (PK)
Name
DateCreated
Purpose
CommitteID
UpdateNotes1
UpdateNotes2
Status
EndDate

Committee
CommitteeID (PK)
Committee

Members
MembersID (PK)
MembersName

MembersAllocatedto
MemberAlloc(PK)
SWBPID
MembersID

My relationships are:

Officers (one) - (many) OfficersAllocatedto
OfficersAllocatedto (many) - (one) Group
Group (one) - (many) MembersAllocatedto
MembersAllocatedto (many) - (one) Members
Group (many) - (one) Committee No problems

Forms are:

The main form is linked to a query on the Group table. I used the
subform wizard to link the OfficersAllocatedto and MembersAllocated to
the main form via the Group table primary key SBWPID . The subforms
display the appropriate member and officer data for the records, which
have already been added. I have tried linking all the tables in one
query but that doesn't work either.

Although I get an error message once I okay it the record is added to
the subform but I can't expect other people to use the database with
the error message popping up each time a record is added. The error
message I get when I try to add a new record to my subform is:

Can't add record(s); join key of table 'MembersAllocatedto' not in
recordset.

I have the combo boxes bound to the appropriate fields on the officer
and member tables and they display the correct list of names from
which I want the users make a selection. I realised the limit-to-list
would restrict the display list but thought it would also stop
duplicate records.


I hope I haven't overloaded you with information and that you can
help.

Regards
Lesley
 
Back
Top