Hopefully Simple: How to add record new record to a subform?

[Bill Bisco]

Hello everyone. I have searched the internet and this discussion group to
try to answer my question. However, I could not find a solution. If someone
here would be so kind to help me, I'd certainly appreciate it.

I have an unbound form with 2 subforms in it. I'd like to be able to add new
records to these subforms. A picture of the form and subforms is located at
http://img167.imageshack.us/img167/2382/screenshot040.png

On the subforms I have said yes to:

1. Allow Edits
2. Allow Deletions
3. Allow Additions
4. Data Entry

However, I still cannot add new records.

The SQL of the query is slightly complicated, but basically the form opens
filtered based off of a previous form's choice, and there are 4 tables (Zone,
Station, Process, Element) linked to this query and each is a one to many
relationship.

Every Zone has many Stations; every Station has many Processes; every
process has many Elements. And, what I'm trying to let the user do is to add
more Elements to a specific Station.

I apologize for the length of this message. If anyone can help, I'd be
grateful.

Sincerely,
Bill

 

[Douglas J. Steele]

Is the query upon which the form is based updatable? If not, the form won't
be. The fact that you state "The SQL of the query is slightly complicated"
makes me suspect that this is the problem.

 

[Bill Bisco]

Dear Doug,

My query only links to Tables. However, it does use the Totals function and
groups by Sum. Could that be part of the problem?

Here is my SQL, I was trying to avoid pasting it to reduce the length of my
post, but ah well.

SELECT tblZones.ZoneSEQ, tblStations.StationSEQ, tblProcesses.ProcessSEQ,
tblStations.Side, tblProcesses.[Process Name], tblProcesses.StationID,
tblStations.[Station Name], tblZones.[Zone Name], tblProcesses.ProcessType,
tblProcesses.ProcessSubType, tblStations.LineSpeed, tblStations.StationID,
tblStations.StationSide, Sum(tblElements.ElementTime) AS SumOfElementTime,
Sum(tblElements.DSTTime) AS SumOfDSTTime,
Sum(IIf([ValueAdded?]=True,[ElementTime],0)) AS TotalValueAdded

FROM tblZones INNER JOIN (tblStations INNER JOIN (tblProcesses INNER JOIN
tblElements ON tblProcesses.[Process ID] = tblElements.[Process ID]) ON
tblStations.StationID = tblProcesses.StationID) ON tblZones.[Zone ID] =
tblStations.[Zone ID]

GROUP BY tblZones.ZoneSEQ, tblStations.StationSEQ, tblProcesses.ProcessSEQ,
tblStations.Side, tblProcesses.[Process Name], tblProcesses.StationID,
tblStations.[Station Name], tblZones.[Zone Name], tblProcesses.ProcessType,
tblProcesses.ProcessSubType, tblStations.LineSpeed, tblStations.StationID,
tblStations.StationSide

HAVING (((tblStations.Side)="LS"));

Sincerely,
Bill

 

[Douglas J. Steele]

Yup, that's the problem. How is Access supposed to update a query that
summarizes data? A single row on the form represents the summation of many
other rows: which one of those "many other rows" should be updated?

For other things that aren't allowed in updatable queries, check what Allen
Browne has at http://www.allenbrowne.com/ser-61.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dear Doug,

My query only links to Tables. However, it does use the Totals function
and
groups by Sum. Could that be part of the problem?

Here is my SQL, I was trying to avoid pasting it to reduce the length of
my
post, but ah well.

SELECT tblZones.ZoneSEQ, tblStations.StationSEQ, tblProcesses.ProcessSEQ,
tblStations.Side, tblProcesses.[Process Name], tblProcesses.StationID,
tblStations.[Station Name], tblZones.[Zone Name],
tblProcesses.ProcessType,
tblProcesses.ProcessSubType, tblStations.LineSpeed, tblStations.StationID,
tblStations.StationSide, Sum(tblElements.ElementTime) AS SumOfElementTime,
Sum(tblElements.DSTTime) AS SumOfDSTTime,
Sum(IIf([ValueAdded?]=True,[ElementTime],0)) AS TotalValueAdded

FROM tblZones INNER JOIN (tblStations INNER JOIN (tblProcesses INNER JOIN
tblElements ON tblProcesses.[Process ID] = tblElements.[Process ID]) ON
tblStations.StationID = tblProcesses.StationID) ON tblZones.[Zone ID] =
tblStations.[Zone ID]

GROUP BY tblZones.ZoneSEQ, tblStations.StationSEQ,
tblProcesses.ProcessSEQ,
tblStations.Side, tblProcesses.[Process Name], tblProcesses.StationID,
tblStations.[Station Name], tblZones.[Zone Name],
tblProcesses.ProcessType,
tblProcesses.ProcessSubType, tblStations.LineSpeed, tblStations.StationID,
tblStations.StationSide

HAVING (((tblStations.Side)="LS"));

Sincerely,
Bill

Is the query upon which the form is based updatable? If not, the form
won't
be. The fact that you state "The SQL of the query is slightly
complicated"
makes me suspect that this is the problem.