Conditional programming

[Helen]

I'm trying to set a condition where a form will open only
if a partID that I'm entering into a form is available in
another form.
The form I'm working in is "Opportunities". The separate
form is "Plants using PartID". Once I enter a partID into
my opportunities form, I have a macro on AfterUpdate that
will automatically open the "Plants using PartID" form and
show me what plants are using the same PartID. HOWEVER, I
don't want anything to happen if there is no other plant
using that PartID. How do I set my condition in my macro
to NOT open the "plants" form if there aren't any other
plants using that part number?

Anyone? Anyone? Any ideas?
Thanks!!!

 

[Steve Schapel]

Helen,

I think this will work for your Condition...
DCount("*","YourQuery")>0
(... where YourQuery is the name of the query that the Plants Using
PartID form is based on.)

 

[Helen]

Hi Steve!
You are a king among men!! However, when I tried your
suggestion, it's still opening the form and not giving me
my MsgBox. Here's what I did:
Action: Open form where [partID] is on the opportunities
form (because I think it needs to open the form with the
query in the first place)

Condition: DCount ("Plant","Opportunities") is null (and I
tried "<1" and "<=1")
Action: MsgBox "No other plant is using this part number"

Condition: ...
Action: Close form "Plants using PartID"

Condition: DCount ("Plant","Opportunities") is not null
(and other variations of the same thing)
Action: Open form where partID = forms![Opportunities]!
[PartID]

I even tried to setvalue [Plant using partID].[invisible]
until it looks at the conditions, but that's not working,
either!

I've tried substituting DCount ("*") with PartID, with
Plant, and even with the *, but none of those work, either.

I've been stuck on this problem for about two weeks now,
and I'm about ready to throw this computer out the window
(do you think that would help?)

Thanks again, I can't tell you how much I appreciate your
help!
Helen

 

[Steve Schapel]

Helen,

I notice that of the various options you tried, is not included the
method I suggested in my previous reply. Any reason for this?

--
Steve Schapel, Microsoft Access MVP

Hi Steve!
You are a king among men!! However, when I tried your
suggestion, it's still opening the form and not giving me
my MsgBox. Here's what I did:
Action: Open form where [partID] is on the opportunities
form (because I think it needs to open the form with the
query in the first place)

Condition: DCount ("Plant","Opportunities") is null (and I
tried "<1" and "<=1")
Action: MsgBox "No other plant is using this part number"

Condition: ...
Action: Close form "Plants using PartID"

Condition: DCount ("Plant","Opportunities") is not null
(and other variations of the same thing)
Action: Open form where partID = forms![Opportunities]!
[PartID]

I even tried to setvalue [Plant using partID].[invisible]
until it looks at the conditions, but that's not working,
either!

I've tried substituting DCount ("*") with PartID, with
Plant, and even with the *, but none of those work, either.

I've been stuck on this problem for about two weeks now,
and I'm about ready to throw this computer out the window
(do you think that would help?)

Thanks again, I can't tell you how much I appreciate your
help!
Helen

-----Original Message-----
Helen,

I think this will work for your Condition...
DCount("*","YourQuery")>0
(... where YourQuery is the name of the query that the

Plants Using

PartID form is based on.)

--
Steve Schapel, Microsoft Access MVP

only

in

separate

into

that

and

HOWEVER, I

plant

macro


.

 

[Helen]

That was the first one that I tried, and since it didn't
work, I thought maybe the "*" meant something else, so I
tried everything I could think of to substitute the "*".
Do I not open the form in the first place? Do I set the
condition as you wrote it with the action being to open
the form? I think I tried that, and it didn't work.
Thanks again,
Helen

-----Original Message-----
Helen,

I notice that of the various options you tried, is not included the
method I suggested in my previous reply. Any reason for this?

--
Steve Schapel, Microsoft Access MVP

Hi Steve!
You are a king among men!! However, when I tried your
suggestion, it's still opening the form and not giving me
my MsgBox. Here's what I did:
Action: Open form where [partID] is on the opportunities
form (because I think it needs to open the form with the
query in the first place)

Condition: DCount ("Plant","Opportunities") is null (and I
tried "<1" and "<=1")
Action: MsgBox "No other plant is using this part number"

Condition: ...
Action: Close form "Plants using PartID"

Condition: DCount ("Plant","Opportunities") is not null
(and other variations of the same thing)
Action: Open form where partID = forms![Opportunities]!
[PartID]

I even tried to setvalue [Plant using partID]. [invisible]
until it looks at the conditions, but that's not working,
either!

I've tried substituting DCount ("*") with PartID, with
Plant, and even with the *, but none of those work, either.

I've been stuck on this problem for about two weeks now,
and I'm about ready to throw this computer out the window
(do you think that would help?)

Thanks again, I can't tell you how much I appreciate your
help!
Helen

-----Original Message-----
Helen,

I think this will work for your Condition...
DCount("*","YourQuery")>0
(... where YourQuery is the name of the query that the

Plants Using

PartID form is based on.)

--
Steve Schapel, Microsoft Access MVP

Helen wrote:

I'm trying to set a condition where a form will open
only

if a partID that I'm entering into a form is available
in

another form.
The form I'm working in is "Opportunities". The
separate

form is "Plants using PartID". Once I enter a partID
into

my opportunities form, I have a macro on AfterUpdate
that

will automatically open the "Plants using PartID" form
and

show me what plants are using the same PartID.

HOWEVER, I

don't want anything to happen if there is no other
plant

using that PartID. How do I set my condition in my
macro

to NOT open the "plants" form if there aren't any other
plants using that part number?

Anyone? Anyone? Any ideas?
Thanks!!!


.

.

 

[Steve Schapel]

Helen,

Ok, fair enough. I can't see why you want to open the form if there are
no records. I thought that was the whole idea. If you use the
expression I suggested...
DCount("*","YourQuery")>0
.... for the OpenForm action, it should open the form if there are
record, and not open the form if there are not records. What is the
name of the query that the second form is based on? Is the query called
Opportunities? And this query returns the records based on the ?PartID
of the current record on the first form, right? What is the name of the
second form? The first time you told me it was called Plants Using
PartID, and now it looks like you have changed this to Opportunities,
which is what I thought was the name of the first form. Well, if you
have changed the name of the form, has the macro been changed
accordingly? Or are you actually trying to open a second instance of
the first form? If so, this is a somewhat tricky procedure, and I would
suggest another approach. So when you tried my earlier suggestion, it
"didn't work", does this mean the form still opened when it shouldn't
have, or does it mean the form didn't open, even when it should have, or
does it mean you got an error?

--
Steve Schapel, Microsoft Access MVP

That was the first one that I tried, and since it didn't
work, I thought maybe the "*" meant something else, so I
tried everything I could think of to substitute the "*".
Do I not open the form in the first place? Do I set the
condition as you wrote it with the action being to open
the form? I think I tried that, and it didn't work.
Thanks again,
Helen

-----Original Message-----
Helen,

I notice that of the various options you tried, is not

included the

method I suggested in my previous reply. Any reason for
this?

--
Steve Schapel, Microsoft Access MVP

Hi Steve!
You are a king among men!! However, when I tried your
suggestion, it's still opening the form and not giving

me

my MsgBox. Here's what I did:
Action: Open form where [partID] is on the

opportunities

form (because I think it needs to open the form with

the

query in the first place)

Condition: DCount ("Plant","Opportunities") is null

(and I

tried "<1" and "<=1")
Action: MsgBox "No other plant is using this part

number"

Condition: ...
Action: Close form "Plants using PartID"

Condition: DCount ("Plant","Opportunities") is not null
(and other variations of the same thing)
Action: Open form where partID = forms![Opportunities]!
[PartID]

I even tried to setvalue [Plant using partID].

[invisible]

until it looks at the conditions, but that's not

working,

either!

I've tried substituting DCount ("*") with PartID, with
Plant, and even with the *, but none of those work,

either.

I've been stuck on this problem for about two weeks

now,

and I'm about ready to throw this computer out the

window

(do you think that would help?)

Thanks again, I can't tell you how much I appreciate

your

help!
Helen


-----Original Message-----
Helen,

I think this will work for your Condition...
DCount("*","YourQuery")>0
(... where YourQuery is the name of the query that the

Plants Using


PartID form is based on.)

--
Steve Schapel, Microsoft Access MVP

Helen wrote:


I'm trying to set a condition where a form will open

only


if a partID that I'm entering into a form is available

in


another form.
The form I'm working in is "Opportunities". The

separate


form is "Plants using PartID". Once I enter a partID

into


my opportunities form, I have a macro on AfterUpdate

that


will automatically open the "Plants using PartID" form

and


show me what plants are using the same PartID.

HOWEVER, I


don't want anything to happen if there is no other

plant


using that PartID. How do I set my condition in my

macro


to NOT open the "plants" form if there aren't any

other

plants using that part number?

Anyone? Anyone? Any ideas?
Thanks!!!


.

.

 

[Helen]

Steve,
Thank you so much for bearing with me. Here's what the
actual names are:
1. frm-Oppty (this is the main form where you enter new
opportunities), and this form runs off a table called
Opportunity.
2. OEMPartUsage (this is the form that shows which plants
are using which OEM part numbers. This form runs off a
query named Opportunities.

When I enter an OEM part number into the Opportunity form,
I only want to see the form OEMPartUsage for those plants
using that specific OEM part number. I think what was
happening with the DCount with the "*" was that it counted
all the plants with any OEM part numbers, then opening the
form with the part number I specified it to open with and
showing no records, not specifically the one I just
entered. If there aren't any plants currently using that
part number, then I don't want anything to happen, just
tab on to the next field in the frm-Oppty.

I figured I had to open the form OEMPartUsage where
[OEMPart]= [forms]![frm-Oppty]![OEMPart], and then do the
if DCount("*","Opportunities")>0. Which, if there are no
records in the form, it should close the form, and I
figured everything would happen so quickly, the screen
would just look like it blinked.

So I guess my next question is -- how do I get the macro
to determine whether that specific OEMPart number appears
within the Opportunities query, to only open the form
where the query contains the OEMPart in an existing
record, and to do nothing if that part number is brand
spanking new.

And if you could answer that, you will be my hero
forever. Or at least until I run into something else I
can't figure out!

Thanks again for your time and effort!
Helen

-----Original Message-----
Helen,

Ok, fair enough. I can't see why you want to open the form if there are
no records. I thought that was the whole idea. If you use the
expression I suggested...
DCount("*","YourQuery")>0
.... for the OpenForm action, it should open the form if there are
record, and not open the form if there are not records. What is the
name of the query that the second form is based on? Is the query called
Opportunities? And this query returns the records based on the ?PartID
of the current record on the first form, right? What is the name of the
second form? The first time you told me it was called Plants Using
PartID, and now it looks like you have changed this to Opportunities,
which is what I thought was the name of the first form. Well, if you
have changed the name of the form, has the macro been changed
accordingly? Or are you actually trying to open a second instance of
the first form? If so, this is a somewhat tricky procedure, and I would
suggest another approach. So when you tried my earlier suggestion, it
"didn't work", does this mean the form still opened when it shouldn't
have, or does it mean the form didn't open, even when it should have, or
does it mean you got an error?

--
Steve Schapel, Microsoft Access MVP

That was the first one that I tried, and since it didn't
work, I thought maybe the "*" meant something else, so I
tried everything I could think of to substitute the "*".
Do I not open the form in the first place? Do I set the
condition as you wrote it with the action being to open
the form? I think I tried that, and it didn't work.
Thanks again,
Helen

-----Original Message-----
Helen,

I notice that of the various options you tried, is not

included the

method I suggested in my previous reply. Any reason

for

this?

--
Steve Schapel, Microsoft Access MVP

Helen wrote:

Hi Steve!
You are a king among men!! However, when I tried your
suggestion, it's still opening the form and not giving
me

my MsgBox. Here's what I did:
Action: Open form where [partID] is on the
opportunities

form (because I think it needs to open the form with
the

query in the first place)

Condition: DCount ("Plant","Opportunities") is null

(and I

tried "<1" and "<=1")
Action: MsgBox "No other plant is using this part
number"

Condition: ...
Action: Close form "Plants using PartID"

Condition: DCount ("Plant","Opportunities") is not null
(and other variations of the same thing)
Action: Open form where partID = forms![Opportunities]!
[PartID]

I even tried to setvalue [Plant using partID].
[invisible]

until it looks at the conditions, but that's not
working,

either!

I've tried substituting DCount ("*") with PartID, with
Plant, and even with the *, but none of those work,
either.

I've been stuck on this problem for about two weeks
now,

and I'm about ready to throw this computer out the
window

(do you think that would help?)

Thanks again, I can't tell you how much I appreciate
your

help!
Helen


-----Original Message-----
Helen,

I think this will work for your Condition...
DCount("*","YourQuery")>0
(... where YourQuery is the name of the query that the

Plants Using


PartID form is based on.)

--
Steve Schapel, Microsoft Access MVP

Helen wrote:


I'm trying to set a condition where a form will open

only


if a partID that I'm entering into a form is available

in


another form.
The form I'm working in is "Opportunities". The

separate


form is "Plants using PartID". Once I enter a partID

into


my opportunities form, I have a macro on AfterUpdate

that


will automatically open the "Plants using PartID" form

and


show me what plants are using the same PartID.

HOWEVER, I


don't want anything to happen if there is no other

plant


using that PartID. How do I set my condition in my

macro


to NOT open the "plants" form if there aren't any
other

plants using that part number?

Anyone? Anyone? Any ideas?
Thanks!!!


.


.

.

 

[Steve Schapel]

Helen,

It is all becoming clearer now I confess I had made an assumption
which it now looks like is incorrect, namely that the Opportunities
query was using [Forms]![frm-Oppty]![OEMPart] in its criteria, so it
would only ever be returning the records you need for the OEMPartUsage
form. It now seems you are filtering the records for the OEMPartUsage
form via the Where Condition argument of the OpenForm macro action,
right? So, I never thought of this possibility, and so I was advising
based on insufficient information! So, in this case, if you only want
the OEMPartUsage form to open if there are matching records, the
Condition will also need to reference the value of the OEMPart field, so
something like...
DCount("*","Opportunities","[OEMPart]=[forms]![frm-Oppty]![OEMPart]")>0
or...
DCount("*","Opportunities","[OEMPart]='" & [OEMPart] & "'")>0
(assumes OEMPart is a Text data type).

Let us know how you get on.

 

[Helen]

YOU ARE MY HERO!!!!!!!!!!!!!!!!!!!!!
THANK YOU, THANK YOU, THANK YOU, THANK YOU!!!!!!!

-----Original Message-----
Helen,

It is all becoming clearer now I confess I had made an assumption
which it now looks like is incorrect, namely that the Opportunities
query was using [Forms]![frm-Oppty]![OEMPart] in its criteria, so it
would only ever be returning the records you need for the OEMPartUsage
form. It now seems you are filtering the records for the OEMPartUsage
form via the Where Condition argument of the OpenForm macro action,
right? So, I never thought of this possibility, and so I was advising
based on insufficient information! So, in this case, if you only want
the OEMPartUsage form to open if there are matching records, the
Condition will also need to reference the value of the OEMPart field, so
something like...
DCount("*","Opportunities","[OEMPart]=[forms]![frm-Oppty]! [OEMPart]")>0
or...
DCount("*","Opportunities","[OEMPart]='" & [OEMPart] & "'")>0
(assumes OEMPart is a Text data type).

Let us know how you get on.

--
Steve Schapel, Microsoft Access MVP

Steve,
Thank you so much for bearing with me. Here's what the
actual names are:
1. frm-Oppty (this is the main form where you enter new
opportunities), and this form runs off a table called
Opportunity.
2. OEMPartUsage (this is the form that shows which plants
are using which OEM part numbers. This form runs off a
query named Opportunities.

When I enter an OEM part number into the Opportunity form,
I only want to see the form OEMPartUsage for those plants
using that specific OEM part number. I think what was
happening with the DCount with the "*" was that it counted
all the plants with any OEM part numbers, then opening the
form with the part number I specified it to open with and
showing no records, not specifically the one I just
entered. If there aren't any plants currently using that
part number, then I don't want anything to happen, just
tab on to the next field in the frm-Oppty.

I figured I had to open the form OEMPartUsage where
[OEMPart]= [forms]![frm-Oppty]![OEMPart], and then do the
if DCount("*","Opportunities")>0. Which, if there are no
records in the form, it should close the form, and I
figured everything would happen so quickly, the screen
would just look like it blinked.

So I guess my next question is -- how do I get the macro
to determine whether that specific OEMPart number appears
within the Opportunities query, to only open the form
where the query contains the OEMPart in an existing
record, and to do nothing if that part number is brand
spanking new.

And if you could answer that, you will be my hero
forever. Or at least until I run into something else I
can't figure out!

Thanks again for your time and effort!
Helen

.