New record saving on top of old record

[borisbruno]

I'm not sure if this is a table or a form question. What I'd like to have happen is that a new record will be saved for each recipient by year with the dates of service for each month of that year.

Table design:
DOSInd (PK) Auto#
RecipID (FK) Number
Year Text
Jan Text
Feb Text
.
.
.
Dec Text

These fields appear on a subform as textboxes with the corresponding labels. The problem is that I can't fiqure out how to force a new record to be saved when the year is changed. What is happening is that the newly entered year and monthly dates are saving over the previously saved data.

I've tried several arrangements for primary keys involving the first three fields, but that's not fixing the problem. I've tried an OnChange event for the year control in the form, but I don't think I know the right syntax for doing what it is I want.

Any suggestions would be appreciated, even if that means redesigning table or form. I went this route because of user request. They wanted the main form to display the dates of service for a recipient for the year, and allow for additions as the months go by.

Thank you, Charlotte

 

[borisbruno]

Thanks for your quick response.

Pat,

I'm not sure I clearly understand what I need to do. The table should only have 2 fields with both fields being combined as the primary key? Is that correct?

"... - use 1 as the day if all you are interested in is month and year ..."
I'm sorry; I don't understand this part.

Structuring the table this way, does that mean that each month a recipient gets food, a new record will be added to the table?

Thanks, Charlotte

 

[borisbruno]

Pat,

Thanks for clearing that up for me. This project is for a food bank; they need to keep track of the food baskets distributed per month and to which households. The date isn't even that important, except to make sure that a particular household hasn't received more than one food basket per month.

Thank you. Charlotte

 

[borisbruno]

Pat,

Thanks for your help and for showing me that I need to look at my data from a table point of view rather than from the form backwards. I was more focused on getting the data to present in a certain arrangement in the form than concentrating on sound table structure.

Charlotte