Hi Lori,
You probably aren't looking at this anymore, but I'll take a shot anyway. I
scanned the thread again, but I may have missed you responding to this
before, but it strikes me as just the way things work so I feel like it has
to be right
I think Dirk was on it from the start. Generally, you don't create a query
that includes both the main form table and subform table in order to use a
subform in a main form. You would normally only create a query like that if
you wanted to have all the controls from BOTH the tables (main and the one
behind the subform) in the SAME form (read: one main form, no subform).
That MIGHT be used if you have a one-to-one relationship between two tables...
If I had to speculate, I'd say that because you've included the subform data
in the recordsource behind your main form, when you move to a subform the
main form record doesn't save because it thinks it's still in the same
record. I COULD BE WRONG HERE, and it wouldn't change the rest of my post....
So, I would definitely try NOT using that query, and just use the link
master/child field settings of the subforms.
I think you could test this 'theory' by just changing the recordsource in
the main form to the main table instead of the query you've created with the
main table and all the tables behind the subforms. You can always change it
back if it doesn't work. If your query includes some additional info behind
controls in the MAIN FORM, then those controls will not work after you make
the change....but you're just testing anyway. If it works and you DO have
info from more than just the main table, then you can create a query to use
for the main form's recordsource that will include the main table and any
other info (calculated or from tables, etc) that you need IN YOUR MAIN FORM.
Again, you don't want to include the tables behind the subforms.
Hope that helps, if you check back....
CW
Lori said:
Sorry, been out of town, appreciate the responses.
Dirk in response to your questions:
Could you clarify for me the relationships among the relevant tables? The
idea of the form is to put things in place so that an Approval Letter (as a
report) can be generated as well as several other different reports. The
subforms are there to fill in the necessary information required by the
letter, ie. special notes, attachments, CCs etc.
Is there a Projects table lurking in the background? There is a projects
table in the database that is incorporated into the Approval Letter Query.
The query pulls the project name, number, address and basic owner information
for use in the reports.
Normally I'd expect a table named [Approval Letters] to be on the "one" side
of a
one-to-many relationship with a table named [Approval Letter Details]. Is
that the case here? THIS IS THE CASE HERE....the form pulls its
information from the Approval letter table and the first subform pulls from
the Details table. It was necessary to separate these so that each approval
letter could have more than one entry if necessary.
What are the primary keys of these tables, and what fields in each table are
foreign keys to another table? the ALID is the primary key in the Approval
Letter table with this value also included on the details table. the only two
fields that are identical on both tables is the ALID and the AL#.
Would you mind explaining, in simple terms, what your main form is supposed
to show, and what the subforms are supposed to show? I suspect that you may
be trying to show a record on the subform that hasn't even been saved yet,
and I wonder if your main form and subform(s) aren't inverted.
The main form is supposed to show 1) project the project number, the AL
Date, status (i.e. approved, pending, void) and the AL #. The first subform
(the primary subform) shows the "Details", i.e. which subcontractor, change
value, subcontract # and the detailed description of the change. This was
created as a subform so that multiple entries could be included for each AL
if necessary.
I don't think the forms are inverts since switching them around would
eliminate my multiple entries for the ALs when necessary.
any help is appreciated.
Thanks
Lori