Adding several fields in a form and storing in table?

Hi,

I am having some trouble with recording a [Total] field on a form into my Table after formatting it to calculate several fields on the form.
If i just put the amount in the [Total] field and have it linked to the correct field in my Table, it will record it. But not the other
if I have the field Total several others first.

Where am I going wrong?

TIA TORQUE

re: Adding several fields in a form and storing in table?

"TORQUE" wrote
[color=blue]
> I am having some trouble with recording a
> [Total] field on a form into my Table after
> formatting it to calculate several fields on
> the form. If i just put the amount in the
> [Total] field and have it linked to the correct
> field in my Table, it will record it. But not
> the other if I have the field Total several
> others first.[/color]
[color=blue]
> Where am I going wrong?[/color]

If, as could be inferred from you post, you are storing a total along with
the values that are used to calculate it, your first "going wrong" is in
database design -- that is redundant, not normalized, and generally A Bad
Idea. On the other hand, if you are not going to store the factors, but only
keep track of the total in your DB, the design may be just fine.

Assuming the latter, what you have is called a calcuated Control, which
can't be bound to the data. But, what you can do is to include a Control on
your form, bound to the field where the daa is to be stored, with its
Visible property set to No. In the AfterUpdate event of any control that is
a factor in the calculation, you can copy the contents of the Calculated
Control into the invisible text box which will be stored/saved according to
the normal rules: when you move off the record to another, when you
explicitly request a save, when you close the form, and when you move from
the main form into a subform control.

Larry Linson
Microsoft Access MVP

re: Adding several fields in a form and storing in table?

On Fri, 10 Mar 2006 07:56:07 GMT, "Larry Linson" <bouncer@localhost.not> wrote:
[color=blue]
>"TORQUE" wrote
>[color=green]
> > I am having some trouble with recording a
> > [Total] field on a form into my Table after
> > formatting it to calculate several fields on
> > the form. If i just put the amount in the
> > [Total] field and have it linked to the correct
> > field in my Table, it will record it. But not
> > the other if I have the field Total several
> > others first.[/color]
>[color=green]
> > Where am I going wrong?[/color]
>
>If, as could be inferred from you post, you are storing a total along with
>the values that are used to calculate it, your first "going wrong" is in
>database design -- that is redundant, not normalized, and generally A Bad
>Idea. On the other hand, if you are not going to store the factors, but only
>keep track of the total in your DB, the design may be just fine.
>
>Assuming the latter, what you have is called a calcuated Control, which
>can't be bound to the data. But, what you can do is to include a Control on
>your form, bound to the field where the daa is to be stored, with its
>Visible property set to No. In the AfterUpdate event of any control that is
>a factor in the calculation, you can copy the contents of the Calculated
>Control into the invisible text box which will be stored/saved according to
>the normal rules: when you move off the record to another, when you
>explicitly request a save, when you close the form, and when you move from
>the main form into a subform control.
>
> Larry Linson
> Microsoft Access MVP
>[/color]

Hi Larry,
Thanks for you reply.

I have already created another field to do the calculations, and had it set to invisible, set the original [Total] field to look to it.
This part works fine, but it still doesn't save the resultof the [Total] field in my form, into my table. ????

Could you explain what you were talking about in your previous post?

Quote :

In the AfterUpdate event of any control that is
a factor in the calculation, you can copy the contents of the Calculated
Control into the invisible text box which will be stored/saved according to
the normal rules: when you move off the record to another, when you
explicitly request a save, when you close the form, and when you move from
the main form into a subform control.

I have no clue how to use the AfterUpdate, or any other events of the same type.

Thanks
Robert

re: Adding several fields in a form and storing in table?

Hi Torque

How are you doing the calculation for the value of the [Total]?
Somewhere along the line you have to have a final textbox in which you
put a value so that the value of [Total] can be calculated. In the
afterupdate event (Right-click textbox, Properties, event Tab, button
with 3 dots on the right of After update, code builder, ok) of the
final textbox you are going to put the following VBA:
Total.value = textbox1.value + textbox2.value * textbox3.value ...etc.
or whatever your calculation might be
How are you writing the data in the textbox to the table?

re: Adding several fields in a form and storing in table?

On 10 Mar 2006 13:37:57 -0800, "Hansen" <helgardh@hotmail.com> wrote:
[color=blue]
>Hi Torque
>
>How are you doing the calculation for the value of the [Total]?
>Somewhere along the line you have to have a final textbox in which you
>put a value so that the value of [Total] can be calculated. In the
>afterupdate event (Right-click textbox, Properties, event Tab, button
>with 3 dots on the right of After update, code builder, ok) of the
>final textbox you are going to put the following VBA:
>Total.value = textbox1.value + textbox2.value * textbox3.value ...etc.
>or whatever your calculation might be
>How are you writing the data in the textbox to the table?[/color]


Hi Hansen

This is how I am writing the data in the textbox.
=[Drop1Pay]+[Drop2Pay]+[Drop3Pay]+[Drop4Pay]+[UndeckPay]+[ReDeckPay]+[DelayPay1]+[DelayPay2]+[DelayPay3]+[LayoverPay]

This works fine for the calculation, but it will not store the answer into the table.
Is there another way to do this calculation and have the calculation stored into the table?

TIA

re: Adding several fields in a form and storing in table?

Hi Torque

Is there a button on your form to write the values entered to the
table? If not, I suggest that you put one on the form and add the
record using the on click event of the button:

Example:

Dim dbs as DataBase
Dim rst as RecordSet
Dim qry as String

Set dbs = opendatabase()
qry = "Tablewheredatashouldbeadded"
set rst = dbs.openrecordset(qry, dbopendynaset)
With rst
.Addnew
!fieldnameintable = total.value
!fieldnameintable2 = textbox2.value ...etc
.Update
.close
End with

re: Adding several fields in a form and storing in table?

I don't know if I am allowed to do this but a good example of where you
can study this is:
http://www.blueclaw-db.com/unbound_access_forms.htm

re: Adding several fields in a form and storing in table?

On 10 Mar 2006 22:42:29 -0800, "Hansen" <helgardh@hotmail.com> wrote:
[color=blue]
>Hi Torque
>
>Is there a button on your form to write the values entered to the
>table? If not, I suggest that you put one on the form and add the
>record using the on click event of the button:
>
>Example:
>
>Dim dbs as DataBase
>Dim rst as RecordSet
>Dim qry as String
>
>Set dbs = opendatabase()
>qry = "Tablewheredatashouldbeadded"
>set rst = dbs.openrecordset(qry, dbopendynaset)
>With rst
> .Addnew
> !fieldnameintable = total.value
> !fieldnameintable2 = textbox2.value ...etc
> .Update
> .close
>End with[/color]

************************************************** **
Hello again Hansen,

I have tried adding a button with the save record command, gone into the OnClick Event properties, this is what is there;

Private Sub Command153_Click()
On Error GoTo Err_Command153_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command153_Click:
Exit Sub

Err_Command153_Click:
MsgBox Err.Description
Resume Exit_Command153_Click

End Sub


I have tried many different variations of the example you gave me above and many times came up with errors. At times a lg box pops up
asking for a db or something. Other times no errors after clicking on the save button, but it does not record into table.

Maybe some info may help here.

my db name is newdb
the form im working in is named Table1
the table name is Table1
the 2 fields in particular im trying to record to Table1 are named, TripPay and TotalReimbursements


Im not real sure how to insert the info that you gave me above into the vb code screen.
If you want an idea of what im working with, I have posted 2 pics at this web address
http://home.carolina.rr.com/pcmed/

I have played around with access much in the past, but never had any instructions or help with it till lately.
I purchased a Training CD for Microsoft Access 2003 from http://www.vtc.com/. While this CD has been
very helpful, it is still not giving me some information that I am looking for. I do not work with Access on a
Professional Level, It's more for Personal fulfillment. I enjoy designing applications to help me in my
job field.

Thanks for your correspondence
Robert

re: Adding several fields in a form and storing in table?

Hi Torque

Does the Plus button on your form add all the data to Table1? How do
you record the other data on the form?
What you should do is replace the:

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

with: (and I don't know if what you named your textboxes)

Dim dbs as DataBase
Dim rst as RecordSet
Dim qry as String


Set dbs = opendatabase()
qry = "Table1"
set rst = dbs.openrecordset(qry, dbopendynaset)
With rst
.Addnew
!TripPay = trippay.value (if trippay is what the name of the
textbox is)
!TotalReimbursements = Taxi.Value + Tolls.Value + ATM.Value +
Fuel.Value + Misc.Value
.Update
.close
End with

There are numerous other considerations to take into account here.
Is the TripNumber unique or the Primary Key?
Are you checking if you are duplicating data?
Which record are you adding the totals to?

If you want to, Zip or rar your db and e-mail it to me and I will
change it and document for you how to change it.