Why won't my subform update work??

[tractorvix]

I have 4 tables set up
Sales:
SaleID
YearID
MonthID
ProdID
SaleValue

Year:
YearID
Year

Month:
MOnthID
LongMonth
ShortMonth

Prod:
ProdID
ProdName

and have a subform set up with it's source as:
SELECT [tbl-Year].Year, [tbl-Month].shortMth, [tbl-Sales].SaleValue, [tbl-Sales].ProdID, [tbl-Month].MthID FROM [tbl-Year] RIGHT JOIN ([tbl-Month] RIGHT JOIN [tbl-Sales] ON [tbl-Month].MthID=[tbl-Sales].MonthID) ON [tbl-Year].YearID=[tbl-Sales].YearID;

I want to update the Sales table using the subform, which works great for the Year, ProdID and SaleValue, but for some reason it's not updating the MonthID field. I suspect it's something to do with me only having 2 fields for the majority of the tables and 3 for the Month. I've set up relationships etc but it just doesn't seem to be working!

any help please?

Vicky

 

[MajP]

Unless there is a speed issue, that seems overly complicated.
A table for months and years?
You should have a single value in Sales:
"SalesDate" and it should be a date.
You then can use formatting in your queries, forms and reports to show the month and year.
1/1/2008

Format(dtmSales, "mmm"): Jan
Format(dtmSales, "mmmm"): January
Format(dtmSales, "yyyy"): 2008
Format(dtmSales, "yy"): 08

Your argument is probably that you never need a day. That is OK you never show it or require a user to enter a day. Normally this is done with a combination of comboboxes and the use of a dateserial function.