How do I fix this?

[Guest]

Okay, I've got a form that has several subforms. Each of the forms uses two
common fields, ALID and AL#, I have the forms linked thru these fields in my
query and then generated the forms from there. I set up separate queries
between the main form and the subforms.

Here'are my problems. When I enter in a new "letter" the ALID Field (which
is autonumber on the main table) does not populate into the other tables,
and, my subforms will not allow me to enter any data into them until I close
the form and reopen it (if it shows the new entry at all.)

HELP!

 

[Dirk Goldgar]

In

Okay, I've got a form that has several subforms. Each of the forms
uses two common fields, ALID and AL#, I have the forms linked thru
these fields in my query and then generated the forms from there. I
set up separate queries between the main form and the subforms.

Here'are my problems. When I enter in a new "letter" the ALID Field
(which is autonumber on the main table) does not populate into the
other tables, and, my subforms will not allow me to enter any data
into them until I close the form and reopen it (if it shows the new
entry at all.)

HELP!

I'm not sure I understand your description of the setup, but I suspect
that your subforms are based on queries that involve both the main
form's recordsource table and the "child" table that is the subject of
each subform. That's not normally what you would want to do. Please
post the recordsource properties of the main form and the subforms, and
also the name and Link Master/Child Fields of each subform.

 

[Guest]

Here is the SQL view of the main query that both the main form and one of my
subforms is based on:

The Query is called Approval Letter.

SELECT [Approval Letter Details].ALID, [Approval Letters].[Project#],
[Approval Letter Details].[AL#], [Approval Letter Details].Subjob, [Approval
Letters].[Date Submitted], [Approval Letters].Agreement, [Approval
Letters].BriefDescription, [Approval Letters].Status, [Approval
Letters].[Signed by Owner], [Approval Letters].ALType, [Approval
Letters].Typeofchange, [Approval Letters].[Cost for Bond], [Approval Letter
Details].Budget, [Approval Letters].[Updated By:], [Approval Letters].[Date
Updated], [Approval Letter Details].Subcontractor, [Approval Letter
Details].[Subcontract #], [Approval Letter Details].[CO#], [Approval Letter
Details].Amount, [Approval Letter Details].[Cost Code], [Approval Letter
Details].[CI#], [Approval Letters].[Sordoni Signature], [Approval Letter
Details].[Detailed Description], [Approval Letter Details].IsCI
FROM [Approval Letter Details] INNER JOIN [Approval Letters] ON ([Approval
Letter Details].ALID = [Approval Letters].ALID) AND ([Approval Letter
Details].[AL#] = [Approval Letters].[AL#])
WHERE ((([Approval Letters].[Project#])=[Forms]![Approval Letter
Filter]![Project]))
ORDER BY [Approval Letters].[Project#], [Approval Letter Details].[AL#];

Note that I am using a form as a filter to enter in the Project# since it is
on all of the subforms and Access will ask me several times for the Project #
if I don't.

This is the SQL View from the first Subform all others are set up the same
way.

SELECT [Approval Letter].Subjob, [Approval Letter].Amount, [Approval
Letter].[Cost Code], [Approval Letter].[Detailed Description], [Approval
Letter].Subcontractor, [Approval Letter].[CO#], [Approval
Letter].[Subcontract #], [Approval Letter].ALID
FROM [Approval Letter];

 

[Dirk Goldgar]

In

Here is the SQL view of the main query that both the main form and
one of my subforms is based on:

The Query is called Approval Letter.

SELECT [Approval Letter Details].ALID, [Approval Letters].[Project#],
[Approval Letter Details].[AL#], [Approval Letter Details].Subjob,
[Approval Letters].[Date Submitted], [Approval Letters].Agreement,
[Approval Letters].BriefDescription, [Approval Letters].Status,
[Approval Letters].[Signed by Owner], [Approval Letters].ALType,
[Approval Letters].Typeofchange, [Approval Letters].[Cost for Bond],
[Approval Letter Details].Budget, [Approval Letters].[Updated By:],
[Approval Letters].[Date Updated], [Approval Letter
Details].Subcontractor, [Approval Letter Details].[Subcontract #],
[Approval Letter Details].[CO#], [Approval Letter Details].Amount,
[Approval Letter Details].[Cost Code], [Approval Letter
Details].[CI#], [Approval Letters].[Sordoni Signature], [Approval
Letter Details].[Detailed Description], [Approval Letter
Details].IsCI
FROM [Approval Letter Details] INNER JOIN [Approval Letters] ON
([Approval Letter Details].ALID = [Approval Letters].ALID) AND
([Approval Letter Details].[AL#] = [Approval Letters].[AL#])
WHERE ((([Approval Letters].[Project#])=[Forms]![Approval Letter
Filter]![Project]))
ORDER BY [Approval Letters].[Project#], [Approval Letter
Details].[AL#];

Note that I am using a form as a filter to enter in the Project#
since it is on all of the subforms and Access will ask me several
times for the Project # if I don't.

This is the SQL View from the first Subform all others are set up the
same way.

SELECT [Approval Letter].Subjob, [Approval Letter].Amount, [Approval
Letter].[Cost Code], [Approval Letter].[Detailed Description],
[Approval Letter].Subcontractor, [Approval Letter].[CO#], [Approval
Letter].[Subcontract #], [Approval Letter].ALID
FROM [Approval Letter];

Are you saying that the main form is based on that first query (the one
involving both [Approval Letter] and [Approval Letter Details]), and the
second letter is based on the second query (the one involving only
[Approval Letter])? That sounds backwards to me, but maybe I just don't
understand what you're trying to represent here.

Could you clarify for me the relationships among the relevant tables?
Is there a Projects table lurking in the background? 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? What are the primary keys of these tables, and
what fields in each table are foreign keys to another table?

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.

 

[Guest]

The Main form (Called AL Letter) is based on the query which is called
Approval Letter, the subform (Call AL Subform) is based on an SQL Query from
the query Approval Letter. So basically, The both forms are based on the same
query
--
Lori A. Pong

In

Here is the SQL view of the main query that both the main form and
one of my subforms is based on:

The Query is called Approval Letter.

SELECT [Approval Letter Details].ALID, [Approval Letters].[Project#],
[Approval Letter Details].[AL#], [Approval Letter Details].Subjob,
[Approval Letters].[Date Submitted], [Approval Letters].Agreement,
[Approval Letters].BriefDescription, [Approval Letters].Status,
[Approval Letters].[Signed by Owner], [Approval Letters].ALType,
[Approval Letters].Typeofchange, [Approval Letters].[Cost for Bond],
[Approval Letter Details].Budget, [Approval Letters].[Updated By:],
[Approval Letters].[Date Updated], [Approval Letter
Details].Subcontractor, [Approval Letter Details].[Subcontract #],
[Approval Letter Details].[CO#], [Approval Letter Details].Amount,
[Approval Letter Details].[Cost Code], [Approval Letter
Details].[CI#], [Approval Letters].[Sordoni Signature], [Approval
Letter Details].[Detailed Description], [Approval Letter
Details].IsCI
FROM [Approval Letter Details] INNER JOIN [Approval Letters] ON
([Approval Letter Details].ALID = [Approval Letters].ALID) AND
([Approval Letter Details].[AL#] = [Approval Letters].[AL#])
WHERE ((([Approval Letters].[Project#])=[Forms]![Approval Letter
Filter]![Project]))
ORDER BY [Approval Letters].[Project#], [Approval Letter
Details].[AL#];

Note that I am using a form as a filter to enter in the Project#
since it is on all of the subforms and Access will ask me several
times for the Project # if I don't.

This is the SQL View from the first Subform all others are set up the
same way.

SELECT [Approval Letter].Subjob, [Approval Letter].Amount, [Approval
Letter].[Cost Code], [Approval Letter].[Detailed Description],
[Approval Letter].Subcontractor, [Approval Letter].[CO#], [Approval
Letter].[Subcontract #], [Approval Letter].ALID
FROM [Approval Letter];

Are you saying that the main form is based on that first query (the one
involving both [Approval Letter] and [Approval Letter Details]), and the
second letter is based on the second query (the one involving only
[Approval Letter])? That sounds backwards to me, but maybe I just don't
understand what you're trying to represent here.

Could you clarify for me the relationships among the relevant tables?
Is there a Projects table lurking in the background? 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? What are the primary keys of these tables, and
what fields in each table are foreign keys to another table?

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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

Hi Lori,

You don't mention anything about the master/child linking of your subforms
to the main form. Did you do that?

Assumming AlID is the primary key of your main form's table (or the main
table in the query behind your main form), and Alid is also in each ofthe
subforms, that's probably what needs to go in the link master/child fields.

You find the properties of Link Master field and Link Child field if you
select the subform control in your main form (read: NOT the subform itself,
but the control that holds the subform).

HOpe that helps,
CW

 

[John W. Vinson]

The Main form (Called AL Letter) is based on the query which is called
Approval Letter, the subform (Call AL Subform) is based on an SQL Query from
the query Approval Letter. So basically, The both forms are based on the same
query

That's probably NOT what you want. Normally the main form would be based on
the "one" side of a one-to-many relationship, and the subform on the "many".
The only reason I can think of to have a form and subform based on the same
query would be if you want a single form to edit individual records, with a
continuous subform to see multiple records; and even here, I wouldn't expect
to do any updating in the subform.

John W. Vinson [MVP]

 

[Dirk Goldgar]

In

The Main form (Called AL Letter) is based on the query which is called
Approval Letter, the subform (Call AL Subform) is based on an SQL
Query from the query Approval Letter. So basically, The both forms
are based on the same query

That doesn't really answer my questions, Lori, although I agree with
John Vinson that you would not normally have both the main form and the
subform bound to the same query. That's probably the source of your
problem -- the subform can't show a new record entered on the main form
until that record is stored and the subform is requeried. But I need
more information to make a suggestion for how you really should set this
up. That's why I asked these questions:

Could you clarify for me the relationships among the relevant tables?
Is there a Projects table lurking in the background? 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? What are the primary keys of these
tables, and what fields in each table are foreign keys to another
table?

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.

 

[Guest]

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

 

[Guest]

The master/child links are in place using the ALID which is the only common
factor between the two. I really need help on this one.

 

[Guest]

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

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