Recordset is not updateable after adding a query

B

Bob Vance

After I added the query "qryOverdueAll" to my query, which gave a balance to
each owner I can not change the text box "tblOwnerInfo.Status" I am getting
this error "The Recordset is not updateable" Is there another way around
this or do I have to delete "qryOverdueAll" from my query....Thanks Bob


SELECT tblOwnerInfo.Status, tblOwnerInfo.BachInvoice,
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName])
& ", " &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle]) AS
Name, tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerTitle,
tblOwnerInfo.OwnerID, tblOwnerInfo.Email, tblOwnerInfo.EmailDate,
tblOwnerInfo.EmailDateState, qryOverdueAll.Payable
FROM tblOwnerInfo INNER JOIN qryOverdueAll ON tblOwnerInfo.OwnerID =
qryOverdueAll.OwnerID
ORDER BY tblOwnerInfo.Status,
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName])
& ", " &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle]);
 
J

John W. Vinson

After I added the query "qryOverdueAll" to my query, which gave a balance to
each owner I can not change the text box "tblOwnerInfo.Status" I am getting
this error "The Recordset is not updateable" Is there another way around
this or do I have to delete "qryOverdueAll" from my query....Thanks Bob


SELECT tblOwnerInfo.Status, tblOwnerInfo.BachInvoice,
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName])
& ", " &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle]) AS
Name, tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerTitle,
tblOwnerInfo.OwnerID, tblOwnerInfo.Email, tblOwnerInfo.EmailDate,
tblOwnerInfo.EmailDateState, qryOverdueAll.Payable
FROM tblOwnerInfo INNER JOIN qryOverdueAll ON tblOwnerInfo.OwnerID =
qryOverdueAll.OwnerID
ORDER BY tblOwnerInfo.Status,
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName])
& ", " &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle]);
Click to expand...

Depends. Is qryOverdueAll updateable? If it's a totals query (with a Group By,
Sum, Count etc. in it) then it will not, and neither will this query.
 
B

Bob Vance

John W. Vinson said:
After I added the query "qryOverdueAll" to my query, which gave a balance
to
each owner I can not change the text box "tblOwnerInfo.Status" I am
getting
this error "The Recordset is not updateable" Is there another way around
this or do I have to delete "qryOverdueAll" from my query....Thanks Bob


SELECT tblOwnerInfo.Status, tblOwnerInfo.BachInvoice,
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName])
& ", " &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle]) AS
Name, tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerTitle,
tblOwnerInfo.OwnerID, tblOwnerInfo.Email, tblOwnerInfo.EmailDate,
tblOwnerInfo.EmailDateState, qryOverdueAll.Payable
FROM tblOwnerInfo INNER JOIN qryOverdueAll ON tblOwnerInfo.OwnerID =
qryOverdueAll.OwnerID
ORDER BY tblOwnerInfo.Status,
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName])
& ", " &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle]);
Click to expand...

Depends. Is qryOverdueAll updateable? If it's a totals query (with a Group
By,
Sum, Count etc. in it) then it will not, and neither will this query.
Click to expand...
Thanks John, This is the query and I suppose the "Payable" field is the sum
Regards Bob

SELECT tblOwnerInfo.OwnerID,
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ' ')
& IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName) AS
Name,
Nz(qTotalDueForPayment.TotalDue,0)-Nz(qPaidAmountForPayment.PaidTotal,0) AS
Payable
FROM (tblOwnerInfo LEFT JOIN qPaidAmountForPayment ON tblOwnerInfo.OwnerID =
qPaidAmountForPayment.OwnerID) LEFT JOIN qTotalDueForPayment ON
tblOwnerInfo.OwnerID = qTotalDueForPayment.OwnerID
ORDER BY
IIf(IsNull(tblOwnerInfo.OwnerLastName),'',tblOwnerInfo.OwnerLastName & ' ')
& IIf(IsNull(tblOwnerInfo.OwnerFirstName),'',tblOwnerInfo.OwnerFirstName);
 
J

John W. Vinson

After I added the query "qryOverdueAll" to my query, which gave a balance to
each owner I can not change the text box "tblOwnerInfo.Status" I am getting
this error "The Recordset is not updateable" Is there another way around
this or do I have to delete "qryOverdueAll" from my query....Thanks Bob


SELECT tblOwnerInfo.Status, tblOwnerInfo.BachInvoice,
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName])
& ", " &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle]) AS
Name, tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerTitle,
tblOwnerInfo.OwnerID, tblOwnerInfo.Email, tblOwnerInfo.EmailDate,
tblOwnerInfo.EmailDateState, qryOverdueAll.Payable
FROM tblOwnerInfo INNER JOIN qryOverdueAll ON tblOwnerInfo.OwnerID =
qryOverdueAll.OwnerID
ORDER BY tblOwnerInfo.Status,
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName])
& ", " &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle]);
Click to expand...

Well, you're joining one multitable query to another multitable query. It's
not at all surprising that it's not updateable. Also, most of the fields seem
to be calculated fields (your fullnames), which won't be updateable in any
case.

It really sounds like you're trying to do too many different things all with
one query! Can you maybe use a DLookUp into qryOverdueAll instead of linking
the queries, or use a Subform?
 
B

Bob Vance

John W. Vinson said:
After I added the query "qryOverdueAll" to my query, which gave a balance
to
each owner I can not change the text box "tblOwnerInfo.Status" I am
getting
this error "The Recordset is not updateable" Is there another way around
this or do I have to delete "qryOverdueAll" from my query....Thanks Bob


SELECT tblOwnerInfo.Status, tblOwnerInfo.BachInvoice,
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName])
& ", " &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle]) AS
Name, tblOwnerInfo.OwnerFirstName, tblOwnerInfo.OwnerTitle,
tblOwnerInfo.OwnerID, tblOwnerInfo.Email, tblOwnerInfo.EmailDate,
tblOwnerInfo.EmailDateState, qryOverdueAll.Payable
FROM tblOwnerInfo INNER JOIN qryOverdueAll ON tblOwnerInfo.OwnerID =
qryOverdueAll.OwnerID
ORDER BY tblOwnerInfo.Status,
IIf(IsNull([tblOwnerInfo].[OwnerLastName]),'',[tblOwnerInfo].[OwnerLastName])
& ", " &
IIf(IsNull([tblOwnerInfo].[OwnerFirstName]),'',[tblOwnerInfo].[OwnerFirstName]
& ' ') &
IIf(IsNull([tblOwnerInfo].[OwnerTitle]),'',[tblOwnerInfo].[OwnerTitle]);
Click to expand...

Well, you're joining one multitable query to another multitable query.
It's
not at all surprising that it's not updateable. Also, most of the fields
seem
to be calculated fields (your fullnames), which won't be updateable in any
case.

It really sounds like you're trying to do too many different things all
with
one query! Can you maybe use a DLookUp into qryOverdueAll instead of
linking
the queries, or use a Subform?
Click to expand...
Thanks John, but Cant really use a subForm as I am using a continuious form
How do I go about using DLookUp?
Thanks Bob
 
J

John W. Vinson

Thanks John, but Cant really use a subForm as I am using a continuious form
Click to expand...

It's possible to use correlated subforms; you can have two subforms side by
side, with the second one dependent on the first.
How do I go about using DLookUp?
Click to expand...

Include a calculated field:

Payable: DLookUp("[Payable]", "[qryOverdueAll]", "[OwnerID] = " & OwnerID)
 
B

Bob Vance

John W. Vinson said:
Thanks John, but Cant really use a subForm as I am using a continuious
form
Click to expand...

It's possible to use correlated subforms; you can have two subforms side
by
side, with the second one dependent on the first.
How do I go about using DLookUp?
Click to expand...

Include a calculated field:

Payable: DLookUp("[Payable]", "[qryOverdueAll]", "[OwnerID] = " & OwnerID)
Click to expand...

Thanks John, The DLookUp is perfect, just one problem The Format field in my
text box is not changing the format, I am trying $#,##0.00;($#,##0.00) &
Currency but will not change the number, I am getting format like this
1234.1234, Thanks Bob
 
B

Bob Vance

Bob Vance said:
John W. Vinson said:
Thanks John, but Cant really use a subForm as I am using a continuious
form
Click to expand...

It's possible to use correlated subforms; you can have two subforms side
by
side, with the second one dependent on the first.
How do I go about using DLookUp?
Click to expand...

Include a calculated field:

Payable: DLookUp("[Payable]", "[qryOverdueAll]", "[OwnerID] = " &
OwnerID)
Click to expand...

Thanks John, The DLookUp is perfect, just one problem The Format field in
my text box is not changing the format, I am trying $#,##0.00;($#,##0.00)
& Currency but will not change the number, I am getting format like this
1234.1234, Thanks Bob
Click to expand...
Oh Got it now entered this into my textbox and not my query
=DLookUp("[Payable]", "[qryOverdueAll]", "[OwnerID] = " & OwnerID)
Brilliant thanks John
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error when opening a my Query 5
2 DBs 1 giving a query error 1
After Update Help! 9
Recordset not updateable message displayed when form opens 1
The Recordset is Not Updateable 4
Query not updateable, yet in form it is. 1
this recordset is not updateable 1

Top