Do Records Exist in Query or Filter

[Guest]

I have tried suggestions under filtering threads, but it keeps
asking me for a value for both my fields, and even if I input the value in
the dialog presented, it
does not filter anyway.

Background:
I have a header form and a detail form.
There is a (No Issues) checkbox on the header that means that there
are/should not be any detail (Issues) present in the detail, for the
ProjectID that is in the header. Note: Detail entries have different
project numbers, other than the one I check in the header. I need to match
the one that is in the header.

I have gone through about 4 iterations of this code, but this was my last try:
'note that these ProjectID's are Decimals (don't ask. designed by another
vendor)
'this code is located in form activate and validates the detail, when the
CheckBox is true.

Set frm = Me.frmProjectIssue.Form 'the detail form
'Me is the header form
frm.Filter = "Me.[ProjectId].Value - frm.[ProjectId].Value = 0"
'suggestion from another thread
frm.FilterOn = True

If frm.NewRecord Then
'no records found for filter 'OK-'suggestion from another thread

frm.Filter = ""
frmFilterOn = True

Else 'records were found for ProjectID in header 'NOT ok

frm.Filter = ""
frmFilterOn = True
... <code to present msgbox and uncheck the header checkbox>

Where am I going astray here and is there an easier way to check to see if
-any- matching (header ID = any detail) ID with the same value)?

 

[Graham Mandeno]

Hi Michael

Access will do this for you automatically - no code required!

A subform (or subreport) control has two related properties called
LinkMasterFields and LinkChildFields. LinkMasterFields should be set to the
name(s) of one or more controls or recordsource fields on your main form.
LinkChildFields should be set to the name(s) of the related field(s) on your
subform. These serve two purposes:

1. When the value of the LinkMasterFields control changes (for example, by
navigating the main form to a new record), the subform is automatically
filtered so as to show only the related records.

2. Any new records added to the subform will automatically inherit the
LinkMasterFields value as the default value for the LinkChildFields field.

So, all you need to do is set both of these properties to "ProjectID".

As for the checkbox, I would simply set or clear it depending on whether
there are related records. Set its controlsource to:
=frmProjectIssue.Form.RecordsetClone.RecordCount > 0

 

[Guest]

thanks for responding.

that is the solution to a normal header, detail setup, where you have a
particular project is the header and only those child projects that relate to
the project in the header.

In this case, the user can enter any projectid in the detail for the
parentprojectid in the header. The header has a parentprojectid and a
projectid.

When the user loads a project, they provide the projectid (the parent trails
along).
I want to stop them if they enter any projectid that is not related to the
parentprojectid.

The parent appears in the header, but it's the link to the detail. I was
trying to read the field, but the field was locked, disabled, and not the
current field (no focus).

I unlocked it, enabled it, and focused it, but then the error was that I had
to save the record before I could focus on the parent field.

What I did to solve it was to read it off the parent form. It didn't care
about it being locked, disabled, or not focused, and it the, header, wasn't
in the edit mode.

Maybe I can't read it off the detail line, unless I set up a clone or
something.

--
MichaelM

Hi Michael

Access will do this for you automatically - no code required!

A subform (or subreport) control has two related properties called
LinkMasterFields and LinkChildFields. LinkMasterFields should be set to the
name(s) of one or more controls or recordsource fields on your main form.
LinkChildFields should be set to the name(s) of the related field(s) on your
subform. These serve two purposes:

1. When the value of the LinkMasterFields control changes (for example, by
navigating the main form to a new record), the subform is automatically
filtered so as to show only the related records.

2. Any new records added to the subform will automatically inherit the
LinkMasterFields value as the default value for the LinkChildFields field.

So, all you need to do is set both of these properties to "ProjectID".

As for the checkbox, I would simply set or clear it depending on whether
there are related records. Set its controlsource to:
=frmProjectIssue.Form.RecordsetClone.RecordCount > 0

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have tried suggestions under filtering threads, but it keeps
asking me for a value for both my fields, and even if I input the value in
the dialog presented, it
does not filter anyway.

Background:
I have a header form and a detail form.
There is a (No Issues) checkbox on the header that means that there
are/should not be any detail (Issues) present in the detail, for the
ProjectID that is in the header. Note: Detail entries have different
project numbers, other than the one I check in the header. I need to
match
the one that is in the header.

I have gone through about 4 iterations of this code, but this was my last
try:
'note that these ProjectID's are Decimals (don't ask. designed by another
vendor)
'this code is located in form activate and validates the detail, when the
CheckBox is true.

Set frm = Me.frmProjectIssue.Form 'the detail form
'Me is the header form
frm.Filter = "Me.[ProjectId].Value - frm.[ProjectId].Value = 0"
'suggestion from another thread
frm.FilterOn = True

If frm.NewRecord Then
'no records found for filter 'OK-'suggestion from another thread

frm.Filter = ""
frmFilterOn = True

Else 'records were found for ProjectID in header 'NOT ok

frm.Filter = ""
frmFilterOn = True
... <code to present msgbox and uncheck the header
checkbox>

Where am I going astray here and is there an easier way to check to see if
-any- matching (header ID = any detail) ID with the same value)?

 

[Graham Mandeno]

Hi Michael

Sorry, I'm not following you. Can you please post some more information
about your tables - the field names and how they relate to one another.

Are you saying that you want your subform to display the "sibling" records
of the project in the main form (header), rather than the child records as
is the usual practice?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

thanks for responding.

that is the solution to a normal header, detail setup, where you have a
particular project is the header and only those child projects that relate
to
the project in the header.

In this case, the user can enter any projectid in the detail for the
parentprojectid in the header. The header has a parentprojectid and a
projectid.

When the user loads a project, they provide the projectid (the parent
trails
along).
I want to stop them if they enter any projectid that is not related to the
parentprojectid.

The parent appears in the header, but it's the link to the detail. I was
trying to read the field, but the field was locked, disabled, and not the
current field (no focus).

I unlocked it, enabled it, and focused it, but then the error was that I
had
to save the record before I could focus on the parent field.

What I did to solve it was to read it off the parent form. It didn't care
about it being locked, disabled, or not focused, and it the, header,
wasn't
in the edit mode.

Maybe I can't read it off the detail line, unless I set up a clone or
something.

--
MichaelM

Hi Michael

Access will do this for you automatically - no code required!

A subform (or subreport) control has two related properties called
LinkMasterFields and LinkChildFields. LinkMasterFields should be set to
the
name(s) of one or more controls or recordsource fields on your main form.
LinkChildFields should be set to the name(s) of the related field(s) on
your
subform. These serve two purposes:

1. When the value of the LinkMasterFields control changes (for example,
by
navigating the main form to a new record), the subform is automatically
filtered so as to show only the related records.

2. Any new records added to the subform will automatically inherit the
LinkMasterFields value as the default value for the LinkChildFields
field.

So, all you need to do is set both of these properties to "ProjectID".

As for the checkbox, I would simply set or clear it depending on whether
there are related records. Set its controlsource to:
=frmProjectIssue.Form.RecordsetClone.RecordCount > 0

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message

I have tried suggestions under filtering threads, but it keeps
asking me for a value for both my fields, and even if I input the value
in
the dialog presented, it
does not filter anyway.

Background:
I have a header form and a detail form.
There is a (No Issues) checkbox on the header that means that there
are/should not be any detail (Issues) present in the detail, for the
ProjectID that is in the header. Note: Detail entries have different
project numbers, other than the one I check in the header. I need to
match
the one that is in the header.

I have gone through about 4 iterations of this code, but this was my
last
try:
'note that these ProjectID's are Decimals (don't ask. designed by
another
vendor)
'this code is located in form activate and validates the detail, when
the
CheckBox is true.

Set frm = Me.frmProjectIssue.Form 'the detail form
'Me is the header form
frm.Filter = "Me.[ProjectId].Value - frm.[ProjectId].Value =
0"
'suggestion from another thread
frm.FilterOn = True

If frm.NewRecord Then
'no records found for filter 'OK-'suggestion from another thread

frm.Filter = ""
frmFilterOn = True

Else 'records were found for ProjectID in header 'NOT
ok

frm.Filter = ""
frmFilterOn = True
... <code to present msgbox and uncheck the header
checkbox>

Where am I going astray here and is there an easier way to check to see
if
-any- matching (header ID = any detail) ID with the same value)?

 

[Guest]

Header table has ParentProjectID, ProjectID
Detail table has ParentProjectID, ProjectID, Item
The link is only on the ParentProjectID, between the header and the detail.

The spec is that given a parentid of #1, and children of #'s 2, 3, 4, 5,
if they load projectid #3, to work on, (opening the form is based on
ProjectID (not Parent ID), they will see 1, 2, 3, 4, and 5's detail records.

This was a convenience to the users to not have to open each and every
header, in order to enter detail records. Any project within the family, can
be entered from any family's header projectid.

All this is set up and working. My validation was to stop them from
entering a child projectid, that was -not part- of the family.

My problem was that all I had to do to complete my code, was to get the
value of the parentid in the detail line, and I had a query ready to go.

--
MichaelM

Hi Michael

Sorry, I'm not following you. Can you please post some more information
about your tables - the field names and how they relate to one another.

Are you saying that you want your subform to display the "sibling" records
of the project in the main form (header), rather than the child records as
is the usual practice?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

thanks for responding.

that is the solution to a normal header, detail setup, where you have a
particular project is the header and only those child projects that relate
to
the project in the header.

In this case, the user can enter any projectid in the detail for the
parentprojectid in the header. The header has a parentprojectid and a
projectid.

When the user loads a project, they provide the projectid (the parent
trails
along).
I want to stop them if they enter any projectid that is not related to the
parentprojectid.

The parent appears in the header, but it's the link to the detail. I was
trying to read the field, but the field was locked, disabled, and not the
current field (no focus).

I unlocked it, enabled it, and focused it, but then the error was that I
had
to save the record before I could focus on the parent field.

What I did to solve it was to read it off the parent form. It didn't care
about it being locked, disabled, or not focused, and it the, header,
wasn't
in the edit mode.

Maybe I can't read it off the detail line, unless I set up a clone or
something.

--
MichaelM

Hi Michael

Access will do this for you automatically - no code required!

A subform (or subreport) control has two related properties called
LinkMasterFields and LinkChildFields. LinkMasterFields should be set to
the
name(s) of one or more controls or recordsource fields on your main form.
LinkChildFields should be set to the name(s) of the related field(s) on
your
subform. These serve two purposes:

1. When the value of the LinkMasterFields control changes (for example,
by
navigating the main form to a new record), the subform is automatically
filtered so as to show only the related records.

2. Any new records added to the subform will automatically inherit the
LinkMasterFields value as the default value for the LinkChildFields
field.

So, all you need to do is set both of these properties to "ProjectID".

As for the checkbox, I would simply set or clear it depending on whether
there are related records. Set its controlsource to:
=frmProjectIssue.Form.RecordsetClone.RecordCount > 0

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
I have tried suggestions under filtering threads, but it keeps
asking me for a value for both my fields, and even if I input the value
in
the dialog presented, it
does not filter anyway.

Background:
I have a header form and a detail form.
There is a (No Issues) checkbox on the header that means that there
are/should not be any detail (Issues) present in the detail, for the
ProjectID that is in the header. Note: Detail entries have different
project numbers, other than the one I check in the header. I need to
match
the one that is in the header.

I have gone through about 4 iterations of this code, but this was my
last
try:
'note that these ProjectID's are Decimals (don't ask. designed by
another
vendor)
'this code is located in form activate and validates the detail, when
the
CheckBox is true.

Set frm = Me.frmProjectIssue.Form 'the detail form
'Me is the header form
frm.Filter = "Me.[ProjectId].Value - frm.[ProjectId].Value =
0"
'suggestion from another thread
frm.FilterOn = True

If frm.NewRecord Then
'no records found for filter 'OK-'suggestion from another thread

frm.Filter = ""
frmFilterOn = True

Else 'records were found for ProjectID in header 'NOT
ok

frm.Filter = ""
frmFilterOn = True
... <code to present msgbox and uncheck the header
checkbox>

Where am I going astray here and is there an easier way to check to see
if
-any- matching (header ID = any detail) ID with the same value)?

 

[Graham Mandeno]

Hi Michael

OK, so you want to see the parent AND all the siblings of the current main
form record in the subform?

If you wanted just the siblings this could have been done with
LinkMaster/ChildFields, but you want to match two pairs of fields with an OR
relationship. For this you will need a filter. Try this:

With Me.frmProjectIssue.Form
.Filter = "ParentProjectId=" & Me.ParentProjectID _
& " OR ProjectId=" & Me.ParentProjectID
.FilterOn = True
End With

This should be in the main form's Form_Current event procedure.

I'm not sure what you mean by "stop them from entering a child projectid,
that was -not part- of the family."

Are you using the subform to add new child projects? If so, then set the
DefaultValue of ParentProjectId in the same code block:

.ParentProjectId.DefaultValue = Me.ParentProjectId
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Header table has ParentProjectID, ProjectID
Detail table has ParentProjectID, ProjectID, Item
The link is only on the ParentProjectID, between the header and the
detail.

The spec is that given a parentid of #1, and children of #'s 2, 3, 4, 5,
if they load projectid #3, to work on, (opening the form is based on
ProjectID (not Parent ID), they will see 1, 2, 3, 4, and 5's detail
records.

This was a convenience to the users to not have to open each and every
header, in order to enter detail records. Any project within the family,
can
be entered from any family's header projectid.

All this is set up and working. My validation was to stop them from
entering a child projectid, that was -not part- of the family.

My problem was that all I had to do to complete my code, was to get the
value of the parentid in the detail line, and I had a query ready to go.

--
MichaelM

Hi Michael

Sorry, I'm not following you. Can you please post some more information
about your tables - the field names and how they relate to one another.

Are you saying that you want your subform to display the "sibling"
records
of the project in the main form (header), rather than the child records
as
is the usual practice?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

message

thanks for responding.

that is the solution to a normal header, detail setup, where you have a
particular project is the header and only those child projects that
relate
to
the project in the header.

In this case, the user can enter any projectid in the detail for the
parentprojectid in the header. The header has a parentprojectid and a
projectid.

When the user loads a project, they provide the projectid (the parent
trails
along).
I want to stop them if they enter any projectid that is not related to
the
parentprojectid.

The parent appears in the header, but it's the link to the detail. I
was
trying to read the field, but the field was locked, disabled, and not
the
current field (no focus).

I unlocked it, enabled it, and focused it, but then the error was that
I
had
to save the record before I could focus on the parent field.

What I did to solve it was to read it off the parent form. It didn't
care
about it being locked, disabled, or not focused, and it the, header,
wasn't
in the edit mode.

Maybe I can't read it off the detail line, unless I set up a clone or
something.

--
MichaelM


:

Hi Michael

Access will do this for you automatically - no code required!

A subform (or subreport) control has two related properties called
LinkMasterFields and LinkChildFields. LinkMasterFields should be set
to
the
name(s) of one or more controls or recordsource fields on your main
form.
LinkChildFields should be set to the name(s) of the related field(s)
on
your
subform. These serve two purposes:

1. When the value of the LinkMasterFields control changes (for
example,
by
navigating the main form to a new record), the subform is
automatically
filtered so as to show only the related records.

2. Any new records added to the subform will automatically inherit the
LinkMasterFields value as the default value for the LinkChildFields
field.

So, all you need to do is set both of these properties to "ProjectID".

As for the checkbox, I would simply set or clear it depending on
whether
there are related records. Set its controlsource to:
=frmProjectIssue.Form.RecordsetClone.RecordCount > 0

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
I have tried suggestions under filtering threads, but it keeps
asking me for a value for both my fields, and even if I input the
value
in
the dialog presented, it
does not filter anyway.

Background:
I have a header form and a detail form.
There is a (No Issues) checkbox on the header that means that there
are/should not be any detail (Issues) present in the detail, for the
ProjectID that is in the header. Note: Detail entries have
different
project numbers, other than the one I check in the header. I need
to
match
the one that is in the header.

I have gone through about 4 iterations of this code, but this was my
last
try:
'note that these ProjectID's are Decimals (don't ask. designed by
another
vendor)
'this code is located in form activate and validates the detail,
when
the
CheckBox is true.

Set frm = Me.frmProjectIssue.Form 'the detail form
'Me is the header form
frm.Filter = "Me.[ProjectId].Value -
frm.[ProjectId].Value =
0"
'suggestion from another thread
frm.FilterOn = True

If frm.NewRecord Then
'no records found for filter 'OK-'suggestion from another thread

frm.Filter = ""
frmFilterOn = True

Else 'records were found for ProjectID in header
'NOT
ok

frm.Filter = ""
frmFilterOn = True
... <code to present msgbox and uncheck the header
checkbox>

Where am I going astray here and is there an easier way to check to
see
if
-any- matching (header ID = any detail) ID with the same value)?

 

[Guest]

BELOW, IN CAPS:
--
MichaelM

Hi Michael

OK, so you want to see the parent AND all the siblings of the current main
form record in the subform?

YES

MAIN: PARENTPROJECTID, PROJECTID
SUB: PARENTPROJECTID, PROJECTID, ITEM
LINKED BY PARENT ONLY. BUT SINCE THEY ENTER THE PROJECTID IN THE SUB, I
HAVE TO VALIDATE/CONTROL IT.

If you wanted just the siblings this could have been done with
LinkMaster/ChildFields,

TRUE, but the subform -is- linked to the master by Parent id only and the
detail table contains parent and project, so I do see what I want. The
objective is to read the checkbox, which should only be checked if there were
NO ISSUES. So, when I filter I need to check to see what the result was. If
it found any detail with the Project id in the header, then the checkbox is
wrong and needs to be falsed.

but you want to match two pairs of fields with an OR

relationship. For this you will need a filter. Try this:

I THINK I NEEDED TO JUST MATCH THE PROJECTID IN THE HEADER WITH ANY RECORD
WITH THAT PROJECTID IN THE DETAIL TO PROVE THE CHECKBOX WRONG. IF THERE IS
NO RESULT, THEN THE CHECKBOX SHOULD BE TRUE.

With Me.frmProjectIssue.Form
.Filter = "ParentProjectId=" & Me.ParentProjectID _
& " OR ProjectId=" & Me.ParentProjectID
.FilterOn = True
End With

This should be in the main form's Form_Current event procedure.

I'm not sure what you mean by "stop them from entering a child projectid,
that was -not part- of the family."

THAT WAS ANOTHER PART, THAT IF THEY ENTER A PROJECTID IN THE DETAIL, THAT
ISN'T THE IN THE SAME PARENTPROJECTID, FROM THE MASTER PROJECT TABLE, THEN I
CANCEL AND UNDO THEIR INPUT

Are you using the subform to add new child projects? If so, then set the
DefaultValue of ParentProjectId in the same code block:

NOT A NEW PROJECT. RATHER THE DETAIL RECORD STARTS WITH THEM ENTERING THE
PROJECTID, THAT IS PART OF THE FAMILY, AND CONTINUING WITH OTHER DETAIL INFO
ON THE RECORD.

.ParentProjectId.DefaultValue = Me.ParentProjectId
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Header table has ParentProjectID, ProjectID
Detail table has ParentProjectID, ProjectID, Item
The link is only on the ParentProjectID, between the header and the
detail.

The spec is that given a parentid of #1, and children of #'s 2, 3, 4, 5,
if they load projectid #3, to work on, (opening the form is based on
ProjectID (not Parent ID), they will see 1, 2, 3, 4, and 5's detail
records.

This was a convenience to the users to not have to open each and every
header, in order to enter detail records. Any project within the family,
can
be entered from any family's header projectid.

All this is set up and working. My validation was to stop them from
entering a child projectid, that was -not part- of the family.

My problem was that all I had to do to complete my code, was to get the
value of the parentid in the detail line, and I had a query ready to go.

--
MichaelM

Hi Michael

Sorry, I'm not following you. Can you please post some more information
about your tables - the field names and how they relate to one another.

Are you saying that you want your subform to display the "sibling"
records
of the project in the main form (header), rather than the child records
as
is the usual practice?

--
Graham Mandeno [Access MVP]
Auckland, New Zealand

message
thanks for responding.

that is the solution to a normal header, detail setup, where you have a
particular project is the header and only those child projects that
relate
to
the project in the header.

In this case, the user can enter any projectid in the detail for the
parentprojectid in the header. The header has a parentprojectid and a
projectid.

When the user loads a project, they provide the projectid (the parent
trails
along).
I want to stop them if they enter any projectid that is not related to
the
parentprojectid.

The parent appears in the header, but it's the link to the detail. I
was
trying to read the field, but the field was locked, disabled, and not
the
current field (no focus).

I unlocked it, enabled it, and focused it, but then the error was that
I
had
to save the record before I could focus on the parent field.

What I did to solve it was to read it off the parent form. It didn't
care
about it being locked, disabled, or not focused, and it the, header,
wasn't
in the edit mode.

Maybe I can't read it off the detail line, unless I set up a clone or
something.

--
MichaelM


:

Hi Michael

Access will do this for you automatically - no code required!

A subform (or subreport) control has two related properties called
LinkMasterFields and LinkChildFields. LinkMasterFields should be set
to
the
name(s) of one or more controls or recordsource fields on your main
form.
LinkChildFields should be set to the name(s) of the related field(s)
on
your
subform. These serve two purposes:

1. When the value of the LinkMasterFields control changes (for
example,
by
navigating the main form to a new record), the subform is
automatically
filtered so as to show only the related records.

2. Any new records added to the subform will automatically inherit the
LinkMasterFields value as the default value for the LinkChildFields
field.

So, all you need to do is set both of these properties to "ProjectID".

As for the checkbox, I would simply set or clear it depending on
whether
there are related records. Set its controlsource to:
=frmProjectIssue.Form.RecordsetClone.RecordCount > 0

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

message
I have tried suggestions under filtering threads, but it keeps
asking me for a value for both my fields, and even if I input the
value
in
the dialog presented, it
does not filter anyway.

Background:
I have a header form and a detail form.
There is a (No Issues) checkbox on the header that means that there
are/should not be any detail (Issues) present in the detail, for the
ProjectID that is in the header. Note: Detail entries have
different
project numbers, other than the one I check in the header. I need
to
match
the one that is in the header.

I have gone through about 4 iterations of this code, but this was my
last
try:
'note that these ProjectID's are Decimals (don't ask. designed by
another
vendor)
'this code is located in form activate and validates the detail,
when
the
CheckBox is true.

Set frm = Me.frmProjectIssue.Form 'the detail form
'Me is the header form
frm.Filter = "Me.[ProjectId].Value -
frm.[ProjectId].Value =
0"
'suggestion from another thread
frm.FilterOn = True

If frm.NewRecord Then
'no records found for filter 'OK-'suggestion from another thread

frm.Filter = ""
frmFilterOn = True

Else 'records were found for ProjectID in header
'NOT
ok

frm.Filter = ""
frmFilterOn = True
... <code to present msgbox and uncheck the header
checkbox>

Where am I going astray here and is there an easier way to check to
see
if
-any- matching (header ID = any detail) ID with the same value)?

 

[Graham Mandeno]

Hi Michael

Inline...

BELOW, IN CAPS:

YES

MAIN: PARENTPROJECTID, PROJECTID
SUB: PARENTPROJECTID, PROJECTID, ITEM
LINKED BY PARENT ONLY. BUT SINCE THEY ENTER THE PROJECTID IN THE SUB, I
HAVE TO VALIDATE/CONTROL IT.

When you say "linked by parent only" do you mean that both LinkMasterFields
and LinkChildFields are set to "ParentProjectId"?
If so, then you will only see the sibling records in the subform as
(presumably) a project cannot be the parent of itself.

TRUE, but the subform -is- linked to the master by Parent id only and the
detail table contains parent and project, so I do see what I want. The
objective is to read the checkbox, which should only be checked if there
were
NO ISSUES.

Is this checkbox bound to a field in your main form's recordsource, or is it
calculated?
What is its ControlSource?

You have given me the impression that your form and subform are bound to the
same table and that this table has a one-to-many self-relationship
(ParentProjectId to ProjectId). In which case, what are the "Issues"? Is
there another table involved here?

So, when I filter I need to check to see what the result was. If
it found any detail with the Project id in the header, then the checkbox
is
wrong and needs to be falsed.

I THINK I NEEDED TO JUST MATCH THE PROJECTID IN THE HEADER WITH ANY RECORD
WITH THAT PROJECTID IN THE DETAIL TO PROVE THE CHECKBOX WRONG. IF THERE
IS
NO RESULT, THEN THE CHECKBOX SHOULD BE TRUE.

I'm now getting the impression that the subform must be based on a query
comprising fields from two related tables - Projects and Issues - which are
related one-to-many. Am I correct?

Let's say you have a second table "Issues" with some fields such as:
IssueId (autonumber)
ProjectId (foreign key to Projects table)
IssueName (text)
IssueDescription (memo)
IssueResolution (memo)
IssueResolved (yes/no)

Now, you want your checkbox to be true is there are no records in the Issues
table which match the ProjectId in the main form, AND where IssueResolved is
not true.

You can set its ControlSource to:
=DCount("*", "Issues", "IssueResolved=0 and ProjectId="
& [ProjectId]) = 0

Does this help?

THAT WAS ANOTHER PART, THAT IF THEY ENTER A PROJECTID IN THE DETAIL, THAT
ISN'T THE IN THE SAME PARENTPROJECTID, FROM THE MASTER PROJECT TABLE, THEN
I
CANCEL AND UNDO THEIR INPUT

You mean the user is adding a new issue for a project, but you only want to
verify that this project shares a parent with the current "header" project?

In this case, use a combo box. In the main form's Form_Current procedure,
set its RowSource as follows:

Me.frmProjectIssue.Form.cboProjectId.RowSource = _
"Select ProjectId, ProjectName from Projects where ParentProjectId=" _
& Me.ParentProjectId & " order by ProjectName"

Bind the combo box to the ProjectId field in your subform.

Now, the user will be restricted to selecting one of a list of valid
projects.

 

[Guest]

the > BELOW, IN CAPS: was just referring to my reply would be in caps, just
in case it struck you as a real shout.
Ok, back to work.

I know, it's difficult to describe verbally.

GM:

I'm now getting the impression that the subform must be based on a query
comprising fields from two related tables - Projects and Issues - which are
related one-to-many. Am I correct?
MM:
Yes.

GM:
You mean the user is adding a new issue for a project, but you only want to
verify that this project shares a parent with the current "header" project?

MM:
Yes. When they load a project, they give me "the" project (not parent) they
want to work on. I bring it up with lots of info in the header as to who the
parent is and a list of all the siblings and their project names. I needed
to restrict them to only entering projectid's that are related to that
parent. That's done now.

GM:

In this case, use a combo box. In the main form's Form_Current procedure,
set its RowSource as follows...<which I know is below>

MM:
That does sound interesting and I see it takes care of the entering of valid
projectid's. Does that source query still allow insertion of new records in
the sub. I know some multitable queries don't allow new records or even some
editing?
If that query does show all siblings and allow editing and insertion of only
those related projectid's, then that's pretty terrific and I will have to try
it out.

GM:

Now, you want your checkbox to be true is there are no records in the Issues
table which match the ProjectId in the main form, AND where IssueResolved is
not true.

You can set its ControlSource to:
=DCount("*", "Issues", "IssueResolved=0 and ProjectId="
& [ProjectId]) = 0

Does this help?

MM:
That also sounds tasty. Slight clearification - the user checks the box
manually. They have to analyze that a project has no issues. If the box is
checked, I then have the code to validate it. And when the form is loaded, I
also had to have the code, cuz the checkmark doesn't stick. So. The user
checks it, and I uncheck it. So, I guess I can reverse the code sample.
But, does look good.

Let me do the homework. I'll get back to you.
Thanks for sticking with this. I also have the bulldog mentality.
--
MichaelM

Hi Michael

Inline...

BELOW, IN CAPS:

YES

MAIN: PARENTPROJECTID, PROJECTID
SUB: PARENTPROJECTID, PROJECTID, ITEM
LINKED BY PARENT ONLY. BUT SINCE THEY ENTER THE PROJECTID IN THE SUB, I
HAVE TO VALIDATE/CONTROL IT.

When you say "linked by parent only" do you mean that both LinkMasterFields
and LinkChildFields are set to "ParentProjectId"?
If so, then you will only see the sibling records in the subform as
(presumably) a project cannot be the parent of itself.

TRUE, but the subform -is- linked to the master by Parent id only and the
detail table contains parent and project, so I do see what I want. The
objective is to read the checkbox, which should only be checked if there
were
NO ISSUES.

Is this checkbox bound to a field in your main form's recordsource, or is it
calculated?
What is its ControlSource?

You have given me the impression that your form and subform are bound to the
same table and that this table has a one-to-many self-relationship
(ParentProjectId to ProjectId). In which case, what are the "Issues"? Is
there another table involved here?

So, when I filter I need to check to see what the result was. If
it found any detail with the Project id in the header, then the checkbox
is
wrong and needs to be falsed.

I THINK I NEEDED TO JUST MATCH THE PROJECTID IN THE HEADER WITH ANY RECORD
WITH THAT PROJECTID IN THE DETAIL TO PROVE THE CHECKBOX WRONG. IF THERE
IS
NO RESULT, THEN THE CHECKBOX SHOULD BE TRUE.

Let's say you have a second table "Issues" with some fields such as:
IssueId (autonumber)
ProjectId (foreign key to Projects table)
IssueName (text)
IssueDescription (memo)
IssueResolution (memo)
IssueResolved (yes/no)

THAT WAS ANOTHER PART, THAT IF THEY ENTER A PROJECTID IN THE DETAIL, THAT
ISN'T THE IN THE SAME PARENTPROJECTID, FROM THE MASTER PROJECT TABLE, THEN
I
CANCEL AND UNDO THEIR INPUT

In this case, use a combo box. In the main form's Form_Current procedure,
set its RowSource as follows:

Me.frmProjectIssue.Form.cboProjectId.RowSource = _
"Select ProjectId, ProjectName from Projects where ParentProjectId=" _
& Me.ParentProjectId & " order by ProjectName"

Bind the combo box to the ProjectId field in your subform.


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand