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

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.

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/


"Weebl" wrote in message
...
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.

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.


"Weebl" wrote:

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.

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" wrote in message
...
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.

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.

"KARL DEWEY" wrote:

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.


"Weebl" wrote:

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,

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.

"Ken Snell" wrote:

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/


"Weebl" wrote in message
...
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,

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.

"Jeff Boyce" wrote:

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" wrote in message
...
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.


.

(see comments in-line below)

"Weebl" wrote in message
...
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.

Example code:

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

--

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



"Weebl" wrote in message
...
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.

"Ken Snell" wrote:

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/


"Weebl" wrote in message
...
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,

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.

"Ken Snell" wrote:

Example code:

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

--

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



"Weebl" wrote in message
...
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.

"Ken Snell" wrote:

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/


"Weebl" wrote in message
...
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.


.



.