Subform doesn't work once added to main form

[Rachel]

Hi,

I've been searching around looking for some help and can't find anyone
that has had quite the same problem.

I have a main form called 'Sites' and have added a subform called
'Previous Year Flows' into the main form. I do not enter data into
this subform. Instead, data on previous years is drawn through from a
table called prev yr flows into this subform. When I open the subform
on its own it draws through all the necessary data, but when it is
embedded within the main form, none of the actual data is pulled
through at all, although the linked fields, Site ID and Application
Number are drawn through, so I don't know if this means that it is
linking properly?

I just can't seem to get the subform to draw through the data properly
when it is added onto the main form. I've been going around in circles
with this and can't find a solution so if anyone can offer any hints,
I'd be grateful. I'm afraid I'm not very proficient at Access so it
could be something simple I'm missing.

 

[Steve Schapel]

Rachel,

When you say that Site ID and Application Number are "the linked
fields", do you mean that both of these fields are included in the
Record Source of both the main form and the subform? And that the Link
Master Fields and Link Child Fields properties of the subform would both
read like this:
[Site ID],[Application Number]
Is that right?

In that case, let's say you open the subform independently. In the data
shown on the subform, are there records with the same Site ID *and*
Application Number of the main form record you were looking at when you
tested with the subform embedded? Hope that makes sense...

 

[Rachel]

Hi,

Not sure what happened to the reply I sent the other day, so we'll see
if I can remember what I wrote! When I said they were the ''linked
fields'' I meant that they are the fields that I have based the
relationship between the main form, site browser and the subform, prev
yr flows on. The link master fields and link child field properties of
the subform reads:

Site Reference; Current Application (master)

Site ID; Application Number (child)

When I look at the subform independently, all the data is drawn
through from the prev yr table as well as the Site ID for that
particular record, but the application number isn't. When the subform
is embedded in the main form, none of the data on previous years is
visible but the Site ID and Application Number fields are both
completed.

I'm afraid I'm not too good at all the intricacies of Access so hope
thats answered your query

Thanks

Rachel,

When you say that Site ID and Application Number are "the linked
fields", do you mean that both of these fields are included in the
Record Source of both themainformand thesubform? And that the Link
Master Fields and Link Child Fields properties of thesubformwould both
read like this:
[Site ID],[Application Number]
Is that right?

In that case, let's say you open thesubformindependently. In the data
shown on thesubform, are there records with the same Site ID *and*
Application Number of themainformrecord you were looking at when you
tested with thesubformembedded? Hope that makes sense...

--
Steve Schapel, Microsoft Access MVP

Hi,

I've been searching around looking for some help and can't find anyone
that has had quite the same problem.

I have amainformcalled 'Sites' and have added asubformcalled
'Previous Year Flows' into themainform. I do not enter data into
thissubform. Instead, data on previous years is drawn through from a
table called prev yr flows into thissubform. When I open thesubform
on its own it draws through all the necessary data, but when it is
embedded within themainform, none of the actual data is pulled
through at all, although the linked fields, Site ID and Application
Number are drawn through, so I don't know if this means that it is
linking properly?

I just can't seem to get thesubformto draw through the data properly
when it is added onto themainform. I've been going around in circles
with this and can't find a solution so if anyone can offer any hints,
I'd be grateful. I'm afraid I'm not very proficient at Access so it
could be something simple I'm missing.- Hide quoted text -

- Show quoted text -

 

[Steve Schapel]

Rachel,

Can you please post back with the SQL view of the query that the subform
is based on?

Here's the deal:
On the parent form, there is a [Site Reference] control, and there is a
[Current Application] control. Right? So when the main form is open,
at any particular record, there will be a particular value for each of
these two controls, right? So in the subform, the *only* records that
will be shown, with your setup, are the records where the value of the
[Site ID] field matches the value of the main form's current [Site
Reference] field, *and* the value of the [Application Number] matches
the value of the main form's current [Current Application] field. It is
apparent that there are no matching subform records, so therefore it is
only to be expected that there will be no data shown in the subform.
What we have to identify ow is "why"...

 

[Rachel]

Hi Steve,

I think this could be the problem then - my subform is based on the
Prev Yr Flows table not a query. So I can't post the SQL view of the
query the subform is based on beacuse it isn't based on any query. At
the end of the year I update the Prev Yr Flows table and then I was
expecting the updated info to be drawn through into the subform.

As far as the other things you mentioned, your asssumptions are all
correct as far as I'm aware.

Thanks..

Rachel,

Can you please post back with the SQL view of the query that the subform
is based on?

Here's the deal:
On the parent form, there is a [Site Reference] control, and there is a
[Current Application] control. Right? So when the main form is open,
at any particular record, there will be a particular value for each of
these two controls, right? So in the subform, the *only* records that
will be shown, with your setup, are the records where the value of the
[Site ID] field matches the value of the main form's current [Site
Reference] field, *and* the value of the [Application Number] matches
the value of the main form's current [Current Application] field. It is
apparent that there are no matching subform records, so therefore it is
only to be expected that there will be no data shown in the subform.
What we have to identify ow is "why"...

--
Steve Schapel, Microsoft Access MVP

Hi,

Not sure what happened to the reply I sent the other day, so we'll see
if I can remember what I wrote! When I said they were the ''linked
fields'' I meant that they are the fields that I have based the
relationship between the main form, site browser and the subform, prev
yr flows on. The link master fields and link child field properties of
the subform reads:

Site Reference; Current Application (master)

Site ID; Application Number (child)

When I look at the subform independently, all the data is drawn
through from the prev yr table as well as the Site ID for that
particular record, but the application number isn't. When the subform
is embedded in the main form, none of the data on previous years is
visible but the Site ID and Application Number fields are both
completed.

I'm afraid I'm not too good at all the intricacies of Access so hope
thats answered your query- Hide quoted text -

- Show quoted text -

 

[Steve Schapel]

Rachel,

Ok, thanks for the further clarification.

So, let's say we look at a "test case". Decide on one of the main form
records. When you open the main form at this record, the problem is
that there is no data shown in the subform.

In the table/query that the main form is based on, this record includes
these fields:
Site Reference
Current Application
Take note of the value of these fields for the "test case". Ok?

Now, here's the question... Are there any records in the Prev Yr Flows
table where:
- the Site ID field is the same as the Site Reference in the main table
....and also:
- the Application Number field is the same as the Current Application
field in the main table
??

Sorry, this is similar to what I wrote before, but I was still not clear
on the answer. Thanks.

If the answer is Yes, there are such records in the Prev Yr Flows table,
then also please check in the design view of the tables that the data
type of the corresponding fields are the same.

 

[Rachel]

Hi Steve,

Thanks very much for suggesting going through that method of a test
case. When I went through it step by step as you suggested, I realised
that in the Prev Yr Flows table there was a record with the same Site
Ref, but NOT that application number as well. Once I removed the
criteria for having to match up Site Ref AND Current Application and
limited it to just having the same Site Ref, the subform data was
visible and now works as part of the main form.

Problem solved!

Thanks very much for all your help and patience
Rachel