joins and subforms problem (I think)

[Ravensleach]

I have qryHistory which joins two tables: tblMembers and tblHistory; the relationship between the tables
is one to many as each member can have more than one item of history.

SELECT DISTINCTROW tblHistory.HistoryID, tblMembers.MemberID, tblHistory.MemberID, [Title] & " " & [Initials] & " " & [Surname] AS AddressName, tblMembers.HandbookEntry, tblHistory.History, tblHistory.StartDate, tblHistory.EndDate
FROM tblMembers INNER JOIN tblHistory ON tblMembers.MemberID = tblHistory.MemberID
ORDER BY tblHistory.StartDate;

the problem is when we try to add a new history for a member who doesn't already have one.
I need to have the tblMembers.MemberID written to the tblHistory.MemberID field. (We are using a subform based on tblHistory with three fields: StartDate, EndDate and History, within a form which includes both MemberID fields and the HistoryID.

Can anyone help? Thanks.

 

[dhookom]

Why do you have any history records in the main form? I would expect the main form to be based on the members and the subform to be based on the history.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Ravensleach]

The form is based on a query which joins tblMembers and tblHistory, and the subform on tblHistory.

 

[dhookom]

Why do you need tblHistory in your main form's record source? Normally tblHistory would not be used in the main form.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]