Filter form for only records with NO records in subform

S

SusanV

Good morning all,

I have a main form (one) with a continuous subform (many). I need to be able
to add an option box to filter the main form to show:
a. All records
b. Only records with associated subform records
c. Only records with NO associated subform records

I'm thinking I can get this via a filter using the subform recordcount, and
I've worked out how to get that info:

Dim i As Integer
i = Me.subfrmTM_HSC.Form.RecordsetClone.RecordCount
Debug.Print i

This returns perfectly the number of associated records in the subform.

How can I apply now use this info to only show records in the main from
where i = 0? I've tried adding a button with the above plus the following
lines:
Me.FilterOn = True
Me.Filter = (i = 0)
This simply returns NO records, although scrolling through the unfiltered
form I know there are dozens of records with 0 in the subform.

I'm sure this can be done, as I've seen posts in the past where someone
needs to find something similar, say customers who have no active orders or
some such.

What am I missing here? Googling has found nothing truly helpful...

TIA,
SusanV
 
S

SusanV

In case anyone is interested, the solution I worked out is to create new
querydefs and use them as teh recordsource fo the form, for example, the
button to filter only unmatched records:

Private Sub cmdFiltNoEQ_Click()
strClass = Me.Class

qname0 = "qryVess_TMs"
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = '" & qname0 &
"'")) Then
DoCmd.DeleteObject acQuery, qname0
End If

strsql = "SELECT tblTMS.TM, tblTMS.TMTitle, tblHSC_TM.ID,
tblVessels.VesselID " _
& "FROM (tblTMS INNER JOIN tblHSC_TM ON tblTMS.TM = tblHSC_TM.TM)
INNER JOIN " _
& "tblVessels ON tblHSC_TM.VesselID = tblVessels.VesselID " _
& "WHERE (((tblVessels.VesselID)='" & Me.VesselID & "'));"

With CurrentDb
Set qdfNew = .CreateQueryDef(qname0, strsql)
.Close
End With

qname = "TMs With No Matching Equipment"
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = '" & qname
& "'")) Then
DoCmd.DeleteObject acQuery, qname
End If

strsql = "SELECT tblTM_Class.TM, tblTMS.TMTitle, tblTM_Class.Class,
tblTMS.System, tblVessels.VesselID, tblTMS.TMFileName " _
& "FROM (tblTMS INNER JOIN (tblTM_Class LEFT JOIN qryVess_TMs ON " _
& "tblTM_Class.TM = qryVess_TMs.TM) ON tblTMS.TM = tblTM_Class.TM)
INNER JOIN " _
& "(tblVessels INNER JOIN tblClass ON tblVessels.Vessel =
tblClass.Vessel) " _
& "ON tblTM_Class.Class = tblClass.Class " _
& "WHERE (((tblTM_Class.Class)='" & strClass & "') AND
((qryVess_TMs.TM) Is Null));"

With CurrentDb
Set qdfNew = .CreateQueryDef(qname, strsql)
.Close
End With

Me.RecordSource = qname

End Sub

Similar code on buttons to show all and to show only matched - I'm sure
there's a simpler solution, and would love to hear it, as this was a real
PITA... but hey, at least it works.
 
G

Guest

I am a bit confused by your post.
a. All records
b. Only records with associated subform records
c. Only records with NO associated subform records

I assume you have A and B but you want C ??

Then you say

"How can I apply now use this info to only show records in the main from
where i = 0? ... This simply returns NO records"

If your recordset count equals to zero then there will be no records. That
is correct. So you either want no records at all or records with zero.

Zero is not the same as no records. What does the zero refer to?
 
S

SusanV

OK, I'll try to clarify a bit.

The main form has say 309 records (technical manuals). The subform lists the
equipment the tech manuals refer to - this can be a single item or
multiple - for instance a diesel engine tech manual will also cover the
turbocharger, governor, attached pumps etc etc etc, whereas a dishwasher
tech manual only covers the dishwasher.

The project involves listing all the equipment and tech manuals on a vessel,
and matching up what tech manual goes to what equipment. All tech manuals
will have equipment matches (otherwise why would they even have the tech
manual? <grin>) - not all equipment will have tech manuals (such as
ventilation fans).

After the engineers go through using an equipment based main form (sub-form
tech manuals filtered by system) they must go back through the opposite way
to QA - main form showing the tech manuals, verifying that ALL tech manuals
have at least one piece of equipment associated.

So.... I need this tech manual based main form to give them the option to
view only those tm's with no associated equipment, OR only those with
equipment for a visual QA, or all tech manuals regardless of equipment
association.

At this point I actually have the NO equipment match worked out via
me.recordsource as posted in my follow-up, but have not been able to figure
how to get only the ones WITH matches. I think I'll have to query a query,
and set that as the recordsource, as the one-to-many is creating duplicate
tech manuals being listed in the main form, I've almost got it worked out,
but this has been a bear and it seems like there ought to be a much simpler
way that setting the recordsource of the main form for each option. I can't
work how to use a filter or where clause to simplify this - and I have
another project coming up where this sort of filtering will be crucial, and
used on multiple forms. I REALLY don't want to have to go through this for
each instance.

below is the code I worked out to get the no matches option.

--
SusanV

''' Start code
Private Sub cmdFiltNoEQ_Click()
strClass = Me.Class

qname0 = "qryVess_TMs"
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = '" & qname0 &
"'")) Then
DoCmd.DeleteObject acQuery, qname0
End If

strsql = "SELECT tblTMS.TM, tblTMS.TMTitle, tblHSC_TM.ID,
tblVessels.VesselID " _
& "FROM (tblTMS INNER JOIN tblHSC_TM ON tblTMS.TM = tblHSC_TM.TM)
INNER JOIN " _
& "tblVessels ON tblHSC_TM.VesselID = tblVessels.VesselID " _
& "WHERE (((tblVessels.VesselID)='" & Me.VesselID & "'));"

With CurrentDb
Set qdfNew = .CreateQueryDef(qname0, strsql)
.Close
End With

qname = "TMs With No Matching Equipment"
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = '" & qname
& "'")) Then
DoCmd.DeleteObject acQuery, qname
End If

strsql = "SELECT tblTM_Class.TM, tblTMS.TMTitle, tblTM_Class.Class,
tblTMS.System, tblVessels.VesselID, tblTMS.TMFileName " _
& "FROM (tblTMS INNER JOIN (tblTM_Class LEFT JOIN qryVess_TMs ON " _
& "tblTM_Class.TM = qryVess_TMs.TM) ON tblTMS.TM = tblTM_Class.TM)
INNER JOIN " _
& "(tblVessels INNER JOIN tblClass ON tblVessels.Vessel =
tblClass.Vessel) " _
& "ON tblTM_Class.Class = tblClass.Class " _
& "WHERE (((tblTM_Class.Class)='" & strClass & "') AND
((qryVess_TMs.TM) Is Null));"

With CurrentDb
Set qdfNew = .CreateQueryDef(qname, strsql)
.Close
End With

Me.RecordSource = qname

End Sub

'''
 
G

Guest

Two questions:

You say: After the engineers go through using an equipment based main form.
Did you design this?

For all equipment that do have manuals, can they be referred to in more than
one?

--
www.ae911truth.org



SusanV said:
OK, I'll try to clarify a bit.

The main form has say 309 records (technical manuals). The subform lists the
equipment the tech manuals refer to - this can be a single item or
multiple - for instance a diesel engine tech manual will also cover the
turbocharger, governor, attached pumps etc etc etc, whereas a dishwasher
tech manual only covers the dishwasher.

The project involves listing all the equipment and tech manuals on a vessel,
and matching up what tech manual goes to what equipment. All tech manuals
will have equipment matches (otherwise why would they even have the tech
manual? <grin>) - not all equipment will have tech manuals (such as
ventilation fans).

After the engineers go through using an equipment based main form (sub-form
tech manuals filtered by system) they must go back through the opposite way
to QA - main form showing the tech manuals, verifying that ALL tech manuals
have at least one piece of equipment associated.

So.... I need this tech manual based main form to give them the option to
view only those tm's with no associated equipment, OR only those with
equipment for a visual QA, or all tech manuals regardless of equipment
association.

At this point I actually have the NO equipment match worked out via
me.recordsource as posted in my follow-up, but have not been able to figure
how to get only the ones WITH matches. I think I'll have to query a query,
and set that as the recordsource, as the one-to-many is creating duplicate
tech manuals being listed in the main form, I've almost got it worked out,
but this has been a bear and it seems like there ought to be a much simpler
way that setting the recordsource of the main form for each option. I can't
work how to use a filter or where clause to simplify this - and I have
another project coming up where this sort of filtering will be crucial, and
used on multiple forms. I REALLY don't want to have to go through this for
each instance.

below is the code I worked out to get the no matches option.

--
SusanV

''' Start code
Private Sub cmdFiltNoEQ_Click()
strClass = Me.Class

qname0 = "qryVess_TMs"
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = '" & qname0 &
"'")) Then
DoCmd.DeleteObject acQuery, qname0
End If

strsql = "SELECT tblTMS.TM, tblTMS.TMTitle, tblHSC_TM.ID,
tblVessels.VesselID " _
& "FROM (tblTMS INNER JOIN tblHSC_TM ON tblTMS.TM = tblHSC_TM.TM)
INNER JOIN " _
& "tblVessels ON tblHSC_TM.VesselID = tblVessels.VesselID " _
& "WHERE (((tblVessels.VesselID)='" & Me.VesselID & "'));"

With CurrentDb
Set qdfNew = .CreateQueryDef(qname0, strsql)
.Close
End With

qname = "TMs With No Matching Equipment"
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = '" & qname
& "'")) Then
DoCmd.DeleteObject acQuery, qname
End If

strsql = "SELECT tblTM_Class.TM, tblTMS.TMTitle, tblTM_Class.Class,
tblTMS.System, tblVessels.VesselID, tblTMS.TMFileName " _
& "FROM (tblTMS INNER JOIN (tblTM_Class LEFT JOIN qryVess_TMs ON " _
& "tblTM_Class.TM = qryVess_TMs.TM) ON tblTMS.TM = tblTM_Class.TM)
INNER JOIN " _
& "(tblVessels INNER JOIN tblClass ON tblVessels.Vessel =
tblClass.Vessel) " _
& "ON tblTM_Class.Class = tblClass.Class " _
& "WHERE (((tblTM_Class.Class)='" & strClass & "') AND
((qryVess_TMs.TM) Is Null));"

With CurrentDb
Set qdfNew = .CreateQueryDef(qname, strsql)
.Close
End With

Me.RecordSource = qname

End Sub

'''





scubadiver said:
I am a bit confused by your post.


I assume you have A and B but you want C ??

Then you say

"How can I apply now use this info to only show records in the main from
where i = 0? ... This simply returns NO records"

If your recordset count equals to zero then there will be no records. That
is correct. So you either want no records at all or records with zero.

Zero is not the same as no records. What does the zero refer to?
 
S

SusanV

Yes, I did the original db design some months back, simple little db with 4
tables and 2 forms and a single report. Of course, being for the Navy, the
project has changed a half dozen times, each time doubling or tripling in
complexity. It would be really nice to get something close to final project
goals defined at the onset, but this is the way the beast behaves, and who
am I to try to train guv'ment officials? <grin>

Each equipment has only one technical manual.
Not all equipment has a tech manual
All tech manuals have at least one equipment.

Shall we twist this further? Each tech manual may reside in any number of
locations, totaling thus far 78 (this will grow to over a hundred by
project's end, but is not available at this time - dribs and drabs). These
locations also must be tracked - which one lives where, who needs what
shipped, who can provide missing tech manuals etc. Oh, and by the way while
you're at it can you also sort this data by vessel class, by vessel
function, and/or vessel operational status? Yeah, thanks!

One thing - this job is always a challenge!

Thanks for your interest,

SusanV



scubadiver said:
Two questions:

You say: After the engineers go through using an equipment based main
form.
Did you design this?

For all equipment that do have manuals, can they be referred to in more
than
one?

--
www.ae911truth.org



SusanV said:
OK, I'll try to clarify a bit.

The main form has say 309 records (technical manuals). The subform lists
the
equipment the tech manuals refer to - this can be a single item or
multiple - for instance a diesel engine tech manual will also cover the
turbocharger, governor, attached pumps etc etc etc, whereas a dishwasher
tech manual only covers the dishwasher.

The project involves listing all the equipment and tech manuals on a
vessel,
and matching up what tech manual goes to what equipment. All tech manuals
will have equipment matches (otherwise why would they even have the tech
manual? <grin>) - not all equipment will have tech manuals (such as
ventilation fans).

After the engineers go through using an equipment based main form
(sub-form
tech manuals filtered by system) they must go back through the opposite
way
to QA - main form showing the tech manuals, verifying that ALL tech
manuals
have at least one piece of equipment associated.

So.... I need this tech manual based main form to give them the option to
view only those tm's with no associated equipment, OR only those with
equipment for a visual QA, or all tech manuals regardless of equipment
association.

At this point I actually have the NO equipment match worked out via
me.recordsource as posted in my follow-up, but have not been able to
figure
how to get only the ones WITH matches. I think I'll have to query a
query,
and set that as the recordsource, as the one-to-many is creating
duplicate
tech manuals being listed in the main form, I've almost got it worked
out,
but this has been a bear and it seems like there ought to be a much
simpler
way that setting the recordsource of the main form for each option. I
can't
work how to use a filter or where clause to simplify this - and I have
another project coming up where this sort of filtering will be crucial,
and
used on multiple forms. I REALLY don't want to have to go through this
for
each instance.

below is the code I worked out to get the no matches option.

--
SusanV

''' Start code
Private Sub cmdFiltNoEQ_Click()
strClass = Me.Class

qname0 = "qryVess_TMs"
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = '" & qname0
&
"'")) Then
DoCmd.DeleteObject acQuery, qname0
End If

strsql = "SELECT tblTMS.TM, tblTMS.TMTitle, tblHSC_TM.ID,
tblVessels.VesselID " _
& "FROM (tblTMS INNER JOIN tblHSC_TM ON tblTMS.TM = tblHSC_TM.TM)
INNER JOIN " _
& "tblVessels ON tblHSC_TM.VesselID = tblVessels.VesselID " _
& "WHERE (((tblVessels.VesselID)='" & Me.VesselID & "'));"

With CurrentDb
Set qdfNew = .CreateQueryDef(qname0, strsql)
.Close
End With

qname = "TMs With No Matching Equipment"
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = '" &
qname
& "'")) Then
DoCmd.DeleteObject acQuery, qname
End If

strsql = "SELECT tblTM_Class.TM, tblTMS.TMTitle, tblTM_Class.Class,
tblTMS.System, tblVessels.VesselID, tblTMS.TMFileName " _
& "FROM (tblTMS INNER JOIN (tblTM_Class LEFT JOIN qryVess_TMs ON
" _
& "tblTM_Class.TM = qryVess_TMs.TM) ON tblTMS.TM =
tblTM_Class.TM)
INNER JOIN " _
& "(tblVessels INNER JOIN tblClass ON tblVessels.Vessel =
tblClass.Vessel) " _
& "ON tblTM_Class.Class = tblClass.Class " _
& "WHERE (((tblTM_Class.Class)='" & strClass & "') AND
((qryVess_TMs.TM) Is Null));"

With CurrentDb
Set qdfNew = .CreateQueryDef(qname, strsql)
.Close
End With

Me.RecordSource = qname

End Sub

'''





scubadiver said:
I am a bit confused by your post.

a. All records
b. Only records with associated subform records
c. Only records with NO associated subform records

I assume you have A and B but you want C ??

Then you say

"How can I apply now use this info to only show records in the main
from
where i = 0? ... This simply returns NO records"

If your recordset count equals to zero then there will be no records.
That
is correct. So you either want no records at all or records with zero.

Zero is not the same as no records. What does the zero refer to?
 
G

Guest

I think the simplest thing to do (at the moment anyway) would be to have two
buttons. One button opens a query that has no matches and a button that opens
a query that has matches.

If you have managed to get the no matches then why not just change the
filter to those records where it is greater than zero.

--
www.ae911truth.org



SusanV said:
Yes, I did the original db design some months back, simple little db with 4
tables and 2 forms and a single report. Of course, being for the Navy, the
project has changed a half dozen times, each time doubling or tripling in
complexity. It would be really nice to get something close to final project
goals defined at the onset, but this is the way the beast behaves, and who
am I to try to train guv'ment officials? <grin>

Each equipment has only one technical manual.
Not all equipment has a tech manual
All tech manuals have at least one equipment.

Shall we twist this further? Each tech manual may reside in any number of
locations, totaling thus far 78 (this will grow to over a hundred by
project's end, but is not available at this time - dribs and drabs). These
locations also must be tracked - which one lives where, who needs what
shipped, who can provide missing tech manuals etc. Oh, and by the way while
you're at it can you also sort this data by vessel class, by vessel
function, and/or vessel operational status? Yeah, thanks!

One thing - this job is always a challenge!

Thanks for your interest,

SusanV



scubadiver said:
Two questions:

You say: After the engineers go through using an equipment based main
form.
Did you design this?

For all equipment that do have manuals, can they be referred to in more
than
one?

--
www.ae911truth.org



SusanV said:
OK, I'll try to clarify a bit.

The main form has say 309 records (technical manuals). The subform lists
the
equipment the tech manuals refer to - this can be a single item or
multiple - for instance a diesel engine tech manual will also cover the
turbocharger, governor, attached pumps etc etc etc, whereas a dishwasher
tech manual only covers the dishwasher.

The project involves listing all the equipment and tech manuals on a
vessel,
and matching up what tech manual goes to what equipment. All tech manuals
will have equipment matches (otherwise why would they even have the tech
manual? <grin>) - not all equipment will have tech manuals (such as
ventilation fans).

After the engineers go through using an equipment based main form
(sub-form
tech manuals filtered by system) they must go back through the opposite
way
to QA - main form showing the tech manuals, verifying that ALL tech
manuals
have at least one piece of equipment associated.

So.... I need this tech manual based main form to give them the option to
view only those tm's with no associated equipment, OR only those with
equipment for a visual QA, or all tech manuals regardless of equipment
association.

At this point I actually have the NO equipment match worked out via
me.recordsource as posted in my follow-up, but have not been able to
figure
how to get only the ones WITH matches. I think I'll have to query a
query,
and set that as the recordsource, as the one-to-many is creating
duplicate
tech manuals being listed in the main form, I've almost got it worked
out,
but this has been a bear and it seems like there ought to be a much
simpler
way that setting the recordsource of the main form for each option. I
can't
work how to use a filter or where clause to simplify this - and I have
another project coming up where this sort of filtering will be crucial,
and
used on multiple forms. I REALLY don't want to have to go through this
for
each instance.

below is the code I worked out to get the no matches option.

--
SusanV

''' Start code
Private Sub cmdFiltNoEQ_Click()
strClass = Me.Class

qname0 = "qryVess_TMs"
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = '" & qname0
&
"'")) Then
DoCmd.DeleteObject acQuery, qname0
End If

strsql = "SELECT tblTMS.TM, tblTMS.TMTitle, tblHSC_TM.ID,
tblVessels.VesselID " _
& "FROM (tblTMS INNER JOIN tblHSC_TM ON tblTMS.TM = tblHSC_TM.TM)
INNER JOIN " _
& "tblVessels ON tblHSC_TM.VesselID = tblVessels.VesselID " _
& "WHERE (((tblVessels.VesselID)='" & Me.VesselID & "'));"

With CurrentDb
Set qdfNew = .CreateQueryDef(qname0, strsql)
.Close
End With

qname = "TMs With No Matching Equipment"
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = '" &
qname
& "'")) Then
DoCmd.DeleteObject acQuery, qname
End If

strsql = "SELECT tblTM_Class.TM, tblTMS.TMTitle, tblTM_Class.Class,
tblTMS.System, tblVessels.VesselID, tblTMS.TMFileName " _
& "FROM (tblTMS INNER JOIN (tblTM_Class LEFT JOIN qryVess_TMs ON
" _
& "tblTM_Class.TM = qryVess_TMs.TM) ON tblTMS.TM =
tblTM_Class.TM)
INNER JOIN " _
& "(tblVessels INNER JOIN tblClass ON tblVessels.Vessel =
tblClass.Vessel) " _
& "ON tblTM_Class.Class = tblClass.Class " _
& "WHERE (((tblTM_Class.Class)='" & strClass & "') AND
((qryVess_TMs.TM) Is Null));"

With CurrentDb
Set qdfNew = .CreateQueryDef(qname, strsql)
.Close
End With

Me.RecordSource = qname

End Sub

'''






I am a bit confused by your post.

a. All records
b. Only records with associated subform records
c. Only records with NO associated subform records

I assume you have A and B but you want C ??

Then you say

"How can I apply now use this info to only show records in the main
from
where i = 0? ... This simply returns NO records"

If your recordset count equals to zero then there will be no records.
That
is correct. So you either want no records at all or records with zero.

Zero is not the same as no records. What does the zero refer to?
 
S

SusanV

Yeah I'm using the queries, it's working OK, I just can't comprehend why I
can't use a filter instead

=/

Thanks for your help,

SusanV

scubadiver said:
I think the simplest thing to do (at the moment anyway) would be to have
two
buttons. One button opens a query that has no matches and a button that
opens
a query that has matches.

If you have managed to get the no matches then why not just change the
filter to those records where it is greater than zero.

--
www.ae911truth.org



SusanV said:
Yes, I did the original db design some months back, simple little db with
4
tables and 2 forms and a single report. Of course, being for the Navy,
the
project has changed a half dozen times, each time doubling or tripling in
complexity. It would be really nice to get something close to final
project
goals defined at the onset, but this is the way the beast behaves, and
who
am I to try to train guv'ment officials? <grin>

Each equipment has only one technical manual.
Not all equipment has a tech manual
All tech manuals have at least one equipment.

Shall we twist this further? Each tech manual may reside in any number of
locations, totaling thus far 78 (this will grow to over a hundred by
project's end, but is not available at this time - dribs and drabs).
These
locations also must be tracked - which one lives where, who needs what
shipped, who can provide missing tech manuals etc. Oh, and by the way
while
you're at it can you also sort this data by vessel class, by vessel
function, and/or vessel operational status? Yeah, thanks!

One thing - this job is always a challenge!

Thanks for your interest,

SusanV



scubadiver said:
Two questions:

You say: After the engineers go through using an equipment based main
form.
Did you design this?

For all equipment that do have manuals, can they be referred to in more
than
one?

--
www.ae911truth.org



:

OK, I'll try to clarify a bit.

The main form has say 309 records (technical manuals). The subform
lists
the
equipment the tech manuals refer to - this can be a single item or
multiple - for instance a diesel engine tech manual will also cover
the
turbocharger, governor, attached pumps etc etc etc, whereas a
dishwasher
tech manual only covers the dishwasher.

The project involves listing all the equipment and tech manuals on a
vessel,
and matching up what tech manual goes to what equipment. All tech
manuals
will have equipment matches (otherwise why would they even have the
tech
manual? <grin>) - not all equipment will have tech manuals (such as
ventilation fans).

After the engineers go through using an equipment based main form
(sub-form
tech manuals filtered by system) they must go back through the
opposite
way
to QA - main form showing the tech manuals, verifying that ALL tech
manuals
have at least one piece of equipment associated.

So.... I need this tech manual based main form to give them the option
to
view only those tm's with no associated equipment, OR only those with
equipment for a visual QA, or all tech manuals regardless of equipment
association.

At this point I actually have the NO equipment match worked out via
me.recordsource as posted in my follow-up, but have not been able to
figure
how to get only the ones WITH matches. I think I'll have to query a
query,
and set that as the recordsource, as the one-to-many is creating
duplicate
tech manuals being listed in the main form, I've almost got it worked
out,
but this has been a bear and it seems like there ought to be a much
simpler
way that setting the recordsource of the main form for each option. I
can't
work how to use a filter or where clause to simplify this - and I have
another project coming up where this sort of filtering will be
crucial,
and
used on multiple forms. I REALLY don't want to have to go through this
for
each instance.

below is the code I worked out to get the no matches option.

--
SusanV

''' Start code
Private Sub cmdFiltNoEQ_Click()
strClass = Me.Class

qname0 = "qryVess_TMs"
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = '" &
qname0
&
"'")) Then
DoCmd.DeleteObject acQuery, qname0
End If

strsql = "SELECT tblTMS.TM, tblTMS.TMTitle, tblHSC_TM.ID,
tblVessels.VesselID " _
& "FROM (tblTMS INNER JOIN tblHSC_TM ON tblTMS.TM =
tblHSC_TM.TM)
INNER JOIN " _
& "tblVessels ON tblHSC_TM.VesselID = tblVessels.VesselID " _
& "WHERE (((tblVessels.VesselID)='" & Me.VesselID & "'));"

With CurrentDb
Set qdfNew = .CreateQueryDef(qname0, strsql)
.Close
End With

qname = "TMs With No Matching Equipment"
If Not IsNull(DLookup("[Name]", "MSysObjects", "[Name] = '" &
qname
& "'")) Then
DoCmd.DeleteObject acQuery, qname
End If

strsql = "SELECT tblTM_Class.TM, tblTMS.TMTitle,
tblTM_Class.Class,
tblTMS.System, tblVessels.VesselID, tblTMS.TMFileName " _
& "FROM (tblTMS INNER JOIN (tblTM_Class LEFT JOIN qryVess_TMs
ON
" _
& "tblTM_Class.TM = qryVess_TMs.TM) ON tblTMS.TM =
tblTM_Class.TM)
INNER JOIN " _
& "(tblVessels INNER JOIN tblClass ON tblVessels.Vessel =
tblClass.Vessel) " _
& "ON tblTM_Class.Class = tblClass.Class " _
& "WHERE (((tblTM_Class.Class)='" & strClass & "') AND
((qryVess_TMs.TM) Is Null));"

With CurrentDb
Set qdfNew = .CreateQueryDef(qname, strsql)
.Close
End With

Me.RecordSource = qname

End Sub

'''






I am a bit confused by your post.

a. All records
b. Only records with associated subform records
c. Only records with NO associated subform records

I assume you have A and B but you want C ??

Then you say

"How can I apply now use this info to only show records in the main
from
where i = 0? ... This simply returns NO records"

If your recordset count equals to zero then there will be no
records.
That
is correct. So you either want no records at all or records with
zero.

Zero is not the same as no records. What does the zero refer to?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top