Form and subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I eliminate record in a form from showing up if there is no child
record in subform??? For example; when I mark record(s) in subform that they
are fixed ( field RecFixed = true ) I would like to not display the record at
all in main form if all child records in subform are marked as fixed.
Otherwise display as normal.
Right now If I click RecFixed and form is refreshed that record in subform
is gone, which is good. But I need to not display record in main form at all
if all record(s) in subform are marked fixed, currently it will display form
without anything loaded in subform.... Hope I'm clear enough.
Thank you in advance
 
DrEvil said:
How do I eliminate record in a form from showing up if there is no child
record in subform??? For example; when I mark record(s) in subform that they
are fixed ( field RecFixed = true ) I would like to not display the record at
all in main form if all child records in subform are marked as fixed.
Otherwise display as normal.
Right now If I click RecFixed and form is refreshed that record in subform
is gone, which is good. But I need to not display record in main form at all
if all record(s) in subform are marked fixed, currently it will display form
without anything loaded in subform....
Click to expand...


Try changing the main form's record source to a query that
uses INNER JOIN to the subform table. Add the RecFixed
field to the query's field list, uncheck its Show box and
set its Criteria to False
 
I was think along those lines but it didn't work like I thought, here is the
SQL view:
SELECT MasterCaseNO.CaseNO, MasterCaseNO.CaseNoMessage,
MasterCaseNO.NoPhysicalFile, MasterCaseNO.FileFixed, MasterCaseNO.ID,
MasterCaseNO.LastUpdated, MasterCaseNO.UserID
FROM MasterCaseNO INNER JOIN [MasterCaseNO 2] ON MasterCaseNO.CaseNO =
[MasterCaseNO 2].CaseNO
WHERE (((MasterCaseNO.FileFixed)=False))
ORDER BY MasterCaseNO.CaseNO;
RecFixed is really FileFixed..
MasterCaseNo is parent form source
What is happening the subform data (which is allowed to repeat) is forcing
numbers for main form to be as big as subform table (MasterCaseNO 2)
So instead listing 12 cases it's listing all 18 from subform
table....Hopefully it makes sense. I think it's relationship that it's maybe
not set right...
 
I should have expected that.

Your query would have come closer if you had the right field
in the WHERE clause. I was expecting it to be:
WHERE [MasterCaseNO 2].RecFixed = False

But I think that's still not what you need. If I
understand, you want only the MasterCaseNO records that have
a related MasterCaseNO 2 record with RecFixed = False. If
I've finally working on the right problem, then let's try
one of these instead:

SELECT MasterCaseNO.CaseNO, . . ., MasterCaseNO.UserID
FROM MasterCaseNO
WHERE Exists (SELECT 1
FROM [MasterCaseNO 2]
WHERE [MasterCaseNO 2].RecFixed = False
AND MasterCaseNO.CaseNO = [MasterCaseNO 2].CaseNO)
ORDER BY MasterCaseNO.CaseNO

or

SELECT MasterCaseNO.CaseNO, . . ., MasterCaseNO.UserID
FROM MasterCaseNO
WHERE False = ANY (SELECT [MasterCaseNO 2].RecFixed
FROM [MasterCaseNO 2]
WHERE MasterCaseNO.CaseNO = [MasterCaseNO 2].CaseNO)
ORDER BY MasterCaseNO.CaseNO

I'm pretty sure that both queries will return the same
dataset, but ine of them may be faster than the other.
--
Marsh
MVP [MS Access]

I was think along those lines but it didn't work like I thought, here is the
SQL view:
SELECT MasterCaseNO.CaseNO, MasterCaseNO.CaseNoMessage,
MasterCaseNO.NoPhysicalFile, MasterCaseNO.FileFixed, MasterCaseNO.ID,
MasterCaseNO.LastUpdated, MasterCaseNO.UserID
FROM MasterCaseNO INNER JOIN [MasterCaseNO 2] ON MasterCaseNO.CaseNO =
[MasterCaseNO 2].CaseNO
WHERE (((MasterCaseNO.FileFixed)=False))
ORDER BY MasterCaseNO.CaseNO;
RecFixed is really FileFixed..
MasterCaseNo is parent form source
What is happening the subform data (which is allowed to repeat) is forcing
numbers for main form to be as big as subform table (MasterCaseNO 2)
So instead listing 12 cases it's listing all 18 from subform
table....Hopefully it makes sense. I think it's relationship that it's maybe
not set right...

Marshall Barton said:
Try changing the main form's record source to a query that
uses INNER JOIN to the subform table. Add the RecFixed
field to the query's field list, uncheck its Show box and
set its Criteria to False
Click to expand...
Click to expand...
 
Wow... you rock man!!!!
This first one works just like I imagined....
And the whole thing is just seamless...
Thank you as always.

Marshall Barton said:
I should have expected that.

Your query would have come closer if you had the right field
in the WHERE clause. I was expecting it to be:
WHERE [MasterCaseNO 2].RecFixed = False

But I think that's still not what you need. If I
understand, you want only the MasterCaseNO records that have
a related MasterCaseNO 2 record with RecFixed = False. If
I've finally working on the right problem, then let's try
one of these instead:

SELECT MasterCaseNO.CaseNO, . . ., MasterCaseNO.UserID
FROM MasterCaseNO
WHERE Exists (SELECT 1
FROM [MasterCaseNO 2]
WHERE [MasterCaseNO 2].RecFixed = False
AND MasterCaseNO.CaseNO = [MasterCaseNO 2].CaseNO)
ORDER BY MasterCaseNO.CaseNO

or

SELECT MasterCaseNO.CaseNO, . . ., MasterCaseNO.UserID
FROM MasterCaseNO
WHERE False = ANY (SELECT [MasterCaseNO 2].RecFixed
FROM [MasterCaseNO 2]
WHERE MasterCaseNO.CaseNO = [MasterCaseNO 2].CaseNO)
ORDER BY MasterCaseNO.CaseNO

I'm pretty sure that both queries will return the same
dataset, but ine of them may be faster than the other.
--
Marsh
MVP [MS Access]

I was think along those lines but it didn't work like I thought, here is the
SQL view:
SELECT MasterCaseNO.CaseNO, MasterCaseNO.CaseNoMessage,
MasterCaseNO.NoPhysicalFile, MasterCaseNO.FileFixed, MasterCaseNO.ID,
MasterCaseNO.LastUpdated, MasterCaseNO.UserID
FROM MasterCaseNO INNER JOIN [MasterCaseNO 2] ON MasterCaseNO.CaseNO =
[MasterCaseNO 2].CaseNO
WHERE (((MasterCaseNO.FileFixed)=False))
ORDER BY MasterCaseNO.CaseNO;
RecFixed is really FileFixed..
MasterCaseNo is parent form source
What is happening the subform data (which is allowed to repeat) is forcing
numbers for main form to be as big as subform table (MasterCaseNO 2)
So instead listing 12 cases it's listing all 18 from subform
table....Hopefully it makes sense. I think it's relationship that it's maybe
not set right...

DrEvil wrote:
How do I eliminate record in a form from showing up if there is no child
record in subform??? For example; when I mark record(s) in subform that they
are fixed ( field RecFixed = true ) I would like to not display the record at
all in main form if all child records in subform are marked as fixed.
Otherwise display as normal.
Right now If I click RecFixed and form is refreshed that record in subform
is gone, which is good. But I need to not display record in main form at all
if all record(s) in subform are marked fixed, currently it will display form
without anything loaded in subform....
Click to expand...
Marshall Barton said:
Try changing the main form's record source to a query that
uses INNER JOIN to the subform table. Add the RecFixed
field to the query's field list, uncheck its Show box and
set its Criteria to False
Click to expand...
Click to expand...
Click to expand...
 
Back
Top