Recording on subform issue

[david.paton]

I am designing a database for the local branch of Legacy. In this DB, Widows are looked after by Legatees. At any one point, one widow may only be assigned to one legatee but every legatee can look after multiple widows. Over time Legatees can be assigned different widows so I want the legatee name that recorded the comment to remain the same and not change if the legatee looking after the widow changes.

I have a comments table that legatees can record information in, regarding their widows, such as the widow in question may be a bit deaf etc. I have the comments table inserted on the widow form as a sub form in table format. That way, anyone looking at the widow form, can see a list of the date and comment made relating to the widow.

The problem I am having is with the widow form and the comment sub form. For a start, whenever anything is entered in the Legatee Name of the comments subform and you try and go to another record, I keep getting the error message "You cannot add or change a record because a related record is required in the table 'Legatee' ". I don't know why it is giving this error message as I have the legatee on the comment sub form in the legatee table.

I was also wondering if the Legatee name should be a combo box, as I am not sure how to record the Legatee name.? I thought I have read somewhere that having a combo box as part of a table is not good database design.

Thanks,

David

 

[david.paton]

I just found the answer to the first question, I had '0' as the default value in the number fields in the comments table but I still am not sure about how to record the legatee name.

 

[static]

You don't store an ID and it's data together in 'foreign' tables.
Record the LegateeID and if you need the name look it up.

combo6

rightclick > change to > combo box

control source = CommentsLegateeID
rowsorce = select legateeid, legateename from legatee
bound column = 1
column count = 2
column widths = 0

Without changing anything else, that fixed the error when adding a new record.