Updating values on query based form

Hi, in the past I've spent ages writing VB routines to get around this
problem, is there a simple way?
>
I have three tables for a membership system:
>
tblMembership - MembershipNumber; Names etc
tblMembershipSubsHeader - "AmountDue" [their annual subs rate]; whether
the pay by standing order and so on
tblMembershipSubs - PaymentYear [auto created for current year];
DatePaid; AmountPaid
>
I list all of these on a form, the record source is a query linking all
tables [the relationship is already set up]
>
BUT I cannot update the records in the form, I need to update DatePaid
and AmountPaid.
>
The only condition set in the underlying query is for the PaymentYear
[DatePart("yyyy",Date())]
>
I could write some VBA to update these two fields via sql, however I'm
aware that this will not be visible on the form until it is refreshed,
the problem then is that if I do this via code it will go to the
top..... I'd then need to use more to go back to the current record....
!!! Very messy - is there an alternative??
>
Thank you in anticipation
>
Jon
>

you can remove tblMembershipSubs from the query that underlies the form. add
a subform to the form, and bind the subform to tblMembershipSubs; you can
update the subform records via direct data entry.
>
hth
>
>
"J-P-W" <jonpwebb@gmail.comwrote in message
news:1169307506.985073.108110@38g2000cwa.googlegro ups.com...

Thanks Tina, Unfortunately I have the form as continuous - so no sub
forms! Continous is best so the entire membership can be looked through
quickl... any other thoughts?
>
Thanks
>
Jon
>
tina wrote:

>

Thanks Tina, Unfortunately I have the form as continuous - so no sub
forms! Continous is best so the entire membership can be looked through
quickl... any other thoughts?
>
Thanks
>
Jon
>
tina wrote:

This is the whole query:
>
SELECT tblMemberSubs.Year, tblMembers.MemberNumber, tblMembers.Surname,
tblMembers.Forenames, tblMemberSubsHeader.TotalDue,
tblMemberSubsHeader.StandingOrder, tblMemberSubs.DatePaid,
tblMemberSubs.Amount
FROM tblMemberSubsHeader INNER JOIN (tblMemberSubs INNER JOIN
tblMembers ON tblMemberSubs.MemberNumber = tblMembers.MemberNumber) ON
tblMemberSubsHeader.MemberNumber = tblMembers.MemberNumber
WHERE (((tblMemberSubs.Year)=DatePart("yyyy",Date())))
ORDER BY tblMembers.MemberNumber;
>
Thanks
>
Jon
>
J-P-W wrote:

>

okay, well, that didn't answer the relationships question. so i'll assume
that tblMembers has a one-to-one relationship with tblMemberSubsHeader, and
tblMembers has a one-to-many relationship with tblMemberSubs.
>
if the above is correct, try changing the JOINs in your SQL statement, as
>
SELECT SELECT tblMemberSubs.Year, tblMembers.MemberNumber,
tblMembers.Surname,
tblMembers.Forenames, tblMemberSubsHeader.TotalDue,
tblMemberSubsHeader.StandingOrder, tblMemberSubs.DatePaid,
tblMemberSubs.Amount
FROM (tblMemberSubsHeader LEFT JOIN tblMembers ON
tblMemberSubsHeader.MemberNumber = tblMembers.MemberNumber) LEFT JOIN
tblMemberSubs ON tblMembers.MemberNumber = tblMemberSubs.MemberNumber
WHERE tblMemberSubs.Year=Year(Date())
ORDER BY tblMembers.MemberNumber;
>
note that if tblMembers has a one-to-many relationship with *both* child
tables, the above query is still not updateable. also, Year is a reserved
word in Access; recommend you change the fieldname from Year to something
else, like PmtYear.
>
hth
>
>
"J-P-W" <jonpwebb@gmail.comwrote in message
news:1169325100.862658.66190@l53g2000cwa.googlegro ups.com...form. addcanthiswhetherallDatePaidPaymentYearI'mrefreshed,record....

Sorry I didn't specify the relationships, but yes you got that right.
>
Thank you for your help if the records remains not updateable then I
guess I'll need to write that code after all!!
>
Regards
>
Jon
>
>
>
tina wrote:

>