Subform not updatable after changing Recordsource

[joel009]

Access 2010 in 2007 mode.
I have a subform with bound fields that are controlled by the main form’s combo box. Most fields (except the key fields) I am able to update. My customer had me add a button to display other data in the subform and I have that working except now the recordset is not updateable.
How do I make the recordset updateable when I change the recordsource? This is what I am using:
Forms!frmBaseRouteAssignment.[RouteEfficiencyDetail].Form.RecordSource = "SELECT tblPartRouteAssignment.Route," _
& " tblPartRouteAssignment.PartNumber, tblPartRouteAssignment.Station, tblPartInfo.Description," _
& " IIf([StationBld_Rate]=0,[VerifiedBld_Rate],[StationBld_Rate]) AS Bld_Rate, tblPartInfo.PackDensity," _
& " tblPartInfo.Container, tblPartRouteAssignment.ReceivingDock, tblPartRouteAssignment.LineFeedDeliveryType, tblPartRouteAssignment.TravDistLineFeed," _
& " tblPartRouteAssignment.TravDistStaging, tblPartRouteAssignment.TravDistDock, tblPartRouteAssignment.StackHtLFDelivery," _
& " (([LineFeedHandling]/[StackHtLFDelivery])+([StagingHandling]/[StackHtReceiving])+([EmptyReturnHandling]/[StackHtReceiving])) AS HdlgPerCont," _
& " (([TravDistLineFeed]/[StackHtLFDelivery])+([TravDistStaging]/[StackHtReceiving])+([TravDistEmpty]/[StackHtReceiving])) AS TravelPerTrip," _
& " [TravelPerTrip]*0.00227 AS TravelMinPerTrip, [HdlgPerCont]+[TravelMinPerTrip] AS AllocPerCont, tblPartRouteAssignment.LFAllocation," _
& " tblPlantInfo.WorkingMinPerShift, tblPartRouteAssignment.PlantCode, tblPartRouteAssignment.DefinedInKanban" _
& " FROM (((tblPartRouteAssignment INNER JOIN tblPlantInfo ON tblPartRouteAssignment.PlantCode = tblPlantInfo.PlantCode)" _
& " INNER JOIN tblLinefeedHandling ON tblPartRouteAssignment.LineFeedDeliveryType = tblLinefeedHandling.LineFeedType)" _
& " INNER JOIN tblPartInfo ON (tblPartRouteAssignment.ModelYear = tblPartInfo.ModelYear)" _
& " AND (tblPartRouteAssignment.PlantCode = tblPartInfo.PlantCode) AND (tblPartRouteAssignment.PartNumber = tblPartInfo.PartNumber))" _
& " INNER JOIN tblPartStation ON (tblPartRouteAssignment.ModelYear = tblPartStation.ModelYear)" _
& " AND (tblPartRouteAssignment.PlantCode = tblPartStation.PlantCode)" _
& " AND (tblPartRouteAssignment.PartNumber = tblPartStation.PartNumber) AND (tblPartRouteAssignment.Station = tblPartStation.Station)" _
& " ORDER BY tblPartRouteAssignment.Route, tblPartInfo.Description;"
Then I reassign the ControlSource for 4 fields:
Forms!frmBaseRouteAssignment![RouteEfficiencyDetail].Form!txtFullStorage.ControlSource = "TravDistLineFeed"
Forms!frmBaseRouteAssignment![RouteEfficiencyDetail].Form!txtLineStaging.ControlSource = "TravDistStaging"
Forms!frmBaseRouteAssignment![RouteEfficiencyDetail].Form!txtEmptyStorage.ControlSource = "TravDistDock"
Forms!frmBaseRouteAssignment![RouteEfficiencyDetail].Form!txtDockReturn.ControlSource = "StackHtLFDelivery"
When I first go into the form the subform fields are editable but after the click event where I change the records source they are not.
How do I approach updating the key fields? Any suggestions?


Joel

 

[dhookom]

You have bunch of tables in your SQL statement. I expect the joins are not all on primary and foreign keyes joined 1 to many - 1 to many - 1 to many.

Have you tried just creating a query with the SQL to see if you can make it editable?

I'm not sure why you need so many tables in a record source. I typically have 1 or 2 tables only.

Duane
Hook'D on Access
MS Access MVP

 

[joel009]

My customer designed the form and subform and said "make these fields updatable". He used wizards to create everything and did a fine job of getting all the data to display but now I get to do the trickey stuff: Field level validation and "make these fields updatable".
I changed the query to only contain the key fields and it works!
New query:
strSQL = "SELECT tblPartRouteAssignment.Route, tblPartRouteAssignment.PartNumber, tblPartRouteAssignment.Station," _
& " tblPartInfo.Description, IIf([StationBld_Rate]=0,[VerifiedBld_Rate],[StationBld_Rate]) AS Bld_Rate, tblPartInfo.PackDensity," _
& " tblPartInfo.Container, tblPartRouteAssignment.ReceivingDock, tblPartRouteAssignment.LineFeedDeliveryType," _
& " tblPartRouteAssignment.TravDistLineFeed, tblPartRouteAssignment.TravDistStaging, tblPartRouteAssignment.TravDistDock," _
& " tblPartRouteAssignment.StackHtLFDelivery, (([LineFeedHandling]/[StackHtLFDelivery])+([StagingHandling]/[StackHtReceiving])+([EmptyReturnHandling]/[StackHtReceiving])) AS HdlgPerCont," _
& " (([TravDistLineFeed]/[StackHtLFDelivery])+([TravDistStaging]/[StackHtReceiving])+([TravDistEmpty]/[StackHtReceiving])) AS TravelPerTrip," _
& " [TravelPerTrip]*0.00227 AS TravelMinPerTrip, [HdlgPerCont]+[TravelMinPerTrip] AS AllocPerCont, tblPartRouteAssignment.LFAllocation," _
& " tblPlantInfo.WorkingMinPerShift, tblPartRouteAssignment.DefinedInKanban" _
& " FROM (((tblPartRouteAssignment INNER JOIN tblPlantInfo ON tblPartRouteAssignment.PlantCode = tblPlantInfo.PlantCode)" _
& " INNER JOIN tblLinefeedHandling ON tblPartRouteAssignment.LineFeedDeliveryType = tblLinefeedHandling.LineFeedType)" _
& " INNER JOIN tblPartInfo ON (tblPartRouteAssignment.PartNumber = tblPartInfo.PartNumber)" _
& " AND (tblPartRouteAssignment.PlantCode = tblPartInfo.PlantCode))" _
& " INNER JOIN tblPartStation ON (tblPartRouteAssignment.Station = tblPartStation.Station)" _
& " AND (tblPartRouteAssignment.PartNumber = tblPartStation.PartNumber)" _
& " AND (tblPartRouteAssignment.PlantCode = tblPartStation.PlantCode)" _
& " WHERE (((tblPartRouteAssignment.PlantCode) = " & "'" & txtPlantCode.Value & "'" & ")" _
& " And ((tblPartRouteAssignment.ModelYear) = " & "'" & txtModelYear.Value & "'" & ")" _
& " And ((tblPartInfo.ModelYear) = " & "'" & txtModelYear.Value & "'" & ")" _
& " And ((tblPartStation.ModelYear) = " & "'" & txtModelYear.Value & "'" & "))" _
& " ORDER BY tblPartRouteAssignment.Route, tblPartInfo.Description;"

Thanks for the direction.

Joel

 

[TheAceMan1]

How are ya joel009 . . .

To tell if the returned recordset is [blue]uneditable[/blue] have a look at the [blue]AddNew[/blue] navigation button

, if it has that [blue]disabled look[/blue]

then the recordset is uneditable!

If you cannot edit the data in a query, this list (courtesy by [blue]Allen Browne[/blue]) may help you identify why it is not updatable:
[ol][li]It has a [blue]GROUP BY[/blue] clause. A Totals query is always read-only.[/li]
[li]It has a [blue]TRANSFORM[/blue] clause. A Crosstab query is always read-only.[/li]
[li]It uses [blue]First(), Sum(), Max(), Count(), etc[/blue]. in the SELECT clause. Queries that aggregate records are read-only.[/li]
[li]It contains a [blue]DISTINCT[/blue] predicate. Set Unique Values to No in the query's Properties.[/li]
[li]It involves a [blue]UNION[/blue]. Union queries are always read-only.[/li]
[li]It has a [blue]subquery[/blue] in the SELECT clause. Uncheck the Show box under your subquery, or use a domain aggregation function instead.[/li]
[li]It uses [blue]JOINs of different directions on multiple tables[/blue] in the FROM clause. Remove some tables.[/li]
[li]The [blue]fields in a JOIN are not indexed correctly[/blue]: there is [blue]no primary key or unique index[/blue] on the JOINed fields.[/li]
[li]The query's [blue]Recordset Type[/blue] property is [blue]Snapshot[/blue]. Set Recordset Type to "Dynaset" in the query's Properties.[/li]
[li]The query is [blue]based on another query that is read-only[/blue] (stacked query.)[/li]
[li]Your [blue]permissions are read-only[/blue] (Access security.)[/li]
[li]The database is [blue]opened read-only[/blue], or the file attributes are read-only, or the database is on read-only media (e.g. CD-ROM, network drive without write privileges.)[/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[joel009]

Thanks AceMan1 - it seems I am always reworking somebody's mess and have 1 day to do it....lol

Joel