A query as an editable recordsource?

mohobrien

Registered User.
Local time
Today, 03:19
Joined
Dec 28, 2003
Messages
58
After crawling through the labyrinth of subforms, synchonization, querydefs (three levels of them) to arrive at the promising land of a functional database with multiple subforms complete with cascade update and delete and even an on error or two, I ventured further and struck out for the promised land of querying with subforms included. Ahh! The joy when even that met with success. So now maybe you appreciate my agony when I find that while I can edit the query result in the subforms without a problem, the main form I cannot edit. Non updateable the horrible little message says. :(
Obviously because the record source of the subforms hasn't changed, just the main form. Is there a way to edit a table from a query result displayed in the main form or is that like putting toothpaste back in the tube? Are queries one way streets? Any suggestions would be more than welcome.
The code doesn't show anything except the method used to open the form.
Code:
 'change recordsource
    Forms!ALLDISP.RecordSource = "qrytmp3"
    DoCmd.OpenForm strParentForm, acNormal, , , acFormPropertySettings
 
You'll need to post the query. The most common causes of a non-updatable query are an aggregate function or incorrect join. There are others though so you might need to read the kb article on the topic.
 
Code:
strSelectByCriteria = "SELECT ALLDISP.*, tblHolders.*, tblPreviousHolders.*, tblSubmission.* " & _
    "FROM ((ALLDISP LEFT JOIN tblHolders ON ALLDISP.[Disposition Number] = tblHolders.DispositionNumber) " & _
    "LEFT JOIN tblPreviousHolders ON ALLDISP.[Disposition Number] = tblPreviousHolders.DispositionNumber) " & _
    "LEFT JOIN tblSubmission ON ALLDISP.[Disposition Number] = tblSubmission.DispositionNumber " & _
    "WHERE " & varSQL & ""
    
    strSelectByKey = "SELECT [Disposition Number]FROM qrytmp GROUP BY [Disposition Number]"
    
    strFinalSelect = "SELECT ALLDISP.* " & _
    "FROM qrytmp2 " & _
    "INNER JOIN ALLDISP ON qrytmp2.[Disposition Number] = ALLDISP.[Disposition Number]"
and this being a typical varSQL
Code:
"WHERE (((ALLDISP.[Disposition Number]) Like "ml*") AND ((tblHolders.[Holder]) Like "cam*"))"
and then
Code:
Set qdfSelectByCriteria = db.CreateQueryDef("qrytmp")   ' make the query object to bring in AllInfo
    With qdfSelectByCriteria
            .SQL = strSelectByCriteria
    End With
    
    Set qdfSelectByKey = db.CreateQueryDef("qrytmp2")
    With qdfSelectByKey
            .SQL = strSelectByKey
    End With
    Set qdfFinalSelect = db.CreateQueryDef("qrytmp3")
    With qdfFinalSelect
            .SQL = strFinalSelect
    End With
    
    'Get the name of the Parent form
    strParentForm = Left(CStr(frm.Name), Len(CStr(frm.Name)) - 4)
    'change recordsource
    Forms!ALLDISP.RecordSource = "qrytmp3"
    DoCmd.OpenForm strParentForm, acNormal, , , acFormPropertySettings
I'm pretty sure my problem is because of the joins.
What I might have to do is put everything except the key field, which I should never have to edit anyway, in subforms. Then everything should be updateable. Just a pain redesigning the forms.
 
Last edited:
"Group By" aggregates the data. Queries with aggregated data are not updatable. The underlying record identification is lost when the data is aggregated so there is no way to identify which row should be updated.

Just because two tables have a many-to-one relationship with a third doesn't mean that you can create a meaningful recordset that combines all the data. You need a separate subform to hold each set of many-side data.
 
Thanks Pat for the confirmation. Any thoughts on my idea of putting everything but the key field in subforms. The main form would have just my key field or am I chasing my tail?
 
Put whatever comes from the one-side table on the main form. Put the fields from the many-side tables in separate subforms.
 
Encouragement :D
Now I'll really try!

Success! Fully searchable and updateable query by form with multiple subforms.:D :D Thank you Pat.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom