Data from a form to a table??

[ovim]

I'm trying to teach myself Access, but so far its not going so great.

I've created a database with a few tables in it.

Now Im trying to make a form with which i can update my tables with data on
Customer, products purchased, price etc.

My form has a subform in tabular form with combobox from which you can select
products, a box telling the price, a box to add the quantity and field which
calculates the price for one row. in the footer of the subform I have a box
which calculates the sum of all prices.

On my mainform I have a box which refers to the totalprice box in the footer
of the subform. How can I get the totalprice to be updated to my sales table?
I have the reference to the subform as the control source.

 

[fredg]

I'm trying to teach myself Access, but so far its not going so great.

I've created a database with a few tables in it.

Now Im trying to make a form with which i can update my tables with data on
Customer, products purchased, price etc.

My form has a subform in tabular form with combobox from which you can select
products, a box telling the price, a box to add the quantity and field which
calculates the price for one row. in the footer of the subform I have a box
which calculates the sum of all prices.

On my mainform I have a box which refers to the totalprice box in the footer
of the subform. How can I get the totalprice to be updated to my sales table?
I have the reference to the subform as the control source.

You don't! Nor is there any need to.
Storing calculated data wastes space and time and is subject to error
if one of the underlying data fields has subsequently been changed.
Any time you need the Totals simply compute them, as you already do,
on your form, in a report, or in a query. But do not store that
calculated data in your table.
Think Access database.... not Excel spreadsheet.

 

[ovim]

I'm trying to teach myself Access, but so far its not going so great.

[quoted text clipped - 11 lines]

of the subform. How can I get the totalprice to be updated to my sales table?
I have the reference to the subform as the control source.

You don't! Nor is there any need to.
Storing calculated data wastes space and time and is subject to error
if one of the underlying data fields has subsequently been changed.
Any time you need the Totals simply compute them, as you already do,
on your form, in a report, or in a query. But do not store that
calculated data in your table.
Think Access database.... not Excel spreadsheet.

Ok, thats what I've read elsewhere. What if I have a product with a certain
price, and that price changes and I update the new price to the products
table. After that calculations (regarding sales history with that product)
will give wrong total price. Whats the best course of action to get around
that?

btw Thank you for your quick reply.

 

[John Vinson]

Ok, thats what I've read elsewhere. What if I have a product with a certain
price, and that price changes and I update the new price to the products
table. After that calculations (regarding sales history with that product)
will give wrong total price. Whats the best course of action to get around
that?

THere, you're correct. It's not a violation of normal form, since the
price is the "price at a specific point in time".

Use the AfterUpdate event of whatever control determines the price to
"push" the price into a bound control. E.g. if you have a ProductID
combo box, containing the price as one of the fields in the combo, use

Me.txtSalePrice = Me.cboProductID.Column(2)

to push the third field in the product combo (it's zero based) into
the textbox txtSalesPrice.

John W. Vinson[MVP]

 

[ovim via AccessMonster.com]

THere, you're correct. It's not a violation of normal form, since the
price is the "price at a specific point in time".

Use the AfterUpdate event of whatever control determines the price to
"push" the price into a bound control. E.g. if you have a ProductID
combo box, containing the price as one of the fields in the combo, use

Me.txtSalePrice = Me.cboProductID.Column(2)

to push the third field in the product combo (it's zero based) into
the textbox txtSalesPrice.

John W. Vinson[MVP]

Ok, I understand but I cant get it to work.

Did I understand right?

I put "Me.SubFTxtbox = Me.MainFTxtbox" in the afterupdate property of my
(subform) txt box. And that should copy the sum from the (subform)txtbox to
(mainform)txtbox after the sum has been calculated into the (subform)txtbox?
Or should there be Forms!Formname... or something because Im referring form
subform to mainform.

 

[John Vinson]

Did I understand right?

I put "Me.SubFTxtbox = Me.MainFTxtbox" in the afterupdate property of my
(subform) txt box. And that should copy the sum from the (subform)txtbox to
(mainform)txtbox after the sum has been calculated into the (subform)txtbox?
Or should there be Forms!Formname... or something because Im referring form
subform to mainform.

If you put the statement directly into the textbox property - that's
the wrong place. If the Subform textbox is a calculated field, its
AfterUpdate event will never fire.

I'd use the Subform's AfterUpdate event instead. Click the ... icon,
invoke the code builder, and put something like

Private Sub Form_AfterUpdate()
Parent!MainFTxtbox = Me.SubFTxtbox
End Sub

This will copy whatever is in this form (the subform's) control named
SubFTxtbox into the parent (main) form's MainFTxtbox.

John W. Vinson[MVP]

 

[ovim via AccessMonster.com]

Did I understand right?

[quoted text clipped - 3 lines]

Or should there be Forms!Formname... or something because Im referring form
subform to mainform.

If you put the statement directly into the textbox property - that's
the wrong place. If the Subform textbox is a calculated field, its
AfterUpdate event will never fire.

I'd use the Subform's AfterUpdate event instead. Click the ... icon,
invoke the code builder, and put something like

Private Sub Form_AfterUpdate()
Parent!MainFTxtbox = Me.SubFTxtbox
End Sub

This will copy whatever is in this form (the subform's) control named
SubFTxtbox into the parent (main) form's MainFTxtbox.

John W. Vinson[MVP]

I did what you said and got it ot work (well sort of), the only problem was
that the price the "Sub Form_AfterUpdate()" copied, was the price before it
was calculated.

So if it was a new row it copied nothing and if it was a row with a price
already in it and I changed the quantity to change the price it copied the
price before recalculation to the field on the parentform?

After that I did something (but Im not sure what?) and now it doesnt work
anymore. I even built the form over again but it doesnt work either. Perhaps
Im too tired...

 

[John Vinson]

I did what you said and got it ot work (well sort of), the only problem was
that the price the "Sub Form_AfterUpdate()" copied, was the price before it
was calculated.

So if it was a new row it copied nothing and if it was a row with a price
already in it and I changed the quantity to change the price it copied the
price before recalculation to the field on the parentform?

After that I did something (but Im not sure what?) and now it doesnt work
anymore. I even built the form over again but it doesnt work either. Perhaps
Im too tired...

I'm sorry, I'm lost now.

What is the Recordsource of the main form?
What is the Recordsource of the subform?
What is the name of the textbox in which you're calculating the price
that you want to copy?
What is the name of the textbox into which you wish to copy it?
What is the expression which does the calculation (the Control Source
of the textbox)?

John W. Vinson[MVP]

 

[ovim via AccessMonster.com]

I did what you said and got it ot work (well sort of), the only problem was
that the price the "Sub Form_AfterUpdate()" copied, was the price before it

[quoted text clipped - 7 lines]

anymore. I even built the form over again but it doesnt work either. Perhaps
Im too tired...

I'm sorry, I'm lost now.

What is the Recordsource of the main form?
What is the Recordsource of the subform?
What is the name of the textbox in which you're calculating the price
that you want to copy?
What is the name of the textbox into which you wish to copy it?
What is the expression which does the calculation (the Control Source
of the textbox)?

John W. Vinson[MVP]

Ok, now I know whats wrong.

When I got it almost working I had put the afterupdate event to the quantity
field in the subform. Thats why it didn't copy the calculated amount to the
mainform.

To copy the calculated total to the mainform I'd have to put the afterupdate
event to the calculated total field in the subform.

And now I know that calculated fields dont fire afterupdate events even if
the calcuation is "updated".

How can i get around this?

 

[John Vinson]

Ok, now I know whats wrong.

When I got it almost working I had put the afterupdate event to the quantity
field in the subform. Thats why it didn't copy the calculated amount to the
mainform.

To copy the calculated total to the mainform I'd have to put the afterupdate
event to the calculated total field in the subform.

And now I know that calculated fields dont fire afterupdate events even if
the calcuation is "updated".

How can i get around this?

Use the AfterUpdate event of the Form itself - not the calculated
control; or use the Afterupdate event(s) of the control(s) that the
user DOES update which feed the calculated field.


John W. Vinson[MVP]