Edit query

[Richard]

Hi

Is there a way to edit a temp table linked to a totals query?

The form for the temp table is a subform and is linked to the totals query
to lookup information. It won't let me insert data to the temptable as it is
not updatable. Is there a way to make the form updateable or is there
another simpler way?

Thanks in advance
Richard

--

 

[John Vinson]

Hi

Is there a way to edit a temp table linked to a totals query?

The form for the temp table is a subform and is linked to the totals query
to lookup information. It won't let me insert data to the temptable as it is
not updatable. Is there a way to make the form updateable or is there
another simpler way?

If you have a Query including a Totals operation, in any way, it will
not be updateable. Can you base the Subform just on the temp table,
using the Master/Child Link Field properties to join it to the totals
query on the mainform? How does the Totals query relate to the temp
table?

 

[Richard]

Hi John

Thanks for responding.

This is for a payment form where I would like to list out unpaid invoices of
the particular company and the user is able to tick off the specific
invoices. The ticked records of the temp table will then be appended to the
payment details table and the data of the temp table deleted.

This is to facilitate part payment and the totals query is for the invoice
payment.

Is there an easier way?

Thanks in advance
Richard

 

[John Vinson]

Hi John

Thanks for responding.

This is for a payment form where I would like to list out unpaid invoices of
the particular company and the user is able to tick off the specific
invoices. The ticked records of the temp table will then be appended to the
payment details table and the data of the temp table deleted.

This is to facilitate part payment and the totals query is for the invoice
payment.

Is there an easier way?

Thanks in advance
Richard

I'm not sure since I don't know the structure of your tables. Would
you be willing to post the SQL of the query? I don't see what's being
totalled here, for instance!

 

[Richard]

Hi John

The query is as follows....

Right now, I have changed the QryPaymentTotals to a create table query
enabling this query to be updatable. But I think it will not be practical
when there a lot of records.

Here's the code for the query:


SELECT tblPaymentDetailsTemp.PaymentCheck,
tblPaymentDetailsTemp.PaymentAmount, tblPaymentDetailsTemp.PayId,
tblPaymentDetailsTemp.EnPaId, Enrolments.CompanyId, Enrolments.WORKSHOPID,
Enrolments.WORKSHOPDATE, Enrolments.FEES, partname([firstname],[lastname])
AS PaName, [Enrolment/Participant].EnPaId, [fees]-Nz([sumofpaymentamount],0)
AS Amountdue
FROM (Enrolments INNER JOIN (([Enrolment/Participant] INNER JOIN WBdata ON
[Enrolment/Participant].ParticipantID = WBdata.ID) LEFT JOIN
tblPaymentDetailsTemp ON [Enrolment/Participant].EnPaId =
tblPaymentDetailsTemp.EnPaId) ON Enrolments.EnrolmentID =
[Enrolment/Participant].EnrolmentID) LEFT JOIN QryPaymentTotals ON
[Enrolment/Participant].EnPaId = QryPaymentTotals.EnPaId
WHERE (((Enrolments.CompanyId)=[forms]![payment]![customerid]) AND
((Enrolments.FEES) Is Not Null) AND
(([fees]-Nz([sumofpaymentamount],0))<>0))
WITH OWNERACCESS OPTION;

-- Thanks

Richard

 

[John Vinson]

Hi John

The query is as follows....

Right now, I have changed the QryPaymentTotals to a create table query
enabling this query to be updatable. But I think it will not be practical
when there a lot of records.

It looks like the only field you're totalling is sumofpaymentamounts -
you might want to consider using the DSum() function directly on the
payments table as a calculated field in your query. This can be a bit
slow but certainly not as slow as a make-table query; and it doesn't
interfere with updatability!

 

[Richard]

Hi John

Thanks for the tip. How would I criteria the function?

Richard

 

[Richard]

Hi John

I need the EnPaId as criteria for the query to work.

Richard

 

[John Vinson]

Hi John

I need the EnPaId as criteria for the query to work.

Richard

DSum("[field to sum]", "[tablename]", "[EnPaId] = " & [EnPaId])

 

[Richard]

Hi John

Thanks again. The criteria works but when there's no amount, the "#error"
appears. How do I get rid of it?

Richard

--

Hi John

I need the EnPaId as criteria for the query to work.

Richard

DSum("[field to sum]", "[tablename]", "[EnPaId] = " & [EnPaId])

 

[Richard]

Hi John

It's working perfectly. Thanks a million

Richard

--

Hi John

I need the EnPaId as criteria for the query to work.

Richard

DSum("[field to sum]", "[tablename]", "[EnPaId] = " & [EnPaId])