Getting an UnBound control value into a Table field??????

[Weebl]

I have a form that calculates a production rate in "parts per hour". This
form needs to be able to differentiate between Line work, Cutter work, Side
work, and Blister work.

I created a drop down list for selecting the "Study Type" as listed above. I
then created a text box for each "study type". When I select the "study type"
each text box checks to see if the selection applies to it and makes the
calculation if it does apply. The code for the "Line" study is as follows:

=IIf([StudyType]="Line",2700?/[SecondsPerPart])

I had to put this under Control Source to make it work, but now I can't
figure out how to get the information into the "Line" field of the underlying
table. I attempted to put this code under Default Value, but it had no affect.

I also tried having the "Study Type" drop down box assign the value under
After Update from a seperate calculated control and setting the Control
Source of the "Line" text box to the "Line" field of the underlying table. No
luck.

I'm at my wits end. Please help.

 

[Ken Snell]

You need to use the form's BeforeUpdate event to write the value of that
unbound control into the appropriate field in the form's recordset.

 

[KARL DEWEY]

It sounds like you have a separate field to store the value based on Type.
If this is so then you are not following proper database design.
You should one field to store the value and second for type.

You can use 'nested' IIF like this --
=IIf([StudyType]="Line", 2700/[SecondsPerPart], IIf([StudyType]="Cutter",
Some_Number/[SecondsPerPart], IIf([StudyType]="Side",
Some_Number/[SecondsPerPart], IIf([StudyType]="Blister",
Another_number/[SecondsPerPart], "Error"))))

 

[Jeff Boyce]

Another option would be to leave the controls bound to their underlying
fields, and use the AfterUpdate event of the combobox to correctly "fill"
the appropriate control.

On the other hand, if the calculation is ALWAYS the same, why bother storing
the calculated value? Just use a query to calculate on demand.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Weebl]

Karl,

I thought about doing like you sugested but, I need a seperate field for
each Study Type because the "Line" and "Cutter" type need to go as a pair,
yet seperate. I didn't want to inflate the database by having duplicate
records. So I created a control for each Study Type that is activated by the
choosing the Study Type from the combo box.

It sounds like you have a separate field to store the value based on Type.
If this is so then you are not following proper database design.
You should one field to store the value and second for type.

You can use 'nested' IIF like this --
=IIf([StudyType]="Line", 2700/[SecondsPerPart], IIf([StudyType]="Cutter",
Some_Number/[SecondsPerPart], IIf([StudyType]="Side",
Some_Number/[SecondsPerPart], IIf([StudyType]="Blister",
Another_number/[SecondsPerPart], "Error"))))

--
Build a little, test a little.

I have a form that calculates a production rate in "parts per hour". This
form needs to be able to differentiate between Line work, Cutter work, Side
work, and Blister work.

I created a drop down list for selecting the "Study Type" as listed above. I
then created a text box for each "study type". When I select the "study type"
each text box checks to see if the selection applies to it and makes the
calculation if it does apply. The code for the "Line" study is as follows:

=IIf([StudyType]="Line",2700?/[SecondsPerPart])

I had to put this under Control Source to make it work, but now I can't
figure out how to get the information into the "Line" field of the underlying
table. I attempted to put this code under Default Value, but it had no affect.

I also tried having the "Study Type" drop down box assign the value under
After Update from a seperate calculated control and setting the Control
Source of the "Line" text box to the "Line" field of the underlying table. No
luck.

I'm at my wits end. Please help.

 

[Weebl]

Ken,

This is basically what I've been trying to do. The problem is that I don't
know how. Can you point me in the right direction? I looked at the UPDATE
statement but I don't have any idea where to use it. I looked at the SetValue
Action, but couldn't get that to work either.

You need to use the form's BeforeUpdate event to write the value of that
unbound control into the appropriate field in the form's recordset.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

I have a form that calculates a production rate in "parts per hour". This
form needs to be able to differentiate between Line work, Cutter work,
Side
work, and Blister work.

I created a drop down list for selecting the "Study Type" as listed above.
I
then created a text box for each "study type". When I select the "study
type"
each text box checks to see if the selection applies to it and makes the
calculation if it does apply. The code for the "Line" study is as follows:

=IIf([StudyType]="Line",2700?/[SecondsPerPart])

I had to put this under Control Source to make it work, but now I can't
figure out how to get the information into the "Line" field of the
underlying
table. I attempted to put this code under Default Value, but it had no
affect.

I also tried having the "Study Type" drop down box assign the value under
After Update from a seperate calculated control and setting the Control
Source of the "Line" text box to the "Line" field of the underlying table.
No
luck.

I'm at my wits end. Please help.

.

 

[Weebl]

Jeff,

I tried to figure a way to do that as well. I used a statement like this:

=IIf([StudyType]="Line",[LRate]=[PartsPerHour],IIF([StudyType]="Cutter",[CRate]
=
[PartsPerHour],IIf([StudyType]="Side",[SRate]=[PartsPerHour],IIF([StudyType]="Blister",[BRate]=[PartsPerHour],0))))

This didn't fly either.

I gave a lot of consideration to the Query but couldn't find any information
on getting the result from the Query to the form. I am not sure I want to go
straight from the Query to the underlying Table. I would like to have all the
data in front of me on the Form so I can confirm all before saving the
information. Besides, I haven't been able to figure out how to go from the
Query to a Table either.

Thanks.

Another option would be to leave the controls bound to their underlying
fields, and use the AfterUpdate event of the combobox to correctly "fill"
the appropriate control.

On the other hand, if the calculation is ALWAYS the same, why bother storing
the calculated value? Just use a query to calculate on demand.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I have a form that calculates a production rate in "parts per hour". This
form needs to be able to differentiate between Line work, Cutter work,
Side
work, and Blister work.

I created a drop down list for selecting the "Study Type" as listed above.
I
then created a text box for each "study type". When I select the "study
type"
each text box checks to see if the selection applies to it and makes the
calculation if it does apply. The code for the "Line" study is as follows:

=IIf([StudyType]="Line",2700?/[SecondsPerPart])

I had to put this under Control Source to make it work, but now I can't
figure out how to get the information into the "Line" field of the
underlying
table. I attempted to put this code under Default Value, but it had no
affect.

I also tried having the "Study Type" drop down box assign the value under
After Update from a seperate calculated control and setting the Control
Source of the "Line" text box to the "Line" field of the underlying table.
No
luck.

I'm at my wits end. Please help.

.

 

[Jeff Boyce]

(see comments in-line below)

Jeff,

I tried to figure a way to do that as well. I used a statement like this:

=IIf([StudyType]="Line",[LRate]=[PartsPerHour],IIF([StudyType]="Cutter",[CRate]
=
[PartsPerHour],IIf([StudyType]="Side",[SRate]=[PartsPerHour],IIF([StudyType]="Blister",[BRate]=[PartsPerHour],0))))

This didn't fly either.

Where? Where did you use this? Is this part of a query? If so, please
post the SQL statement of that query. (By the way, you wouldn't use an
IIF() statement like this in a query. Instead, you'd use an IFF() statement
for EACH field, so you could set the value of each/all fields, with separate
IIF() statements). Again, please post the SQL.

I gave a lot of consideration to the Query but couldn't find any
information
on getting the result from the Query to the form.

?Where did you look? There's no special work needed to "get the result from
the query to the form". Simply base your form on your query (rather than
directly on a table).

I am not sure I want to go
straight from the Query to the underlying Table.

If I'm interpreting correctly, good! A query is NOT a user interface, a
form is.

< I would like to have all the

data in front of me on the Form so I can confirm all before saving the
information. Besides, I haven't been able to figure out how to go from the
Query to a Table either.

Open a new query in design view.

Add the table.

Add the fields.

Save the query.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Ken Snell]

Example code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.NameOfFieldToGetValue.Value = Me.NameOfControlWithValue.Value
End Sub

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Ken,

This is basically what I've been trying to do. The problem is that I don't
know how. Can you point me in the right direction? I looked at the UPDATE
statement but I don't have any idea where to use it. I looked at the
SetValue
Action, but couldn't get that to work either.

You need to use the form's BeforeUpdate event to write the value of that
unbound control into the appropriate field in the form's recordset.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

I have a form that calculates a production rate in "parts per hour".
This
form needs to be able to differentiate between Line work, Cutter work,
Side
work, and Blister work.

I created a drop down list for selecting the "Study Type" as listed
above.
I
then created a text box for each "study type". When I select the "study
type"
each text box checks to see if the selection applies to it and makes
the
calculation if it does apply. The code for the "Line" study is as
follows:

=IIf([StudyType]="Line",2700?/[SecondsPerPart])

I had to put this under Control Source to make it work, but now I can't
figure out how to get the information into the "Line" field of the
underlying
table. I attempted to put this code under Default Value, but it had no
affect.

I also tried having the "Study Type" drop down box assign the value
under
After Update from a seperate calculated control and setting the Control
Source of the "Line" text box to the "Line" field of the underlying
table.
No
luck.

I'm at my wits end. Please help.

.

 

[Weebl]

Ken,

Thanks for the reply. It works great, except I need to make it variable. I
need the "NameOfFieldToGetValue" to be set by the drop down list. This is
what I tried:

Private Sub StudyType_BeforeUpdate(Cancel As Integer)

Me.StudyType.Value = Me.PartsPerHour.Value

End Sub

The "Me" statement is seeing "StudyType" as a quoted string instead of a
variable. Is there a way to unquote the "StudyType" string? I looked for
"unquote", but no luck. I looked up "unquote a string" and found nothing.

Your continued patience with this is greatly appreciated.

Example code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.NameOfFieldToGetValue.Value = Me.NameOfControlWithValue.Value
End Sub

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Ken,

This is basically what I've been trying to do. The problem is that I don't
know how. Can you point me in the right direction? I looked at the UPDATE
statement but I don't have any idea where to use it. I looked at the
SetValue
Action, but couldn't get that to work either.

You need to use the form's BeforeUpdate event to write the value of that
unbound control into the appropriate field in the form's recordset.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


I have a form that calculates a production rate in "parts per hour".
This
form needs to be able to differentiate between Line work, Cutter work,
Side
work, and Blister work.

I created a drop down list for selecting the "Study Type" as listed
above.
I
then created a text box for each "study type". When I select the "study
type"
each text box checks to see if the selection applies to it and makes
the
calculation if it does apply. The code for the "Line" study is as
follows:

=IIf([StudyType]="Line",2700?/[SecondsPerPart])

I had to put this under Control Source to make it work, but now I can't
figure out how to get the information into the "Line" field of the
underlying
table. I attempted to put this code under Default Value, but it had no
affect.

I also tried having the "Study Type" drop down box assign the value
under
After Update from a seperate calculated control and setting the Control
Source of the "Line" text box to the "Line" field of the underlying
table.
No
luck.

I'm at my wits end. Please help.


.

.

 

[Ken Snell]

I'm confused... you're using the StudyType control's BeforeUpdate value to
set the value of the StudyType control? You cannot do that.

What specifically are you wanting to do? Which control has the value that is
to be written into which control? Which control has a "dynamic" value that
identifies the name of the control that is to get the value (and from where
does that value come)? Be very specific with names and desires, please.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Ken,

Thanks for the reply. It works great, except I need to make it variable. I
need the "NameOfFieldToGetValue" to be set by the drop down list. This is
what I tried:

Private Sub StudyType_BeforeUpdate(Cancel As Integer)

Me.StudyType.Value = Me.PartsPerHour.Value

End Sub

The "Me" statement is seeing "StudyType" as a quoted string instead of a
variable. Is there a way to unquote the "StudyType" string? I looked for
"unquote", but no luck. I looked up "unquote a string" and found nothing.

Your continued patience with this is greatly appreciated.

Example code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.NameOfFieldToGetValue.Value = Me.NameOfControlWithValue.Value
End Sub

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Ken,

This is basically what I've been trying to do. The problem is that I
don't
know how. Can you point me in the right direction? I looked at the
UPDATE
statement but I don't have any idea where to use it. I looked at the
SetValue
Action, but couldn't get that to work either.

:

You need to use the form's BeforeUpdate event to write the value of
that
unbound control into the appropriate field in the form's recordset.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


I have a form that calculates a production rate in "parts per hour".
This
form needs to be able to differentiate between Line work, Cutter
work,
Side
work, and Blister work.

I created a drop down list for selecting the "Study Type" as listed
above.
I
then created a text box for each "study type". When I select the
"study
type"
each text box checks to see if the selection applies to it and makes
the
calculation if it does apply. The code for the "Line" study is as
follows:

=IIf([StudyType]="Line",2700?/[SecondsPerPart])

I had to put this under Control Source to make it work, but now I
can't
figure out how to get the information into the "Line" field of the
underlying
table. I attempted to put this code under Default Value, but it had
no
affect.

I also tried having the "Study Type" drop down box assign the value
under
After Update from a seperate calculated control and setting the
Control
Source of the "Line" text box to the "Line" field of the underlying
table.
No
luck.

I'm at my wits end. Please help.


.

.

 

[Weebl]

Ken,

Sorry to be so confusing. Let me start from the beginning.

I have a table that contains a Part# and information specifically related to
that part. Production rates are part of that information. While about half of
the parts have only one production rate; (Side, or Blister,) the other half
have two; (Line and Cutter). This prevented me from using a field identifying
the rate type, i.e. Line, Cutter, Side, Blister. So I placed all four fields
in the table to allow the flexability that I would need.

I then created a Form based on this Table that is used to take the time
study data and calculate a production rate. I have a StudySize field, Minutes
& Seconds fields that are used by another field to calculate the total
seconds for the study. This is then broke down into SecondsPerPart, which is
then used to calculate how many PartsPerHour can be done.

I then have four controls, one each for the possible production rates;
(LRate, CRate, SRate, & BRate.) I used the StudyType combo box to identify
the production rate being calculated so the rate could then be entered into
the correct field of the table.

Placing an IIF() statement in the Control Source of the four rate controls
allowed me to send the PartsPerHour data to the correct control, but it did
not allow me to bind the control to the proper table field.

This would be an easy fix if I could simply have two fields in the table:
PartsPerHour and StudyType. However, due to the need for double studies on
half of the parts I can not do this.

So, in the end, I need to get the PartsPerHour data into the Table field
designated by the StudyType combo box.

I truly appreciate your working with me on this. Thank you.

I'm confused... you're using the StudyType control's BeforeUpdate value to
set the value of the StudyType control? You cannot do that.

What specifically are you wanting to do? Which control has the value that is
to be written into which control? Which control has a "dynamic" value that
identifies the name of the control that is to get the value (and from where
does that value come)? Be very specific with names and desires, please.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

Ken,

Thanks for the reply. It works great, except I need to make it variable. I
need the "NameOfFieldToGetValue" to be set by the drop down list. This is
what I tried:

Private Sub StudyType_BeforeUpdate(Cancel As Integer)

Me.StudyType.Value = Me.PartsPerHour.Value

End Sub

The "Me" statement is seeing "StudyType" as a quoted string instead of a
variable. Is there a way to unquote the "StudyType" string? I looked for
"unquote", but no luck. I looked up "unquote a string" and found nothing.

Your continued patience with this is greatly appreciated.

Example code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.NameOfFieldToGetValue.Value = Me.NameOfControlWithValue.Value
End Sub

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Ken,

This is basically what I've been trying to do. The problem is that I
don't
know how. Can you point me in the right direction? I looked at the
UPDATE
statement but I don't have any idea where to use it. I looked at the
SetValue
Action, but couldn't get that to work either.

:

You need to use the form's BeforeUpdate event to write the value of
that
unbound control into the appropriate field in the form's recordset.

--

Ken Snell
http://www.accessmvp.com/KDSnell/


I have a form that calculates a production rate in "parts per hour".
This
form needs to be able to differentiate between Line work, Cutter
work,
Side
work, and Blister work.

I created a drop down list for selecting the "Study Type" as listed
above.
I
then created a text box for each "study type". When I select the
"study
type"
each text box checks to see if the selection applies to it and makes
the
calculation if it does apply. The code for the "Line" study is as
follows:

=IIf([StudyType]="Line",2700?/[SecondsPerPart])

I had to put this under Control Source to make it work, but now I
can't
figure out how to get the information into the "Line" field of the
underlying
table. I attempted to put this code under Default Value, but it had
no
affect.

I also tried having the "Study Type" drop down box assign the value
under
After Update from a seperate calculated control and setting the
Control
Source of the "Line" text box to the "Line" field of the underlying
table.
No
luck.

I'm at my wits end. Please help.


.



.

.

 

[Weebl]

Jeff,

The formulae for calculating the parts per hour is not consistant from part
to part and therefore I couldn't use one query. The form I created will
calculate the rate based on the study information and then I will only need
to store one piece of information instead of two.

What I have is over 600 parts. About half of these only have one rate,
however the other half have two rates for two different processes. This
negates my ability to use one field for the rate and one for the Type of
rate. So I included all four fields in the Table: LRate, CRate, SRate, BRate
= the rate for Line, Cutter, Side, and Blister respectively.

The Form that actualy performs the calculations has a combo box, StudyType,
that is used to specify the Field to recieve the rate data as defined above.
I then have a control for each of the four fields. If I use an IIF() in the
Control Source for the control I can get the data into the proper control,
unfortuantely I can not get the data into the underlying table because I can
not figure out how to bind the control to the field and retain the IIF() that
evaluates the combo box identifying the type of rate.

I tried placing the IIF() under Default Value for the control and that
didn't work. I tried placing it under the various Event sections. Again, no
luck. I tried placing it withit the code for the SaveRecord button without
any success. I am not sure where to go next.

Your patience is greatly appreciated.

(see comments in-line below)

Jeff,

I tried to figure a way to do that as well. I used a statement like this:

=IIf([StudyType]="Line",[LRate]=[PartsPerHour],IIF([StudyType]="Cutter",[CRate]
=
[PartsPerHour],IIf([StudyType]="Side",[SRate]=[PartsPerHour],IIF([StudyType]="Blister",[BRate]=[PartsPerHour],0))))

This didn't fly either.

Where? Where did you use this? Is this part of a query? If so, please
post the SQL statement of that query. (By the way, you wouldn't use an
IIF() statement like this in a query. Instead, you'd use an IFF() statement
for EACH field, so you could set the value of each/all fields, with separate
IIF() statements). Again, please post the SQL.

I gave a lot of consideration to the Query but couldn't find any
information
on getting the result from the Query to the form.

?Where did you look? There's no special work needed to "get the result from
the query to the form". Simply base your form on your query (rather than
directly on a table).

I am not sure I want to go
straight from the Query to the underlying Table.

If I'm interpreting correctly, good! A query is NOT a user interface, a
form is.

< I would like to have all the

data in front of me on the Form so I can confirm all before saving the
information. Besides, I haven't been able to figure out how to go from the
Query to a Table either.

Open a new query in design view.

Add the table.

Add the fields.

Save the query.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Thanks.

.

 

[Jeff Boyce]

I may not be following your description ... but it sounds like you are
saying you already know the rates per part, per process.

If so, why not store that information in a table and use it (via a query) as
needed?

What am I still not seeing?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Jeff,

The formulae for calculating the parts per hour is not consistant from
part
to part and therefore I couldn't use one query. The form I created will
calculate the rate based on the study information and then I will only
need
to store one piece of information instead of two.

What I have is over 600 parts. About half of these only have one rate,
however the other half have two rates for two different processes. This
negates my ability to use one field for the rate and one for the Type of
rate. So I included all four fields in the Table: LRate, CRate, SRate,
BRate
= the rate for Line, Cutter, Side, and Blister respectively.

The Form that actualy performs the calculations has a combo box,
StudyType,
that is used to specify the Field to recieve the rate data as defined
above.
I then have a control for each of the four fields. If I use an IIF() in
the
Control Source for the control I can get the data into the proper control,
unfortuantely I can not get the data into the underlying table because I
can
not figure out how to bind the control to the field and retain the IIF()
that
evaluates the combo box identifying the type of rate.

I tried placing the IIF() under Default Value for the control and that
didn't work. I tried placing it under the various Event sections. Again,
no
luck. I tried placing it withit the code for the SaveRecord button without
any success. I am not sure where to go next.

Your patience is greatly appreciated.

(see comments in-line below)

Jeff,

I tried to figure a way to do that as well. I used a statement like
this:

=IIf([StudyType]="Line",[LRate]=[PartsPerHour],IIF([StudyType]="Cutter",[CRate]
=
[PartsPerHour],IIf([StudyType]="Side",[SRate]=[PartsPerHour],IIF([StudyType]="Blister",[BRate]=[PartsPerHour],0))))

This didn't fly either.

Where? Where did you use this? Is this part of a query? If so, please
post the SQL statement of that query. (By the way, you wouldn't use an
IIF() statement like this in a query. Instead, you'd use an IFF()
statement
for EACH field, so you could set the value of each/all fields, with
separate
IIF() statements). Again, please post the SQL.

I gave a lot of consideration to the Query but couldn't find any
information
on getting the result from the Query to the form.

?Where did you look? There's no special work needed to "get the result
from
the query to the form". Simply base your form on your query (rather than
directly on a table).

I am not sure I want to go
straight from the Query to the underlying Table.

If I'm interpreting correctly, good! A query is NOT a user interface, a
form is.

< I would like to have all the

data in front of me on the Form so I can confirm all before saving the
information. Besides, I haven't been able to figure out how to go from
the
Query to a Table either.

Open a new query in design view.

Add the table.

Add the fields.

Save the query.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Thanks.

.

 

[Weebl]

Jeff,

You are correct. My Form calculates the Rate for me. My problem is that I do
not know how to get the value from the calculated control to the Table I need
to store it in.

If I bind my control to the Table, I can't have the control calculate the
Rate, and if the control calculates the Rate, I can't have control bound to
the Table. Both of these things need to occur in the Control Source and I can
only do one of them.

So I am asking if there is a way to get the calculated value into the Table
even if the control is not bound to it?

I am sorry that I am not very adept at describing things more sensably. But
thanks for sticking with me on this.

I may not be following your description ... but it sounds like you are
saying you already know the rates per part, per process.

If so, why not store that information in a table and use it (via a query) as
needed?

What am I still not seeing?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Jeff,

The formulae for calculating the parts per hour is not consistant from
part
to part and therefore I couldn't use one query. The form I created will
calculate the rate based on the study information and then I will only
need
to store one piece of information instead of two.

What I have is over 600 parts. About half of these only have one rate,
however the other half have two rates for two different processes. This
negates my ability to use one field for the rate and one for the Type of
rate. So I included all four fields in the Table: LRate, CRate, SRate,
BRate
= the rate for Line, Cutter, Side, and Blister respectively.

The Form that actualy performs the calculations has a combo box,
StudyType,
that is used to specify the Field to recieve the rate data as defined
above.
I then have a control for each of the four fields. If I use an IIF() in
the
Control Source for the control I can get the data into the proper control,
unfortuantely I can not get the data into the underlying table because I
can
not figure out how to bind the control to the field and retain the IIF()
that
evaluates the combo box identifying the type of rate.

I tried placing the IIF() under Default Value for the control and that
didn't work. I tried placing it under the various Event sections. Again,
no
luck. I tried placing it withit the code for the SaveRecord button without
any success. I am not sure where to go next.

Your patience is greatly appreciated.

(see comments in-line below)

Jeff,

I tried to figure a way to do that as well. I used a statement like
this:

=IIf([StudyType]="Line",[LRate]=[PartsPerHour],IIF([StudyType]="Cutter",[CRate]
=
[PartsPerHour],IIf([StudyType]="Side",[SRate]=[PartsPerHour],IIF([StudyType]="Blister",[BRate]=[PartsPerHour],0))))

This didn't fly either.

Where? Where did you use this? Is this part of a query? If so, please
post the SQL statement of that query. (By the way, you wouldn't use an
IIF() statement like this in a query. Instead, you'd use an IFF()
statement
for EACH field, so you could set the value of each/all fields, with
separate
IIF() statements). Again, please post the SQL.


I gave a lot of consideration to the Query but couldn't find any
information
on getting the result from the Query to the form.

?Where did you look? There's no special work needed to "get the result
from
the query to the form". Simply base your form on your query (rather than
directly on a table).

I am not sure I want to go
straight from the Query to the underlying Table.

If I'm interpreting correctly, good! A query is NOT a user interface, a
form is.

< I would like to have all the
data in front of me on the Form so I can confirm all before saving the
information. Besides, I haven't been able to figure out how to go from
the
Query to a Table either.

Open a new query in design view.

Add the table.

Add the fields.

Save the query.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.



Thanks.


.

.

 

[Douglas J. Steele]

If the value can be calculated that easily, it shouldn't be stored in the
table.

As Jeff suggests, put the calculation into a query, and use the query
wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Jeff,

You are correct. My Form calculates the Rate for me. My problem is that I
do
not know how to get the value from the calculated control to the Table I
need
to store it in.

If I bind my control to the Table, I can't have the control calculate the
Rate, and if the control calculates the Rate, I can't have control bound
to
the Table. Both of these things need to occur in the Control Source and I
can
only do one of them.

So I am asking if there is a way to get the calculated value into the
Table
even if the control is not bound to it?

I am sorry that I am not very adept at describing things more sensably.
But
thanks for sticking with me on this.

I may not be following your description ... but it sounds like you are
saying you already know the rates per part, per process.

If so, why not store that information in a table and use it (via a query)
as
needed?

What am I still not seeing?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Jeff,

The formulae for calculating the parts per hour is not consistant from
part
to part and therefore I couldn't use one query. The form I created will
calculate the rate based on the study information and then I will only
need
to store one piece of information instead of two.

What I have is over 600 parts. About half of these only have one rate,
however the other half have two rates for two different processes. This
negates my ability to use one field for the rate and one for the Type
of
rate. So I included all four fields in the Table: LRate, CRate, SRate,
BRate
= the rate for Line, Cutter, Side, and Blister respectively.

The Form that actualy performs the calculations has a combo box,
StudyType,
that is used to specify the Field to recieve the rate data as defined
above.
I then have a control for each of the four fields. If I use an IIF() in
the
Control Source for the control I can get the data into the proper
control,
unfortuantely I can not get the data into the underlying table because
I
can
not figure out how to bind the control to the field and retain the
IIF()
that
evaluates the combo box identifying the type of rate.

I tried placing the IIF() under Default Value for the control and that
didn't work. I tried placing it under the various Event sections.
Again,
no
luck. I tried placing it withit the code for the SaveRecord button
without
any success. I am not sure where to go next.

Your patience is greatly appreciated.

:

(see comments in-line below)

Jeff,

I tried to figure a way to do that as well. I used a statement like
this:

=IIf([StudyType]="Line",[LRate]=[PartsPerHour],IIF([StudyType]="Cutter",[CRate]
=
[PartsPerHour],IIf([StudyType]="Side",[SRate]=[PartsPerHour],IIF([StudyType]="Blister",[BRate]=[PartsPerHour],0))))

This didn't fly either.

Where? Where did you use this? Is this part of a query? If so,
please
post the SQL statement of that query. (By the way, you wouldn't use
an
IIF() statement like this in a query. Instead, you'd use an IFF()
statement
for EACH field, so you could set the value of each/all fields, with
separate
IIF() statements). Again, please post the SQL.


I gave a lot of consideration to the Query but couldn't find any
information
on getting the result from the Query to the form.

?Where did you look? There's no special work needed to "get the
result
from
the query to the form". Simply base your form on your query (rather
than
directly on a table).

I am not sure I want to go
straight from the Query to the underlying Table.

If I'm interpreting correctly, good! A query is NOT a user interface,
a
form is.

< I would like to have all the
data in front of me on the Form so I can confirm all before saving
the
information. Besides, I haven't been able to figure out how to go
from
the
Query to a Table either.

Open a new query in design view.

Add the table.

Add the fields.

Save the query.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service
herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.



Thanks.


.

.

 

[Weebl]

Doug

Thanks for the reply. If the value is to be calculated everytime I need it,
I will need to store the number of parts in the test and the amount of time
it took to do the test. If I store the result I will only need to store one
piece of information and not two. So unfortunately, what would otherwise be
solid advise, just wont work this time.

If the value can be calculated that easily, it shouldn't be stored in the
table.

As Jeff suggests, put the calculation into a query, and use the query
wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Jeff,

You are correct. My Form calculates the Rate for me. My problem is that I
do
not know how to get the value from the calculated control to the Table I
need
to store it in.

If I bind my control to the Table, I can't have the control calculate the
Rate, and if the control calculates the Rate, I can't have control bound
to
the Table. Both of these things need to occur in the Control Source and I
can
only do one of them.

So I am asking if there is a way to get the calculated value into the
Table
even if the control is not bound to it?

I am sorry that I am not very adept at describing things more sensably.
But
thanks for sticking with me on this.

I may not be following your description ... but it sounds like you are
saying you already know the rates per part, per process.

If so, why not store that information in a table and use it (via a query)
as
needed?

What am I still not seeing?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Jeff,

The formulae for calculating the parts per hour is not consistant from
part
to part and therefore I couldn't use one query. The form I created will
calculate the rate based on the study information and then I will only
need
to store one piece of information instead of two.

What I have is over 600 parts. About half of these only have one rate,
however the other half have two rates for two different processes. This
negates my ability to use one field for the rate and one for the Type
of
rate. So I included all four fields in the Table: LRate, CRate, SRate,
BRate
= the rate for Line, Cutter, Side, and Blister respectively.

The Form that actualy performs the calculations has a combo box,
StudyType,
that is used to specify the Field to recieve the rate data as defined
above.
I then have a control for each of the four fields. If I use an IIF() in
the
Control Source for the control I can get the data into the proper
control,
unfortuantely I can not get the data into the underlying table because
I
can
not figure out how to bind the control to the field and retain the
IIF()
that
evaluates the combo box identifying the type of rate.

I tried placing the IIF() under Default Value for the control and that
didn't work. I tried placing it under the various Event sections.
Again,
no
luck. I tried placing it withit the code for the SaveRecord button
without
any success. I am not sure where to go next.

Your patience is greatly appreciated.

:

(see comments in-line below)

Jeff,

I tried to figure a way to do that as well. I used a statement like
this:

=IIf([StudyType]="Line",[LRate]=[PartsPerHour],IIF([StudyType]="Cutter",[CRate]
=
[PartsPerHour],IIf([StudyType]="Side",[SRate]=[PartsPerHour],IIF([StudyType]="Blister",[BRate]=[PartsPerHour],0))))

This didn't fly either.

Where? Where did you use this? Is this part of a query? If so,
please
post the SQL statement of that query. (By the way, you wouldn't use
an
IIF() statement like this in a query. Instead, you'd use an IFF()
statement
for EACH field, so you could set the value of each/all fields, with
separate
IIF() statements). Again, please post the SQL.


I gave a lot of consideration to the Query but couldn't find any
information
on getting the result from the Query to the form.

?Where did you look? There's no special work needed to "get the
result
from
the query to the form". Simply base your form on your query (rather
than
directly on a table).

I am not sure I want to go
straight from the Query to the underlying Table.

If I'm interpreting correctly, good! A query is NOT a user interface,
a
form is.

< I would like to have all the
data in front of me on the Form so I can confirm all before saving
the
information. Besides, I haven't been able to figure out how to go
from
the
Query to a Table either.

Open a new query in design view.

Add the table.

Add the fields.

Save the query.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services
mentioned
in this post. Mention and/or description of a product or service
herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with
no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.



Thanks.


.



.

.

 

[Weebl]

This is a huge thanks to everyone who tried to help me with this issue. I
used some of what was said by about everyone and combined it in some fasion
or another and was finally able to work it out.

For those who may want to know how to do this in the future, this is what I
did:

1. I created a calculated control to perform the needed calculation.
2. I bound the study type controls to the appropriate fields of the
underlying table.
3. I created an If, Then, Else statement that evaluated the combo box where
my study type was chosen from. It looked like this:

Public Sub StudyType_Change()

'Assign the production rate based on the study type chosen
'and set all other values to "0".
If [StudyType] = "LRate" Then
Me.LRate.Value = Me.PartsPerHour.Value
[CRate] = 0
[SRate] = 0
[BRate] = 0
ElseIf [StudyType] = "CRate" Then
Me.CRate.Value = Me.PartsPerHour.Value
[LRate] = 0
[SRate] = 0
[BRate] = 0
ElseIf [StudyType] = "SRate" Then
Me.SRate.Value = Me.PartsPerHour.Value
[CRate] = 0
[LRate] = 0
[BRate] = 0
ElseIf [StudyType] = "BRate" Then
Me.BRate.Value = Me.PartsPerHour.Value
[CRate] = 0
[SRate] = 0
[LRate] = 0
Else
MsgBox "Enter the rate desired."
[LRate] = 0
[CRate] = 0
[SRate] = 0
[BRate] = 0
End If

'Update the menu with the new values, (Refresh).
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

End Sub

Note: The final Else coresponds to a choice of "Other" on the combo box and
would give a certain amount of flexability to the user. Though at this time I
can not concieve of a practical use for it.

This sub was set through the "On Change" property of the combo box. Notice
the line just before the End Sub. I needed to put this in to auto refresh the
menu so the results would be visible.

I truely hope this helps others and again, my heart felt thanks for the
guidance, and patience from those who responded to my plight.

Weebl

I have a form that calculates a production rate in "parts per hour". This
form needs to be able to differentiate between Line work, Cutter work, Side
work, and Blister work.

I created a drop down list for selecting the "Study Type" as listed above. I
then created a text box for each "study type". When I select the "study type"
each text box checks to see if the selection applies to it and makes the
calculation if it does apply. The code for the "Line" study is as follows:

=IIf([StudyType]="Line",2700?/[SecondsPerPart])

I had to put this under Control Source to make it work, but now I can't
figure out how to get the information into the "Line" field of the underlying
table. I attempted to put this code under Default Value, but it had no affect.

I also tried having the "Study Type" drop down box assign the value under
After Update from a seperate calculated control and setting the Control
Source of the "Line" text box to the "Line" field of the underlying table. No
luck.

I'm at my wits end. Please help.

 

[Ken Snell]

It appears from your most recent post that you have resolved your question.
Post back if there are any lingering issues.