Updating recordset property in runtime form becomes non editable

[Kalyan Ganesan]

The recordset type i have is Dynaset (Inconsistent Updates)

I am updating the recordset in runtime thru below code

Dim strSelect As String
Dim rs1 As DAO.Recordset


strSelect = "SELECT * FROM MAIN"

Set rs1 = CurrentDb.OpenRecordset(strSelect)
Set Me![subform].Recordset = rs1
Set rs1 = Nothing


it works but the controls in the subform become non editable, if i dont update the recordset in runtime and use LinkedMasterfields then the controls are editable

what property do i need to set to make the form editable

I am attaching the property sheet of the subform in the image

 

[dhookom]

If you simply open the record source as datasheet can you edit records? What is the SQL property of the query?

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

 

[Kalyan Ganesan]

Its a complex query this one..it joins two tables,is that why?

 

[dhookom]

You didn't answer either of my two questions. I think I saw your SQL from another thread but I'm not sure.

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

 

[Kalyan Ganesan]

Yes when i open the subform as a datasheet alone i am able to edit the records,did i answer your question,and the recordsource for that is a query that is set in property sheet and the query has two tables joined

 

[dhookom]

Is this the SQL of the query and if so, LOAN_NUMBER a primary key in one or both tables?

strSelect = "SELECT MAIN.*, MSP_DATA.LAST_PAYMENT_MADE, MSP_DATA.NEXT_PAYMENT_DUE_DATE, MSP_DATA.CURRENT_UPB, MSP_DATA.PROP_VALUE, MSP_DATA.BORROWER_NAME, MSP_DATA.TOTAL_MONTHLY_PAYMENT, MSP_DATA.INVESTOR_ID, MSP_DATA.PROPERTY_ALPHA_STATE_CODE, MSP_DATA.INVESTOR_NAME, MAIN.MULTI_LIEN, MAIN.SECOND_LIEN, MAIN.THIRD_LIEN"
strSelect = strSelect & " FROM MAIN LEFT JOIN MSP_DATA ON MAIN.LOAN_NUMBER = MSP_DATA.LOAN_NUMBER WHERE MAIN.GOLD_NUMBER='" & cmbGoldNumber.Text & "';"

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

 

[Kalyan Ganesan]

Yes thats the query but i have a different module in the same project which uses a similar query but that fetches way more data

this query above fetches like 500 rows

but the idea is the same

 

[dhookom]

I would create a test.

[ul]
[li]Take the query used in the subform and use it as the record source in a new form. Does it allow editing?[/li]
[li]Add this new form as a subform of another new form. Does it allow editing?[/li]
[/ul]

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