473,287 Members | 1,813 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

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

Jan 20 '07 #1
7 2228
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" <jo******@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...
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

Jan 20 '07 #2
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:
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" <jo******@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...
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
Jan 20 '07 #3
what are the specific table relationships?
"J-P-W" <jo******@gmail.comwrote in message
news:11*********************@11g2000cwr.googlegrou ps.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:
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" <jo******@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...
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
>

Jan 20 '07 #4
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:
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:
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" <jo******@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...
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
>
Jan 20 '07 #5
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" <jo******@gmail.comwrote in message
news:11*********************@l53g2000cwa.googlegro ups.com...
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:
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:
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" <jo******@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...
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

Jan 21 '07 #6
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:
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" <jo******@gmail.comwrote in message
news:11*********************@l53g2000cwa.googlegro ups.com...
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:
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:
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" <jo******@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...
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
>
Jan 21 '07 #7
you're welcome, and good luck with it. :)
"J-P-W" <jo******@gmail.comwrote in message
news:11**********************@v45g2000cwv.googlegr oups.com...
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:
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" <jo******@gmail.comwrote in message
news:11*********************@l53g2000cwa.googlegro ups.com...
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:
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:
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" <jo******@gmail.comwrote in message
news:11**********************@38g2000cwa.googlegro ups.com...
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

>

Jan 22 '07 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Jason | last post by:
Let's say I have an html form with 20 or 30 fields in it. The form submits the fields via POST to a php page which updates a table in a database with the $_POST vars. Which makes more sense? ...
3
by: Ryan.Chowdhury | last post by:
This is a general question regarding the use of view and stored procedures. I'm fairly new to databases and SQL. I've created a SQL database using an Access Data Project ("ADP") and I'm...
1
by: Derek Davlut | last post by:
I have a Table that contains data that I use in a query to manipulte the data through expressions. I have a form that uses the query for manipulating the data. How do I write the changed values...
4
by: Darrel | last post by:
I'm creating a table that contains multiple records pulled out of the database. I'm building the table myself and passing it to the page since the table needs to be fairly customized (ie, a...
0
by: rj | last post by:
Hi All, I have used the data form wizard in vb.net 2002 to create a form based on a query in Access. It works just fine until i try to update a value for a field. It gives me an error and doesnt...
4
by: Haas C | last post by:
Hi all, I was wondering if any of you guys can help me out with this: I have two fields on a form: one field is Premium and the other is Brokerage. Whatever amount is in the Premium field is...
1
by: Gumbyu | last post by:
Hi. I am a newbie and am trying to build a form for a daily dietary menu. The problem I am having is that the form, which is based on a query to the primaryfood table does not update all the...
5
stepterr
by: stepterr | last post by:
I have a form that is built based on a query. Everything is working except when I submit the form the radio buttons are only updating the first row in my database. dcategory and dthumbnail are two...
7
by: sparks | last post by:
I am working on a database that has a lot of calculated values on the forms. These were never put into the tables. But were tied to unbound fields on the forms. Now 8000 records later they want...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.