Distinct query makes combined query none updatable 1

[Moss100]

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.

 

[dhookom]

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

 

[combo]

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

combo

 

[Moss100]

Hello - thanks for your input.

The SQL is

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

 

[dhookom]

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

 

[Moss100]

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

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

 

[dhookom]

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

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

 

[Moss100]

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]

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

 

[dhookom]

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:

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