Should the Choose Option value be stored?

[Guest]

I have 2 option buttons: "In Months" = option=1 and "In Weeks"=option=2.
I'm building reports and now need the value of this option button that is in
the main form to determine whether my CalcEndDate will use the month or week
values. Currently the enddate on my main form is in the
Row Source under property tab =
CalcEndDate([optChoose],[term_mos],[acceptance_date]). The case calc is in
a function under Module1.

I'm building a query so that all fields in the report will be available.
Currently, I'm being prompted for the optChoose number. And then a
processing date. How do I eliminate the need to be prompted for the
optChoose number in my query and ultimately my report?

Thank you!!!

 

[Guest]

I changed calc to the following in 2 places:

1) in RowSource Property where the enddate is being calcd on the form =
CalcEndDate=(Forms![frmContract].[optChoose],[term_mos],[acceptance_date])
This is the only spot that does not prompt for the option button value.
2) in my query end_date I used the Build expression:
End_date:
CalcEndDate(Forms![frmContract].[optChoose],[term_mos],[acceptance_date])
When this query is run, it still prompts for the option button value.

Am I missing syntax somewhere else?

Thank you!

I have 2 option buttons: "In Months" = option=1 and "In Weeks"=option=2.
I'm building reports and now need the value of this option button that is in
the main form to determine whether my CalcEndDate will use the month or week
values. Currently the enddate on my main form is in the
Row Source under property tab =
CalcEndDate([optChoose],[term_mos],[acceptance_date]). The case calc is in
a function under Module1.

I'm building a query so that all fields in the report will be available.
Currently, I'm being prompted for the optChoose number. And then a
processing date. How do I eliminate the need to be prompted for the
optChoose number in my query and ultimately my report?

You need to tell the query which form this option frame is
on.

CalcEndDate(Forms!nameofform.optChoose,[term_mos],[acceptance_date])

You might have been misled on this because when you used
that expression in a text box's ControlSource expression, it
assumed the option frame was on the same form as the text
vbox.

 

[Marshall Barton]

The syntax in the query looks ok to me. Double check the
option frame to make sure it it is named optChoose. Note
that option buttons inside an option frame is different from
having option buttons placed directly on the form. You have
never made this distinction in your posts so I want to
verify what we are talking about.

As long as the end date text box is on the same form as the
optChoose Frame control, you did not need to change the
Control Source (not Row Source) expression. Note that the
expression you posted has an extra = sign after CalcEndDate
(which I assume is the name of a Public function in a
standard module).

If you are sitll having trouble with this, please confirm or
refute my assumptions.
--
Marsh
MVP [MS Access]

I changed calc to the following in 2 places:

1) in RowSource Property where the enddate is being calcd on the form =
CalcEndDate=(Forms![frmContract].[optChoose],[term_mos],[acceptance_date])
This is the only spot that does not prompt for the option button value.
2) in my query end_date I used the Build expression:
End_date:
CalcEndDate(Forms![frmContract].[optChoose],[term_mos],[acceptance_date])
When this query is run, it still prompts for the option button value.

I have 2 option buttons: "In Months" = option=1 and "In Weeks"=option=2.
I'm building reports and now need the value of this option button that is in
the main form to determine whether my CalcEndDate will use the month or week
values. Currently the enddate on my main form is in the
Row Source under property tab =
CalcEndDate([optChoose],[term_mos],[acceptance_date]). The case calc is in
a function under Module1.

I'm building a query so that all fields in the report will be available.
Currently, I'm being prompted for the optChoose number. And then a
processing date. How do I eliminate the need to be prompted for the
optChoose number in my query and ultimately my report?

You need to tell the query which form this option frame is
on.

CalcEndDate(Forms!nameofform.optChoose,[term_mos],[acceptance_date])

You might have been misled on this because when you used
that expression in a text box's ControlSource expression, it
assumed the option frame was on the same form as the text
vbox.

 

[Guest]

It is still prompting me for the optChoose value. It should not, correct?
When it does, and I enter 1, then it works. Yes, you were right, it was in
the Control Source not Row Source, but I removed the form name there. When
you mention the optChoose Frame control, under the Module1, I have:

Public Function CalcEndDate(optionframe As Integer, term As Integer, start
As Date)

should I use optChoose instead of optionframe as integer?

Thanks for your patience.

The syntax in the query looks ok to me. Double check the
option frame to make sure it it is named optChoose. Note
that option buttons inside an option frame is different from
having option buttons placed directly on the form. You have
never made this distinction in your posts so I want to
verify what we are talking about.

As long as the end date text box is on the same form as the
optChoose Frame control, you did not need to change the
Control Source (not Row Source) expression. Note that the
expression you posted has an extra = sign after CalcEndDate
(which I assume is the name of a Public function in a
standard module).

If you are sitll having trouble with this, please confirm or
refute my assumptions.
--
Marsh
MVP [MS Access]

I changed calc to the following in 2 places:

1) in RowSource Property where the enddate is being calcd on the form =
CalcEndDate=(Forms![frmContract].[optChoose],[term_mos],[acceptance_date])
This is the only spot that does not prompt for the option button value.
2) in my query end_date I used the Build expression:
End_date:
CalcEndDate(Forms![frmContract].[optChoose],[term_mos],[acceptance_date])
When this query is run, it still prompts for the option button value.

gg wrote:
I have 2 option buttons: "In Months" = option=1 and "In Weeks"=option=2.
I'm building reports and now need the value of this option button that is in
the main form to determine whether my CalcEndDate will use the month or week
values. Currently the enddate on my main form is in the
Row Source under property tab =
CalcEndDate([optChoose],[term_mos],[acceptance_date]). The case calc is in
a function under Module1.

I'm building a query so that all fields in the report will be available.
Currently, I'm being prompted for the optChoose number. And then a
processing date. How do I eliminate the need to be prompted for the
optChoose number in my query and ultimately my report?

You need to tell the query which form this option frame is
on.

CalcEndDate(Forms!nameofform.optChoose,[term_mos],[acceptance_date])

You might have been misled on this because when you used
that expression in a text box's ControlSource expression, it
assumed the option frame was on the same form as the text
vbox.

 

[Guest]

Hi Marsh,

I looked at the option frame and the entire box (contains the 2 option
buttons) under properties is:

Name=optChoose
Control Source=Term_type
Default value=1

If I click on the actual option button, I have:
Name=opt1
Option value=1

thank you.

It is still prompting me for the optChoose value. It should not, correct?
When it does, and I enter 1, then it works. Yes, you were right, it was in
the Control Source not Row Source, but I removed the form name there. When
you mention the optChoose Frame control, under the Module1, I have:

Public Function CalcEndDate(optionframe As Integer, term As Integer, start
As Date)

should I use optChoose instead of optionframe as integer?

Thanks for your patience.

The syntax in the query looks ok to me. Double check the
option frame to make sure it it is named optChoose. Note
that option buttons inside an option frame is different from
having option buttons placed directly on the form. You have
never made this distinction in your posts so I want to
verify what we are talking about.

As long as the end date text box is on the same form as the
optChoose Frame control, you did not need to change the
Control Source (not Row Source) expression. Note that the
expression you posted has an extra = sign after CalcEndDate
(which I assume is the name of a Public function in a
standard module).

If you are sitll having trouble with this, please confirm or
refute my assumptions.
--
Marsh
MVP [MS Access]

I changed calc to the following in 2 places:

1) in RowSource Property where the enddate is being calcd on the form =
CalcEndDate=(Forms![frmContract].[optChoose],[term_mos],[acceptance_date])
This is the only spot that does not prompt for the option button value.
2) in my query end_date I used the Build expression:
End_date:
CalcEndDate(Forms![frmContract].[optChoose],[term_mos],[acceptance_date])
When this query is run, it still prompts for the option button value.


gg wrote:
I have 2 option buttons: "In Months" = option=1 and "In Weeks"=option=2.
I'm building reports and now need the value of this option button that is in
the main form to determine whether my CalcEndDate will use the month or week
values. Currently the enddate on my main form is in the
Row Source under property tab =
CalcEndDate([optChoose],[term_mos],[acceptance_date]). The case calc is in
a function under Module1.

I'm building a query so that all fields in the report will be available.
Currently, I'm being prompted for the optChoose number. And then a
processing date. How do I eliminate the need to be prompted for the
optChoose number in my query and ultimately my report?


:
You need to tell the query which form this option frame is
on.

CalcEndDate(Forms!nameofform.optChoose,[term_mos],[acceptance_date])

You might have been misled on this because when you used
that expression in a text box's ControlSource expression, it
assumed the option frame was on the same form as the text
vbox.

 

[Marshall Barton]

OK, that's what I thought and it all looks corrrect. But
wait a minute, the form is working as desired, right? The
question is about doing the calculation in the query for a
report. In that case, I don't see why you are trying to
refer to the form at all. You're probably getting the
prompt because the form is not open. But, I think you
really want to calculate the end date based on the value in
each record in the table, not a value on the form.

If all that is correct, the end date field in the query
should be:

End_date:
CalcEndDate(Term_type,[term_mos],[acceptance_date])
--
Marsh
MVP [MS Access]

I looked at the option frame and the entire box (contains the 2 option
buttons) under properties is:

Name=optChoose
Control Source=Term_type
Default value=1

If I click on the actual option button, I have:
Name=opt1
Option value=1

It is still prompting me for the optChoose value. It should not, correct?
When it does, and I enter 1, then it works. Yes, you were right, it was in
the Control Source not Row Source, but I removed the form name there. When
you mention the optChoose Frame control, under the Module1, I have:

Public Function CalcEndDate(optionframe As Integer, term As Integer, start
As Date)

should I use optChoose instead of optionframe as integer?

The syntax in the query looks ok to me. Double check the
option frame to make sure it it is named optChoose. Note
that option buttons inside an option frame is different from
having option buttons placed directly on the form. You have
never made this distinction in your posts so I want to
verify what we are talking about.

As long as the end date text box is on the same form as the
optChoose Frame control, you did not need to change the
Control Source (not Row Source) expression. Note that the
expression you posted has an extra = sign after CalcEndDate
(which I assume is the name of a Public function in a
standard module).

If you are sitll having trouble with this, please confirm or
refute my assumptions.


gg wrote:
I changed calc to the following in 2 places:

1) in RowSource Property where the enddate is being calcd on the form =
CalcEndDate=(Forms![frmContract].[optChoose],[term_mos],[acceptance_date])
This is the only spot that does not prompt for the option button value.
2) in my query end_date I used the Build expression:
End_date:
CalcEndDate(Forms![frmContract].[optChoose],[term_mos],[acceptance_date])
When this query is run, it still prompts for the option button value.


gg wrote:
I have 2 option buttons: "In Months" = option=1 and "In Weeks"=option=2.
I'm building reports and now need the value of this option button that is in
the main form to determine whether my CalcEndDate will use the month or week
values. Currently the enddate on my main form is in the
Row Source under property tab =
CalcEndDate([optChoose],[term_mos],[acceptance_date]). The case calc is in
a function under Module1.

I'm building a query so that all fields in the report will be available.
Currently, I'm being prompted for the optChoose number. And then a
processing date. How do I eliminate the need to be prompted for the
optChoose number in my query and ultimately my report?


:
You need to tell the query which form this option frame is
on.

CalcEndDate(Forms!nameofform.optChoose,[term_mos],[acceptance_date])

You might have been misled on this because when you used
that expression in a text box's ControlSource expression, it
assumed the option frame was on the same form as the text
vbox.

 

[Guest]

Marshall, That was it. I wasn't using the table field, term_type. It
worked now.

Thanks for checking back on this. Appreciate your help.

gg

:

OK, that's what I thought and it all looks corrrect. But

wait a minute, the form is working as desired, right? The
question is about doing the calculation in the query for a
report. In that case, I don't see why you are trying to
refer to the form at all. You're probably getting the
prompt because the form is not open. But, I think you
really want to calculate the end date based on the value in
each record in the table, not a value on the form.

If all that is correct, the end date field in the query
should be:

End_date:
CalcEndDate(Term_type,[term_mos],[acceptance_date])
--
Marsh
MVP [MS Access]

I looked at the option frame and the entire box (contains the 2 option
buttons) under properties is:

Name=optChoose
Control Source=Term_type
Default value=1

If I click on the actual option button, I have:
Name=opt1
Option value=1

It is still prompting me for the optChoose value. It should not, correct?
When it does, and I enter 1, then it works. Yes, you were right, it was in
the Control Source not Row Source, but I removed the form name there. When
you mention the optChoose Frame control, under the Module1, I have:

Public Function CalcEndDate(optionframe As Integer, term As Integer, start
As Date)

should I use optChoose instead of optionframe as integer?


:
The syntax in the query looks ok to me. Double check the
option frame to make sure it it is named optChoose. Note
that option buttons inside an option frame is different from
having option buttons placed directly on the form. You have
never made this distinction in your posts so I want to
verify what we are talking about.

As long as the end date text box is on the same form as the
optChoose Frame control, you did not need to change the
Control Source (not Row Source) expression. Note that the
expression you posted has an extra = sign after CalcEndDate
(which I assume is the name of a Public function in a
standard module).

If you are sitll having trouble with this, please confirm or
refute my assumptions.


gg wrote:
I changed calc to the following in 2 places:

1) in RowSource Property where the enddate is being calcd on the form =
CalcEndDate=(Forms![frmContract].[optChoose],[term_mos],[acceptance_date])
This is the only spot that does not prompt for the option button value.
2) in my query end_date I used the Build expression:
End_date:
CalcEndDate(Forms![frmContract].[optChoose],[term_mos],[acceptance_date])
When this query is run, it still prompts for the option button value.


gg wrote:
I have 2 option buttons: "In Months" = option=1 and "In Weeks"=option=2.
I'm building reports and now need the value of this option button that is in
the main form to determine whether my CalcEndDate will use the month or week
values. Currently the enddate on my main form is in the
Row Source under property tab =
CalcEndDate([optChoose],[term_mos],[acceptance_date]). The case calc is in
a function under Module1.

I'm building a query so that all fields in the report will be available.
Currently, I'm being prompted for the optChoose number. And then a
processing date. How do I eliminate the need to be prompted for the
optChoose number in my query and ultimately my report?


:
You need to tell the query which form this option frame is
on.

CalcEndDate(Forms!nameofform.optChoose,[term_mos],[acceptance_date])

You might have been misled on this because when you used
that expression in a text box's ControlSource expression, it
assumed the option frame was on the same form as the text
vbox.