×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Distinct query makes combined query none updatable

Distinct query makes combined query none updatable

Distinct query makes combined query none updatable

(OP)
Hello.

I have a table called Invoice

This has a field LandlordRef (which is not a primary key).

I have created a query to just return unique records of LandlordRef. It works fine.

I then join this to a Property table which has a field LandlordID (again not a primary key) - the query runs, but is not updatable.

What’s the best workaround?

Basically I need to update each property that a landlord owns. Many thanks mark.

RE: Distinct query makes combined query none updatable

You are talking queries but not providing SQL statements.
Does your "query to just return unique records of LandlordRef" allow edits? What is the SQL?
Typically any joins that don't have a primary key on one side of the join are not editable. Do you have a table of unique landlords?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Distinct query makes combined query none updatable

You can find here a list of reasons that make query read-only.

combo

RE: Distinct query makes combined query none updatable

(OP)
Hello - thanks for your input.

The SQL is

CODE -->

SELECT Property.Address1, Property.Address6, Property.Archive
FROM qry_Invoice_Unpaid_Gem_Lan_Ref_Unique INNER JOIN Property ON qry_Invoice_Unpaid_Gem_Lan_Ref_Unique.Lan_Gem_Lan_Ref = Property.[Lan Ref]
WHERE (((Property.Archive)=False)); 

Regards Mark

RE: Distinct query makes combined query none updatable

Mark,
You didn't provide the SQL of the two source queries. Did you read the very good link from combo? I believe it has everything you need to know about why it doesn't work. Without more information, we can't provide a workaround.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Distinct query makes combined query none updatable

(OP)
Yes the first query just returns distict Landlords. So yes I think this is what makes the overall query none updateable.

CODE -->

SELECT DISTINCT tbl_Landlord.Lan_Gemini_Landlord_Ref
FROM tbl_Invoice INNER JOIN tbl_Landlord ON tbl_Invoice.Inv_Pman_Lan_ID_WorkAt_Link = tbl_Landlord.Lan_ID
WHERE (((tbl_Invoice.Inv_Paid)="No")); 

I guess I need some approach to look at the records that the main query returns and from this update the Property table on its own???

Thanks Mark

RE: Distinct query makes combined query none updatable

I assume you distinct query doesn't allow editing. I would try change the SQL to:

CODE --> SQL

SELECT Lan_Gemini_Landlord_Ref
FROM tbl_Landlord
WHERE Lan_ID IN (SELECT Inv_Pman_Lan_ID_WorkAt_Link FROM tbl_Invoice WHERE Inv_Paid="No") 

I am confused since the first query returns [Lan_Gemini_Landlord_Ref] and the second refers to [Lan_Gem_Lan_Ref]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Distinct query makes combined query none updatable

(OP)
OK, so I have cobbled togther the following:

1. 'qry_Gemini_DNP_Clear' contains all the property records that I want to update but can't
2. The records I want to update are in the 'Property1' table.
3. 'qry_Gemini_DNP_Clear' contains the ID field [GemPropRef]
4. Property1 table contains the ID field [Reference]

CODE -->

Dim db As DAO.Database
Dim rs As DAO.Recordset
 
   Set db = CurrentDb
   Set rs = db.OpenRecordset("qry_Gemini_DNP_Clear")
    
   If Not rs.RecordCount > 0 Then Exit Sub
 
   rs.MoveFirst
   Do Until rs.EOF
      
      CurrentDb.Execute "Update [Property1] SET [Address6] = 'jrd' WHERE [REFERENCE] = [GemPropRef]"
           
      'rs.Edit
      'rs!Address6 = ""
      'rs.Update
      rs.MoveNext
   Loop

rs.Close
Set rs = Nothing 


Although the above does NOT work,, I think it works with the line below:

CurrentDb.Execute "Update [Property1] SET [Address6] = 'DNP' WHERE [REFERENCE] = 1000"


I'm sure my code needs some amending - just can't seem to get it right today!!

Thanks Mark

RE: Distinct query makes combined query none updatable

What field in qry_Gemini_DNP_Clear contains the REFERENCE value? Is there a field [GemPropRef]? If that is the field and the value is a number then try the code below:

CODE --> vba

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL as String
   Set db = CurrentDb
   Set rs = db.OpenRecordset("qry_Gemini_DNP_Clear")
    
   If Not rs.RecordCount > 0 Then Exit Sub
 
   rs.MoveFirst
   Do Until rs.EOF
      strSQL = "Update [Property1] SET [Address6] = 'jrd' WHERE [REFERENCE] = " & rs![GemPropRef]
      debug.print strSQL 

      CurrentDb.Execute strSQL, dbFailOnError
           
      'rs.Edit
      'rs!Address6 = ""
      'rs.Update
      rs.MoveNext
   Loop

rs.Close
Set rs = Nothing 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close