Subforms and Queries

[Guest]

I created a form with subforms in it, and it turned out pretty good. I tried
to make a query from the same tables I made my form with the subforms from.
But when the query opens up, it retrieves NO information, and there isn't
even a white line representing blank data fields. It's just the heading bar
and nothing else. What is up with that? Does this have anything to do using
subforms, I don't see how that could factor into it? Also, do I have to do
anything special with my query since I'm using 4 tables as a basis for my
query?

 

[ruralguy via AccessMonster.com]

Do you Allow Additions on the SubForm? You have described a SubForm bound to
a query that returns no records.

 

[Guest]

The subforms should have no bearing on your query. I'm assuming that since
you said it looked good, the form and subforms properly displayed related
data from all tables? If so then the problem is probably with your
conditions (criteria) - remove them and run the query. If you see data, then
the problem is simply that you have set conditions that match no records. If
you had multiple conditions, try adding them one at a time so you can see
which one has a problem.
If that doesn't fix your problem, reply with more info about your tables and
how they are related.
Good luck!

 

[John W. Vinson]

I created a form with subforms in it, and it turned out pretty good. I tried
to make a query from the same tables I made my form with the subforms from.
But when the query opens up, it retrieves NO information, and there isn't
even a white line representing blank data fields. It's just the heading bar
and nothing else. What is up with that? Does this have anything to do using
subforms, I don't see how that could factor into it? Also, do I have to do
anything special with my query since I'm using 4 tables as a basis for my
query?

A Query joining four tables - using the default Inner Join as the join type -
will return data only if there are matching values in *all four* tables. If
any one of the tables has no data, you'll see nothing at all. This is by
design.

This has nothing to do with subforms; you can easily have a Form with three
subforms, and fill data in on one or two of the subforms leaving the other(s)
blank.

If you want to see the data whether or not there are records in the related
tables, select the join line in the query and view the join's properties.
Select option 2 (or 3) - "Show all data in <one table> and matching records in
<another table>".

John W. Vinson [MVP]

 

[Guest]

Thanks for all your input. I'll put it to use and see what happens.

 

[Guest]

Okay, I tried option 2 for the join properties and everything I needed shows
up. Thanks John!

 

[Guest]

I spoke too soon. I notice that on one of my subforms, some of the fields
aren't showing data. Which is strange because when I check the table itself,
the data is there? The data even shows up on the query, but not on the
subform. I type in the info and then when I try to add a new record, the old
one disappears from the subform. What causes that?

 

[John W. Vinson]

I spoke too soon. I notice that on one of my subforms, some of the fields
aren't showing data. Which is strange because when I check the table itself,
the data is there? The data even shows up on the query, but not on the
subform. I type in the info and then when I try to add a new record, the old
one disappears from the subform. What causes that?

Probably an error in the Master/Child Link Field properties of the subform.
What are these properties? Is there in fact a value in the Master Link Field
corresponding to one or more identical values in the Child Link Field?

John W. Vinson [MVP]

 

[Guest]

Sorry it's a slow go, but where do I find the Master/Child Link Field
properties? Thought it was in the properties window with all the rest but
can't find it. Am I looking in the wrong spot?

 

[John W. Vinson]

Sorry it's a slow go, but where do I find the Master/Child Link Field
properties? Thought it was in the properties window with all the rest but
can't find it. Am I looking in the wrong spot?

I presume so if it's not where you are looking! <g>

You need to view the properties of the Subform Control (*NOT* the Form object
within that control). With the form open in design view select the edge of the
subform - you'll get "handles" visible on the corners - and view its
Properties, or select the Subform Control by name from the controls dropdown
on the top left of the design window. The Master and Child Link Field
properties are on the Data tab of the subform control's properties.

John W. Vinson [MVP]

 

[Guest]

Okay, I found it. There is a value. There is a value in the child link
field as well. The values in Link Child Fields is Task/DeliveryOrder, the
value in Link Master Fields is [NewTask/DeliveryOrderInfo
Subform].Form![Task/DeliveryOrder]. What I don't see is anything referencing
my other form that has my contract numbers in it. Does that form need to be
added too so all will show up in my form?

 

[John W. Vinson]

Okay, I found it. There is a value. There is a value in the child link
field as well. The values in Link Child Fields is Task/DeliveryOrder, the
value in Link Master Fields is [NewTask/DeliveryOrderInfo
Subform].Form![Task/DeliveryOrder]. What I don't see is anything referencing
my other form that has my contract numbers in it. Does that form need to be
added too so all will show up in my form?

I presume so. Bear in mind that I have no way to know your form names, how
they are structured, or how you want them related.

I do know that if you're using a control in one subform as the master link
field of a different subform, you must Requery the "child" subform in the
AfterUpdate event of the "parent" subform. This will need code like

Private Sub Form_AfterUpdate()
Parent!secondsubformname.Requery
End Sub

in the AfterUpdate event of the [NewTask/DeliveryOrderInfo Subform].

There may be other problems that I can't diagnose from the information you've
posted though...

John W. Vinson [MVP]