Query updatable but sql not updatable. (1 Viewer)

[George-Bowyer]

I have a sub-form that users can change the records displayed by choosing from 2 3-button option groups.

I then use two nested select cases to choose an sql statement for the recordsource of the sub form.

I want users to be able to enter dates into 2 fields.

When I use the SQL in a query, the recordset is updateable, but when I use the 'same' (slightly edited for VBA use) sql as RecordSource in the form it is non-updateable.

Can some one let me know why, please?

strSQL = "SELECT tblAnnualReturn.fldClubID, tblOrganisations.FldOrgName, tblAnnualReturn.fldDateRecieved, tblAnnualReturn.fldDateProcessed, tblAnnualReturn.fldYear" & _
                    " FROM (tblAnnualReturn INNER JOIN tblClubAssociations ON tblAnnualReturn.fldClubID = tblClubAssociations.fldClubOrgID) INNER JOIN tblOrganisations ON tblClubAssociations.fldClubOrgID = tblOrganisations.OrganisationID" & _
                    " WHERE (((tblAnnualReturn.fldDateRecieved) Is Null) And ((tblAnnualReturn.fldYear) = DatePart('yyyy', Date())) And ((tblClubAssociations.fldAssocOrgID) = 307))" & _
                    " ORDER BY tblOrganisations.FldOrgName;"

Thanks,

George

 

[Ranman256]

The query can be non-updateable depending on the joins.
or
the subform wont update because of the link to key in the master form.

in the query only, view the query results and see if you can edit/add. If so then the query is fine,
if not, then you must alter the joins.
Usu. a subform wont need lots of tables/joins because its bound to the master record.

 

[George-Bowyer]

Hi. Thanks for the reply.

Yes, I can edit the query in query view. If I save the query as recordsource in the subform, I can edit the sub-form.

But when I use VBA to change the RecordSource to the SQL statement it is non-updateable.

The main form is unbound and the sub-form is not bound to the main form. There's just 2 option groups that allow users to refine what they want to see.

 

[George-Bowyer]

Hmm. I've worked out that I have the query saved as "Dynaset - inconsistent updates" and I'm not sure whether or how you can specify that in VBA

 

[George-Bowyer]

I've changed tack and solved the problem a different way, by applying filters, but I'd still like to know if/how you can use inconsistent update dynasets via VBA and SQL?

 

[theDBguy]

I've changed tack and solved the problem a different way, by applying filters, but I'd still like to know if/how you can use inconsistent update dynasets via VBA and SQL?

Hi. I'm sure there is a way, but I never bothered to find out how because I was always taught you wouldn't want to use them anyway because there's always a better way to accomplish the objective like your use of filters, for example.